Sqlite multipule row insert question

I am trying to insert several rows into sqlite database, i have searched this fourm and the net but still can not get this to work, it just inserts the first row correctly but nothing is inserted after the first row. I have verified the query is updated to the correct values on each loop. can you help me?:)

Code:

---

templatedb.transaction();
            while(!db->eof()){
 
            theQuery="";
            theQuery ="INSERT INTO reporttemplates (reportname ,itemtag,itemposx,itemposy,scale,angle,zvalue,data,originalrect,currentrect,";
            theQuery =theQuery+"type,fontindex,color,fntsize,isbold,isitalic,isunderlined,pagenum,showonallpages,opacity)";
            theQuery =theQuery+ " VALUES('"+ui->lineEdit->text()+"','"+db->getCell(db->currentRow(),0)+"','"+db->getCell(db->currentRow(),1)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),2)+"','"+db->getCell(db->currentRow(),3)+"','"+db->getCell(db->currentRow(),4)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),5)+"','"+db->getCell(db->currentRow(),6)+"','"+db->getCell(db->currentRow(),7)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),8)+"','"+db->getCell(db->currentRow(),9)+"','"+db->getCell(db->currentRow(),10)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),11)+"','"+db->getCell(db->currentRow(),12)+"','"+db->getCell(db->currentRow(),13)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),14)+"','"+db->getCell(db->currentRow(),15)+"','"+db->getCell(db->currentRow(),16)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),17)+"','"+db->getCell(db->currentRow(),18)+"')" ;
            templatedb.exec(theQuery);
 
 
 
            db->next();
            int row =db->currentRow();
        }
 
templatedb.exec("COMMIT");

---

What does QSqlDatabase::lastError () tell you about each attempted insert?

With the code I can see you will need to be wary of single quotes appearing in the source data because it will break the SQL. Reportname and data look like the obvious candidates but everything is quoted in your SQL, so anything could be. If you have this happening then perhaps you should consider QSqlQuery::prepare() and QSqlQuery::bindValue() rather than directly accessing the database driver with a constructed string.

You would typically use QSqlDatabase::commit() to commit your transaction.

ChrisW,

thanks for your answer, I used the QDebug (i should have thought of that :o)

it gives error 19 unable to fetch row , constraint failed.


i am not sure if that means it can not get info from sqlite or the "db" database.

I have changed to bindvalue ,still same problem.

the first record is inserted into the sqlite database (templatedb) correctly but no record is inserted after that. i have found that the query goes inactive after the first successful query.exec().

I have found several post about this behavior but i can find NO solution. Does ANYBODY know what the problem is?

Code:

---

int x=0;
int numrecords= db.recordcount;
 
while(x<=numrecords){
    QString theQuery;
    QSqlQuery query(theQuery, templatedb) ;
 
 
                theQuery="";
                theQuery ="INSERT INTO reporttemplates (reportname,itemtag,itemposx,itemposy,scale,angle,zvalue,data,originalrect,currentrect,";
                theQuery =theQuery+"type,fontindex,color,fntsize,isbold,isitalic,isunderlined,pagenum,showonallpages,opacity)";
                theQuery =theQuery+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
 
               query.prepare(theQuery);
 
               query.bindValue(0,ui->lineEdit->text());
               query.bindValue(1,db->getCell(row,0));  
               query.bindValue(2,db->getCell(row,1));
               query.bindValue(3,db->getCell(row,2));
               query.bindValue(4,db->getCell(row,3));
               query.bindValue(5,db->getCell(row,4));
               query.bindValue(6,db->getCell(row,5));
               query.bindValue(7,db->getCell(row,6));
               query.bindValue(8,db->getCell(row,7));
               query.bindValue(9,db->getCell(row,8));
               query.bindValue(10,db->getCell(row,9));
               query.bindValue(11,db->getCell(row,10));
               query.bindValue(12,db->getCell(row,11));
               query.bindValue(13,db->getCell(row,12));
               query.bindValue(14,db->getCell(row,13));
               query.bindValue(15,db->getCell(row,14));
               query.bindValue(16,db->getCell(row,15));
               query.bindValue(17,db->getCell(row,16));
               query.bindValue(18,db->getCell(row,17));
               query.bindValue(19,db->getCell(row,18));
 
 
      qDebug() << query.exec();  <- works correctly one time , next returns false.
      query.next(); <-try to move to last row 
 
      qDebug() << query.lastError(); <-2nd time returns QSqlError(19,"unable to fetch row", "constraint failed")
x++
}

---

Line 39 should be

Code:

---

db.next()

---

like your original code shouldn't it?

The message regarding violation of a constraint can only be coming from the insert. Duplicate primary key value would be the first place to look although there can be other constraints defined on the reporttemplates table. It's not clear which (if any) column is a unique identifier for a row. If you are not inserting a value into any primary key column, and it is not an auto-increment type field, then the second row insert will have the same id as the first (e.g. NULL, 0 or empty) and fail.

THANK YOU CHRIS !!! :)

The primary key was THE issue.