Using variables in fetching data from Database
-
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? -
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()
-
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).
-
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" );
}
@ -
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?
-
it is now working after removing bindValue
@
query.prepare("SELECT adm FROM student_info WHERR adm = '"+admNum+"'");
@ -
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);
@
