Database combobox search

[poporacer]

I have a form that has a combobox that gets populated from a database query. The combobox contains the first and last name of a person that I appended from the two fields in the table. I have used MS Access and you can hide columns in the combo box and retrieve them for search functions later. I can't figure out how to create the correct query. My thought process has gone several ways, but can't find the code to do it: I am thinking I might have to somehow pass the id to the slot?
Here is what I have so far:

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{
 
    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT LName, FName , weight , notes  * FROM rider WHERE id=" + ui->cmbRider->currentIndex(0); // this won't work because the id is not in the combobox.
    query.prepare(Sqlstring);
    query.exec();
 
    ui->txtFName->text().clear();
    ui->txtFName->text()=(query.value (0));//not sure if this will work either
}

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{

    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT LName, FName , weight , notes  * FROM rider WHERE id=" + ui->cmbRider->currentIndex(0); // this won't work because the id is not in the combobox.
    query.prepare(Sqlstring);
    query.exec();

    ui->txtFName->text().clear();
    ui->txtFName->text()=(query.value (0));//not sure if this will work either
}

To copy to clipboard, switch view to plain text mode 

Thanks for your help!

[Lykurg]

I guess you are using a model for the combobox. How does it look like? Anyway, each model provides access to all model data via an index you can create with the "index" parameter. So you can fetch all data without using another query.

If you use a QSqlQueryModel, use record(). (or in general data())

[poporacer]

No I am not using a model for the combobox. Here is the codew for the combobox:

QSqlQuery riderquery, bikequery;
    riderquery.exec("SELECT id, LName, FName, weight, notes FROM rider");
    qDebug() << riderquery.lastError();
   while (riderquery.next())
    {
        ui->cmbRider->addItem(riderquery.value (2).toString()+ " "+ riderquery.value (1).toString());
 
    }

QSqlQuery riderquery, bikequery;
    riderquery.exec("SELECT id, LName, FName, weight, notes FROM rider");
    qDebug() << riderquery.lastError();
   while (riderquery.next())
    {
        ui->cmbRider->addItem(riderquery.value (2).toString()+ " "+ riderquery.value (1).toString());

    }

To copy to clipboard, switch view to plain text mode 

I am not familiar with SQqlQueryModel...I will do some research on that and see what I can find. Basically what I want is the combobox to display the full name of a person (combination of first and last names) and then when the user selects a name in the combobox, line edit widgets get populated in the form based on the selection in the combo box. I searched thinking I might find an example but no luck. I must be searching for the wrong words.
Any suggestions?

[Lykurg]

If your database it not so long, it is ok using items, also it is not the best way. In your case you can set an user data to the item where you store the id. Also querying weight and notes but don't use then is superfluous.
void QComboBox::addItem ( const QString & text, const QVariant & userData = QVariant() ) and then use the models data function to get the value in your slot.

[poporacer]

The database won't be very long. I didn't put the code for the weight and notes so as not to clutter things up. If I figure out the first part, I will put the rest of the code that uses the other data. I searched for model data function and didn't find anything that looked like it would do what I am looking for. Maybe I am going about it wrong or didn't explain myself. I have a form with a combobox that gets populated with all the names of the people in the data base. On this form are several lineedit widgets. When the user selects a name in the combobox the associated lineedits boxes get populated with the data. I was thought a SQL query of the nature SELECT FROM rider WHERE id = "id of item selected". What would be the best way to do this? And a bit of sample code would be greatly appreciated. Can I assign the id from the database to the combobox via setCurrentIndex()?
thanks for your help

I read void QComboBox::addItem ( const QString & text, const QVariant & userData = QVariant() ) and it sounds like this might be what I need to use. But I can't figure out how to use it.

[Lykurg]

On what you write QDataWidgetMapper is what you are after, but then you have to deal with models, which may need some time to understand.

The easy way is to use like you currently do and use QComboBox::addItem() with a second parameter, which is the id of the record. In the slot you can receive the id via QComboBox::itemData() and currentIndex.

[poporacer]

I read the documentation on QDataWidgetMapper and I think this has way more functionality than what I need. I think the QComboBox::addItem is the way to go but I am not quite there. I am not sure how to get the data with QComboBox::itemData(). Here is what I have.

