I connect to the database but the values do not change when I give UPDATE MYSQL Qt C++

Question

The data is loaded in the table, then I change the data in the table, when I click on the update button, I can take the data (even the changes) and save it in the variable, but it seems that when I enter the sql query nothing happens. I need to be able to update the table data as soon as I change something and click update button.

Connection to the bank(ok):

void ConectaBanco()
{
    //Variavel do tipo DataBase
    QSqlDatabase db;

    //Conexao com o MYSQL
    db = QSqlDatabase::addDatabase("QMYSQL");

    //Servidor que vai receber o DB (padrão do server local)
    db.setHostName("127.0.0.1");

    //Porta que vai conectar com o DB (por padrão vai ser 3306)
    db.setPort(3306);

    //Seleciona o banco de dados
    db.setDatabaseName("cursos");

    //Usuario do BD
    db.setUserName("root");

    //Caso tenha senha no mysql
    //db.setPassword();

    //Teste de conexao
    if(db.open())
    {
        qDebug()<<"Sucesso ao abrir a conexão";
    }
    else
    {
        qDebug()<<db.lastError().text();
    }
}

Update function:

void MainWindow::on_pushButton_clicked()
{
    int fila = 0;
    ConectaBanco();
    int CdAluno = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(fila,0)).toInt();
    QString NmAluno = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(fila,1)).toString();
    QString NmCurso = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(fila,2)).toString();
    QString NmEscola = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(fila,3)).toString();


    QSqlQuery query("UPDATE matricula set NmAluno= '"+NmAluno+"', "
                                         "NmCurso= '"+NmCurso+"', "
                                         "NmEscola='"+NmEscola+"' "
                                         "WHERE CdMatricula='"+CdAluno+"'");
    query.exec();

}

When I debug and place the mouse on top of the UPDATE string the following message appears: in Much value

I would like to know how to solve the problem and manage to change the data perfectly.

Answer 1

Instead of concatenating the query string, try something like this:

#include <QVariant>
...
...

QSqlQuery query
query.prepare("UPDATE matricula SET NmAluno= ?, NmCurso= ?, NmEscola=? WHERE CdMatricula=?");

query.addBindValue(NmAluno);
query.addBindValue(NmCurso);
query.addBindValue(NmEscola);
query.addBindValue(CdAluno);

if( ! query.exec()) {
    qDebug() << "Erro ao executar query!!";
}

And beware, what’s in your method void ConectaBanco(), You can only be executed once. If you add a BD twice, you will get a duplicate connection error (as it appears there on your Qt Creator output screen) and your code may not work as you expect. If I were you would create an auxiliary class to handle connections to Mysql.

Answer 2

The update function should be with the Cdmatricula field in string format for the query to recognize the code:

void MainWindow::on_pushButton_clicked()
{
    ConectaBanco();
    int linha = 0;
    QString CdAluno = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(linha,0)).toString();
    QString NmAluno = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(linha,1)).toString();
    QString NmCurso = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(linha,2)).toString();
    QString NmEscola = ui->tabelaCarga->model()->data(ui->tabelaCarga->model()->index(linha,3)).toString();

    qDebug()<<CdAluno<<NmAluno<<NmCurso<<NmEscola;

    QSqlQuery query;

    query.prepare("UPDATE `matricula` SET `NmAluno` = '"+NmAluno+"', "
                                         "`NmCurso` = '"+NmCurso+"', "
                                         "`NmEscola` = '"+NmEscola+"'"
                                         " WHERE `matricula`.`CdMatricula` = '"+CdAluno+"'");



    if(query.exec())
    {
        qDebug()<<"Query executada com sucesso!";
    }
    else
    {
        qDebug()<<"Erro ao executar a query";
    }

    //atualiza a tabela automaticamente
    on_btnCarga_clicked();
}