Problem with query



  • Hi, everyone. In sqlite database, i have a table like that :
    Menu
    (
    Dish1 nvarchar(20);
    Dish2 nvarchar(20);
    Date Date;
    )

    and in qt project, i wrote :
    QDate today = QDate::currentDate();
    QString strToday = today.toString();

    qry->prepare("select * from Menu where DATE = '"+strToday+"'");
    qry->exec();
    if(qry->exec())
        QMessageBox::information(this,"a","success");
    else
        QMessageBox::information(this,"a","fail");
    

    the purpose of this code is to check if a record with the condition Date = '"+strToday+"' is created or not.
    But it always runs into the success case although i haven't added any record in my database, i'm kinda confusing now.
    I want to know if is there any possibility that i can check if a record with given condition is existed or not in my database.



  • two corrections needed:
    QString strToday = today.toString(); should become QString strToday = today.toString(Qt::ISODate);
    if(qry->exec()) should become if(qry->next())

    Also I'd use bindValue to inject the date instead of a string concat.

    qry->prepare("select * from Menu where DATE = :currentDate");
    qry->bindValue(":currentDate", strToday);
    


  • @VRonin

    the new problem is now it always runs into case fail although i have already added some records in my database @@



  • my main purpose is check if a row exists in my sqlite database table!!



  • @Anh-Phan said:

    my main purpose is check if a row exists in my sqlite database table!!

    If you just want to check if any row exist remove the where condition on the query

    also, as a check do change qry->exec(); into:

    if(!qry->exec())
    MessageBox::information(this,"a","Query Failed! " + qry->lastError().text());
    

  • Moderators

    Did you use exactly the same date to insert the row?



  • maybe one issue:
    the call to qry->exec(); is running the SQL statement. If the return is true, we just know the statement has executed correctly (e.g. syntax and fieldnames are correct)
    Depending on the nature of the statement threre may be a resultset in form of a matrix.
    You can check the number of rows in the resultset with calling qry->size(); if you provided a select statement and and if qry->exec()returned true.

    If you just like to know if there are rows slected by your statement qry->size(); is enough.

    To get the rows of your resultset you call qry->next(); for every row. A gernerall use case is to
    loop over all rows.

    if(qry->exec())
    {
       while(qry->next()) //qry->next() will return true if there´s another row
       {
          //value(0) retrieves the value of the first column of the resultset
          qry->value(0).toString(); 
       }
    }


  • As far as I know, sqlite doesn't support the convenient size() function. So, you'll have to use next() and I strongly suggest to bind values as others suggested. To sum up all the suggestions, I think this is what you want:

    qry->prepare("select * from Menu where DATE = :currentDate");
    qry->bindValue(":currentDate", strToday);
    if (!qry->exec()) // Check successful execution
        qCritical() << "Problem executing query. Error was: " << qry->lastError().text();
    if (qry->next()) // Check if there is a result
        QMessageBox::information(this,"a","success");
    else
       QMessageBox::information(this,"a","fail");

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.