SQLite databases queries issues

[MR_mn]

Hello everybody,
I'm trying to work with SQLite database to create an application which, based on an user input to be chosen in a comboBox, automatically fills a lineEdit field with a corresponding value from the same table of the database.
So, for example, I have a database which contains employee's info (name, last name, age, etc..), the comboBox is linked to the column "name" and, based on which name the user chooses, the lineEdit should be populated automatically with the corresponding age.
So far, I was able to connect the comboBox with the right column in the database, displaying the correct values, extract the corresponding chosen value by the user, but I cannot fill anything in the lineEdit.
Here is the code I'm working on:

void MainWindow::on_pushButton_Load_clicked()
{
    connOpen();
    QSqlQueryModel *modal = new QSqlQueryModel();

    QSqlQuery *qry = new QSqlQuery(mydb);
    qry->prepare("select Name from employeeinfo");
    qry->exec();
    modal->setQuery(*qry);

    ui->comboBox->setModel(modal);


    int indexComboBox = ui->comboBox->currentIndex();

    QString text= ui->comboBox->currentText();
   
}


void MainWindow::on_comboBox_currentIndexChanged(const QString &arg1)
{
      
    QString text = ui->comboBox->currentText();
    QSqlQuery *qry_2 = new QSqlQuery;
    qry_2->prepare("SELECT Age FROM Employeeinfo"); //WHERE Age='"+text+"'");
    if(qry_2->exec()) {
            while(qry_2->next()) {
            ui->lineEdit->setText(qry_2->value(5).toString()); }
                     }
    
    qDebug() << qry_2->size()<< qry_2->lastError().text();

}

The query size returned from qDebug is -1, and with another qDebug command I was able to see that what extracted from comboBox is fine. No error is returned from qry_2->last error command.

Does anyone have any hint on this?
Thanks!

[SGaist]

Hi,

Please re-read the documentation of QSqlQueryModel. And more specifically the second version of the setQuery method.

Then there are several things that look wrong:

• There's no reason to allocate your QSqlQuery object on the heap.
• The query you use for the model is linked to the mydb database object while the other use not. Why is that ?
• You are not using the same table name for both queries.<
• You are leaking QSqlQuery objects in on_comboBox_currentIndexChanged since you never delete them.

[MR_mn]

Hello,
thanks for your prompt answer, I had a look at the links you provided but honestly I can't see what I'm doing wrong. I'm getting the right value of the comboBox but that's as far as I can go, the code cannot look for the same value in the database table and have a corresponding value filling the lineEdit.

I'm using the same table name for both queries, the problem is that I changed all the names to post on here just for the sake of clarity.

Thanks!

[MrShawn]

ui->lineEdit->setText(qry_2->value(5).toString())

Okay well first of all in the returned query i would be surprised if you are getting 5 columns returned from that query that only returns 1 column worth of information. Specifically, the 5 there says you want to 5th column's data. You have one column being returned with id of "Age". Change 5 to 0 or "Age" and that will access the column you care about.

It seems you are just troubleshooting, but right now you are going to get x number of rows and really what will happen is you will set the text x number of times and you will only see the final result which is the last record pulled up. Not sure what you are trying to do with the while loop but if you plan to have only one record, say after setting your where clause with the employee name then i suggest changing your code to simply be

if(!qry_2->exec())
{
     //Error occurred while trying to execute query
     ui->lineEdit->setText("Age not found");
     qDebug() << qry_2->lastError().text();
     return;
}

qry_2->next();
if (qry_2->value("Age").isNull())
     ui->lineEdit->setText("Age not found");     //Your query did not find any records...
else
     ui->lineEdit->setText(qry_2->value("Age").toString());

[MrShawn]

@SGaist is right about leaking, but you can just change off of pointers and they will just be scoped to your function.

QSqlQuery qry_2(mydb);

[VRonin]

In addition to what @SGaist said, you should not keep QSqlDatabase variables around. they should be local scoped. see the docs for the official warning.

Coming to your problem, why don't you just change the query to "select Name, Age from employeeinfo" and then use ui->lineEdit->setText(ui->comboBox->model()->index(ui->comboBox->currentIndex(),1).data().tostring()); to retrieve the age? no need to execute the query twice