Using variables in fetching data from Database
-
wrote on 30 May 2013, 21:09 last edited by
hi. I have a code which is suppose to chec if the admission number being assigned to the new student is already assigned to another student. if already assigned, the system is to alert the user. i have this code but it is not working as required. what might be the problem?
@
QString admNum = admNumLineEdit->text();
QSqlDatabase db = QSqlDatabase::addDatabase(QMYSQL);
db.setHostName("localhost");
db.setDatabaseName("school");
db.setUserName("student");
db.setPassword("student");
QSqlQuery qeury;
if(!db.open())
{
....
}
else
{
query.prepare("SELECT adm FROM student_info WHERE adm = ':admission'");
query.bindValue(":admission", admNum);
if(!query.exec())
{
qDebug() << query.lastError();
}
else
{
QSqlRecord rec = query.record();
int cols = rec.count();
if(cols == 0)
{
QMessageBox::information(this, "admission number available", "Admission number can be assigned");
}
else
{
QMessageBox::information(this, "Invalid admission number", "Admission number already exist");
}
}
}
@
if I assign admNum to the same value as the one in the database or a different value, second qmessagebox is displayed. what can be the problem? -
wrote on 30 May 2013, 21:17 last edited by
Why are you counting columns to determine if your query had results?
-
Hi,
QSqlRecord::count returns the number of fields that your query returns, so you'll always have one (adm).
You should rather test query.next()
-
wrote on 31 May 2013, 04:57 last edited by
thanks for your feedback. what i need is to check if there is any row in the table with the same admNum because if the same value is in the table the number of row should be > 0. how do i count the number iof rows?
-
Since you're supposed to only have one row for each number, next will fail if there aren't any. If that's not enough, use QSqlQuery::size() (don't forget to check that your driver has this feature).
-
wrote on 31 May 2013, 07:06 last edited by
i modified my code as shown and it worked. Thanks for the advice SGaist.
@
query. prepare( "SELECT
adm FROM student_info WHERE
adm = ':admission'" );
query. bindValue
( ":admission" , admNum);
if(! query. exec())
{
qDebug() << query. lastError();
}
else
{
int numrow = query.size();
if (numrow == 0)
{
QMessageBox::information
( this , "admission number
available" , "Admission
number can be assigned" );
}
else
{
QMessageBox::information
( this , "Invalid admission
number" , "Admission number
already exist" );
}
@ -
wrote on 31 May 2013, 08:00 last edited by
Am sorry. just noted that the code now runs and the first qmessage box displayed whether the admNum value is the same with what is in the database table or not. any suggestion?
-
wrote on 31 May 2013, 08:26 last edited by
it is now working after removing bindValue
@
query.prepare("SELECT adm FROM student_info WHERR adm = '"+admNum+"'");
@ -
wrote on 31 May 2013, 12:16 last edited by
Hi!
When you bind value to QSqlQuery you shouldn't care about var formats. In your case don't put :admission in to `` . Just write like this:
@query. prepare( "SELECT adm FROM student_info WHERE adm = :admission" );
query. bindValue ( ":admission" , admNum);
@
8/9