Unsolved 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 becomeQString strToday = today.toString(Qt::ISODate);
if(qry->exec())
should becomeif(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());
-
Did you use exactly the same date to insert the row?
-
maybe one issue:
the call toqry->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 callingqry->size();
if you provided a select statement and and ifqry->exec()
returnedtrue
.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");