QSqlquery prepare() and bindvalue() not working?

Question

I have this code for managing logins, here is a small bit of it:

void Login::on_pushButton_clicked(){
    QString user, password;
    user = ui->username->text();
    password = ui->pass->text();

    QSqlQuery qry;
    qry.prepare("SELECT id, name from users WHERE username = :username AND password = :password");
    qry.bindValue(":username", user);
    qry.bindValue(":password", password);{
    int counter = 0;
    while (qry.next()){
        counter++;
    }
    if(counter==1)
        ui -> statuslabel -> setText("Sign in successful");
    if(counter<1)
        ui -> statuslabel -> setText("Sign in unsuccessful");
    }
}

On giving it the correct input (ie correct password and username) it does not work and proceed to the second if condition saying that Sign in is unsuccessful. I have a counter int type that counts how many instances of user input match the database tables. It is initialized to a zero which means that is not the problem. any ideas on what might be the case here?

Rhathin · Accepted Answer · 2018-11-07T08:29:16.330

2

You forgot to call qry.exec() before accessing first element using qry.next(). It should be called after last qry.bindValue().

Below is example how it can be done in your code:

QSqlQuery qry;
qry.prepare("SELECT id, name from users WHERE username = :username AND password = :password");
qry.bindValue(":username", user);
qry.bindValue(":password", password);
qry.exec()
if(!qry.isActive())
{
    ui -> statuslabel -> setText("SQL Statement execution failed");
    return;
}
if(qry.size() > 0)
    ui -> statuslabel -> setText("Sign in successful");
else
    ui -> statuslabel -> setText("Sign in unsuccessful");

Note: You don't have to use QSqlQuery::next to check if your SELECT statement return any result. QSqlQuery::size method will return number of selected rows.

edited Nov 07 '18 at 08:29

answered Nov 06 '18 at 13:15

Rhathin

1,176
2
14
20

Sorry for asking again but can you explain how to execute qry.exec() before query.next(). Also why would that be the case, both of them return bool values right? – Niko Nov 06 '18 at 13:18
1

If you don't call `qry.exec()` the query never executes. You can put `qry.exec();` after `int counter = 0;` – drescherjm Nov 06 '18 at 13:21
@drescherjm Thanks for your suggestion however that did not work. It still returns unsuccessful login. – Niko Nov 06 '18 at 13:27
Thanks a ton! But now it has an opposite effect? it says all logins are correct? qry.size returns -1 – Niko Nov 06 '18 at 13:34
@Niko if `qry.size` returns `-1` it means that either your database doesn't support reporting (then you can use `qry.next` approach) or your query is not active after its execution (which means that there could be some error). Try to use `QSqlQuery::lastError` to check what is going wrong. – Rhathin Nov 06 '18 at 13:41
@Niko I've editted my example. It will check if query is not active in first place. – Rhathin Nov 06 '18 at 13:52
@Rhathin i had tried this before and got a similar error saying that quert is not active, but i have no idea what the problem might bel – Niko Nov 06 '18 at 14:34
@Niko what is output of `qry.lastError().text()` after calling `qry.exec()`? – Rhathin Nov 06 '18 at 14:37
@Rhathin Table 'database.users' doesn't exist QMYSQL: Unable to execute query" Although users does exist in my database – Niko Nov 06 '18 at 14:54
1

@Niko It's really hard to say, what can be wrong without knowing your database structure and rest of your code. This issue however is not directly related with your question and probably should be placed in new question to avoid making this one too broad. – Rhathin Nov 06 '18 at 15:29
I don't think your database is open. Edit: You had a question last week about not opening the database. Was that solved? If it was not you can't continue writing queries. – drescherjm Nov 06 '18 at 15:56
***This issue however is not directly related with your question and probably should be placed in new question*** I fully agree the new issue is not directly related to the original problem. – drescherjm Nov 06 '18 at 15:58
@drescherjm It was solved and now this question was solved as well. – Niko Nov 06 '18 at 17:47
@Rhathin Thanks for your help! Cheers! – Niko Nov 06 '18 at 17:47
Please mark @Rhathin's answer as correct when you can. – drescherjm Nov 06 '18 at 17:48

QSqlquery prepare() and bindvalue() not working?

1 Answers1