[SOLVED] [QComboBox and PSQL] Access PK from another column in the table



  • Hello people!

    I'm trying to write a simple function that adds two entries in a DB. The first entry works well. However, the second entry does not.

    @void newEmployee::addEmployee()
    {
    qNewEmployee = new QSqlQuery(QSqlDatabase::database("*****"));
    qNewEmployee->prepare("INSERT INTO "TB_Employe" VALUES (DEFAULT, :l_name, :f_name, DEFAULT)");

    qNewEmployeeAff = new QSqlQuery(QSqlDatabase::database("*****"));
    qNewEmployeeAff->prepare("INSERT INTO \"TB_Emp2Aff\"  VALUES (DEFAULT, currval('\"sq_IndexEmploye\"'::regclass), :idaff, :b_date)");
    
    // Get values from ComboBoxes and put them in the query
    qNewEmployee->bindValue(":l_name", NEW_lastNameEdit->text());
    qNewEmployee->bindValue(":f_name", NEW_firstNameEdit->text());
    
    qNewEmployeeAff->bindValue(":idaff", modelAffiliation->record(NEW_affEdit->model()->index(NEW_affEdit->currentIndex(),0).row()).value("\"IndexAffiliation\"").toInt());
    qNewEmployeeAff->bindValue(":b_date", NEW_dateEdit->date().toString());
    
    qDebug() << modelAffiliation->record(NEW_affEdit->model()->index(NEW_affEdit->currentIndex(),0).row()).value("\"IndexAffiliation\"").toInt();
    
    qNewEmployee->exec&#40;&#41;;
    qNewEmployeeAff->exec(&#41;;
    

    }@

    NEW_affEdit is a ComboBox which is populated with a model like so:

    @void newEmployee::populateAffiliations()
    {
    // Setup model to populate affComboBox
    modelAffiliation = new QSqlRelationalTableModel(this, QSqlDatabase::database(""));
    modelAffiliation->setTable(""TB_
    *"");
    modelAffiliation->setSort(modelAffiliation->fieldIndex("AFF_Nom"),Qt::AscendingOrder);

    modelAffiliation->select();
    
    NEW_affEdit->setModel(modelAffiliation);
    NEW_affEdit->setModelColumn(modelAffiliation->fieldIndex("AFF_Nom"));
    
    // Sets today as default value of QDateEdit
    NEW_dateEdit->setDate(QDate::currentDate());
    

    }@

    Of course, the ComboBox only contains text. However, I want to access the PK linked to that text in order to add a line in another TB. The line
    @ qNewEmployeeAff->bindValue(":idaff", modelAffiliation->record(NEW_affEdit->model()->index(NEW_affEdit->currentIndex(),0).row()).value(""IndexAffiliation"").toInt());@

    always returns 0 for some reason. Any ideas? Thanks in advance!



  • Is the name of your field really "IndexAffiliation" and not just IndexAffiliation (without the ")



  • The name of the field is IndexAffiliation. However, if I remove the " " it usually sends indexaffiliation to the DB, which means nothing, but I will try removing them and get back to you.

    Thanks for your tip!



  • It does work!

    You know, I'd really like to know when to double-quote or not to double quote. I guess it's only inside QSqlQuery queries.

    Thanks a bunch!



  • I think it's because QSqlRecord uses it only as a key by which to access the field. It is therefore not part of a query to the database (or at least it is not inserted directly into an SQL query) and doesn't need the quotes (which AFAIK you really don't need anyway if you query doesn't contain any whitespaces). Another thing, if you are using MySql (the only database I have experience with, I don't know about other databases) you could also use `` or ' ' as quotes.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.