Problem with query

Anh Phan

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.

VRonin

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

Anh Phan

@VRonin


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

Anh Phan

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

VRonin

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

jsulm

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

sneubert

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

panosk

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