while (riderquery.next())
    {
        ui->cmbRider->addItem(riderquery.value (2).toString()+ " "+ riderquery.value (1).toString(),riderquery.value (0).toInt()); //populates combobox with name and id Is this the correct syntax?
 
        }
}

while (riderquery.next())
    {
        ui->cmbRider->addItem(riderquery.value (2).toString()+ " "+ riderquery.value (1).toString(),riderquery.value (0).toInt()); //populates combobox with name and id Is this the correct syntax?

        }
}

To copy to clipboard, switch view to plain text mode 

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{
 
    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT id, LName, FName, weight, notes * FROM rider WHERE id=" + ui->cmbRider->itemData(0).toString(); //This is where I think I have a problem. I think it is with the ui->cmbRider->itemData(0).toString()
    query.prepare(Sqlstring);
    query.exec();
 
    ui->txtFName->text().clear();
    ui->txtFName->text()=(query.value (1)); //this should populate the lineedit box with the new data
}

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{

    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT id, LName, FName, weight, notes * FROM rider WHERE id=" + ui->cmbRider->itemData(0).toString(); //This is where I think I have a problem. I think it is with the ui->cmbRider->itemData(0).toString()
    query.prepare(Sqlstring);
    query.exec();

    ui->txtFName->text().clear();
    ui->txtFName->text()=(query.value (1)); //this should populate the lineedit box with the new data
}

To copy to clipboard, switch view to plain text mode 

I get several errors related to this: no match for 'operator=' in 'QLineEdit::text() const() =QSqlquery::value() const()'
what am I missing?

PS. Is there a post or some documentation on how to decipher the classes? Like exactly what does void QComboBox::insertItem ( int index, const QString & text, const QVariant & userData = QVariant() ) mean and how do you use it? I get some of the basics, but not sure how to intrepret it entirely. The explanation of this might help me understand future problems.

I really appreciate all the help!

[Lykurg]

I get several errors related to this: no match for 'operator=' in 'QLineEdit::text() const() =QSqlquery::value() const()'
what am I missing?

text is no L-Value, so you have to use setText().

PS. Is there a post or some documentation on how to decipher the classes? Like exactly what does void QComboBox::insertItem ( int index, const QString & text, const QVariant & userData = QVariant() ) mean and how do you use it? I get some of the basics, but not sure how to intrepret it entirely. The explanation of this might help me understand future problems.

QComboBox uses a model with basic roles. The function is just a short hand. See the documentation on model and there roles to understand what QComboBox does.

EDIT: And debug, what itemData is returning.

[poporacer]

Thanks, I got it working with your help, but it I have a small issue. It seems like the query isn't updating after the change. The query works properly the first time and the lineedit gets filled with the proper entry so I know the query is working, but then it doesn't update after the change. The next time through the query is still the same.

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{
 
    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT id,LName, FName, weight, notes FROM rider WHERE id=" + ui->cmbRider->itemData(0).toString();
    query.prepare(Sqlstring);
    query.exec();
    query.next();
     ui->txtFName->text().clear();
    ui->txtFName->setText(query.value (2).toString());
}

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{

    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT id,LName, FName, weight, notes FROM rider WHERE id=" + ui->cmbRider->itemData(0).toString();
    query.prepare(Sqlstring);
    query.exec();
    query.next();
     ui->txtFName->text().clear();
    ui->txtFName->setText(query.value (2).toString());
}

To copy to clipboard, switch view to plain text mode 

Each time the query is the same. Why isn't the query updating?

[Lykurg]

Ehm,

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{
 
    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT id,LName, FName, weight, notes FROM rider WHERE id=" + ui->cmbRider->itemData(0).toString();

void MainWindow::on_cmbRider_currentIndexChanged(int index)
{

    QString Sqlstring;
    QSqlQuery query ;
    Sqlstring="SELECT id,LName, FName, weight, notes FROM rider WHERE id=" + ui->cmbRider->itemData(0).toString();

To copy to clipboard, switch view to plain text mode 

maybe you should use index to get the user data of the selected item.

[poporacer]

Thanks! That did it!