QSqlquery

Hello there,

I have a little problem with my query.

Code:

---

QSqlDatabase testdb= QSqlDatabase::database("testdb") ;
for(int i=0; i < dateList.length();i++)
    {
        QString buf = dateList.at(i);
        buf = buf.left(10);
        QString buf2 = contentList.at(i);
        QSqlQuery insert1("update mytable"
                                "set filed1= "+buf2+" "
                                "where partner in ('4569') "
                                "and Date= CONVERT (DATETIME,'"+buf+" 00:00:00',120) ",testdb);
        bool test = insert1.exec();
        if(test)
            QMessageBox::information(this, tr("query succesful"),tr("test: %1").arg(QString::number(dateList.length())));
    }

---

this query have no effect on my db it changes no fileds why??

Have anybody some idea??

are you sure you are connected to your database ?
wich database you use ?

Yes I use multiple connections thatswhy the first line in my code. I use mssql 9 and 10

... my code is correct I don´t see the changes cause I don´t make a refresh in my table ;o))

If filed1 is character column Yours query must be like :

Code:

---

QSqlQuery insert1("update mytable"
                                "set filed1= '"+buf2+"' "
                                "where partner in ('4569') "
                                "and Date= CONVERT (DATETIME,'"+buf+" 00:00:00',120) ",testdb);

---

You missed ' characters around buf2

and we don't use "in" if there is only one value !

"where partner in ('4569') " ==> "where partner =4569" better :)

thank youuu

... and you might want read about QSqlQuery::prepare() and QSqlQuery::bindValue() to avoid possible sql injections.

(then you could put your query outside the loop and create it only once)

what do you mean in detail I cannot follow the logic in your statement

perhaps a snippet might be usefull!

Quote:

---

Originally Posted by LordQt

what do you mean in detail I cannot follow the logic in your statement

perhaps a snippet might be usefull!

---

If you mean me:

Code:

---

QSqlDatabase testdb= QSqlDatabase::database("testdb") ;
QSqlQuery insert1;
insert1.prepare("update mytable set filed1= :field1 where partner = 4569 and Date= CONVERT (DATETIME, :date, 120)");
for(int i=0; i < dateList.length();i++)
    {
        QString buf = dateList.at(i);
        insert1.bindValue(":field1", buf); //<- will escape the content safely if it is e.g. "asdf'; TRUNCATE TABLE mytable;"
        buf = buf.left(10);
        insert1.bindValue(":date", buf+" 00:00:00");
        if(!insert1.exec())
            QMessageBox::information(this, tr("query succesful"),tr("test: %1").arg(QString::number(dateList.length())));
    }

---

hmmm ok:

Code:

---

QSqlDatabase testdb = QSqlDatabase::database("testDB") ;
QSqlQuery insert1(testdb);
    insert1.prepare("update mytable set field1= :field1 where partner = '4568' and Date= CONVERT (DATETIME, :date, 120)");
    for(int i=0; i < DateList.length();i++)
    {
        QString buf = DateList.at(i);
        insert1.bindValue(":field1", buf);
        buf = buf.left(10);
        QString buf2 = List.at(i);
        insert1.bindValue(":date", buf2+" 00:00:00");
        if(!insert1.exec())
            QMessageBox::information(this, tr("query not successful"),tr("Length: %1").arg(QString::number(DateList.length())));
    }

---

This code from your approach don´t make any changes on my db. It seems there is an error.......

Quote:

---

Originally Posted by codeman

This code from your approach don´t make any changes on my db. It seems there is an error.......

---

Then we need to see the structure of your database table and two sample rows. Highly probably that with your query and/or your values in DateList is something wrong. What type is your field1 field?