QSqlquery

[codeman]

Hello there,

I have a little problem with my query.

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())));
    }

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())));
    }

To copy to clipboard, switch view to plain text mode 

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

Have anybody some idea??

[QAmazigh]

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

[codeman]

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))

[Lesiok]

If filed1 is character column Yours query must be like :

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

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

To copy to clipboard, switch view to plain text mode 

You missed ' characters around buf2

[QAmazigh]

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

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

[codeman]

thank youuu

[Lykurg]

... 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)

[LordQt]

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

perhaps a snippet might be usefull!

[Lykurg]

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

perhaps a snippet might be usefull!

If you mean me:

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())));
    }

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())));
    }

To copy to clipboard, switch view to plain text mode 

[codeman]

hmmm ok:

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())));
    }

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())));
    }

To copy to clipboard, switch view to plain text mode 

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

[Lykurg]

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?