Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

can not getting sql result.



  • hi to all, I've centos 7 and Qt 5.7 in VM. Here I want to access mysql database and loading data into form. Problem is that sql query returns 0 for both of columns ( Stock and Rate ). in mysql database

    mine code is :-```
    code_text

    void Sales::on_lineEditQuantity_editingFinished()
    {
        quantity = ui->lineEditQuantity->text().trimmed().toInt();
        int prdnumber = ui->lineEditProductNumber->text().trimmed().toInt();
    
        QString sql = "select Stock, Rate from tableProductRecords where ProductID = :id";
    
        query = new QSqlQuery();
        query->prepare(sql);
        query->bindValue(":id", prdnumber);
        qDebug() << "prdnumber = " << prdnumber;
    
        int *no = new int;
        Sales::maxno = setmaxno(no);
        if(query->exec())
        {
            if(query->next())
            {
                int st =  query->value(0).toString().trimmed().toInt();
                
    	    qDebug() << "st = " << st << "  " << query->value(0).toString().trimmed().toInt();
                ui->lineEditRate->setText(query->value(1).toString().trimmed());
                qDebug() << "rate = "<< query->value(1).toString().trimmed().toInt();
    
                ui->lineEditBillNo->setText(QString::number(Sales::maxno));
                ui->lineEditTotal->setText(QString::number((query->value(1).toInt()) * quantity));
    
                int stk = st - quantity;//ui->lineEditQuantity->text().trimmed().toInt();
                if (stk < 0)
                {
                    ui->lineEditRate->setText("");
                    ui->lineEditBillNo->setText("");
                    ui->lineEditTotal->setText("");
                    QMessageBox::critical(this, "Sales", "Not enough stock\n available only " + QString::number(st));
                    return;
                }
                else
                {
                    ui->lineEditStock->setText(QString::number(stk));
    
                    sql = "update tableProductRecords set stock = "+
    		       QString::number(stk) + " "+ "where productID = "+ 
    		       ui->lineEditProductNumber->text().trimmed()+";";
                    query->prepare(sql);
                    if(query->exec())
                    {
                        QMessageBox::information(this, "Salas", "record updated");
                    }
                }
            }
            else
            {
                QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
            }
        }
        else
        {
            QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
        }
    }
    

    Output is :-

    prdnumber =  1
    st =  0    0
    QMYSQLResult::data: column 1 out of range
    QMYSQLResult::data: column 1 out of range
    rate =  0  
    QMYSQLResult::data: column 1 out of range
    
    MYSQLResult::data: column 1 out of range
    

    problem is that via sql i am not getting stock and rate values


  • Qt Champions 2019

    Are you sure both columns exists (with the correct lower/uppercase spelling)
    You can inspect the returned values by calling QSqlQuery::record() and print out what columns etc. were returned.

    btw: your code leaks the query (no need to create it on the heap at all) and the string pointer to an integer ('no'). Also you're inconsistent in your usage of the returned values from the query ( query->value(1).toString().trimmed().toInt() <-> query->value(1).toInt()) which makes all this hard to read



  • @rahulvishwakarma in addition to @Christian-Ehrlicher suggestions, are you able to run that same query from the same place where you run your Qt app and getting useful results?



  • @Pablo-J-Rogina yes i can get useful result when I fired query from terminal.



  • Hi. maybe also check the result of the prepare() function, say like this:

     qDebug() << query->prepare(sql);
    

    does it return true or false?


  • Qt Champions 2019

    @rahulvishwakarma So did you actually tried out what I suggested?



  • i tried like this :-

    
    void Sales::on_lineEditQuantity_editingFinished()
    {
        int *no = new int;
        Sales::maxno = setmaxno(no);
    
        quantity = ui->lineEditQuantity->text().trimmed().toInt();
        int prdnumber = ui->lineEditProductNumber->text().trimmed().toInt();
    
        QString sql = "select Stock, Rate from tableProductRecords where ProductID = :id";
    
    	qDebug() << query->prepare(sql); // prints true
        query->bindValue(":id", prdnumber);
        qDebug() << "prdnumber = " << prdnumber;
    
        if(query->exec())
        {
            if(query->next())
            {
    
                if(query->isActive())
                {
                    QMessageBox::information(this, "on_lineEditQuantity_editingFinished", "query is active");
                    QSqlRecord rd = query->record();
                    int numcoll = rd.indexOf("Stock");
    
                    QSqlQuery * sq = new QSqlQuery(*query);
                    if(sq->isValid())
                    {
                        qDebug() << "sq isvalid";
    
                        qDebug() << "rd.fieldName(1) " << rd.fieldName(numcoll);
                    }
                    else
                    {
                        qDebug() << "sq is not valid";
                    }
    
                    while (sq->next())
                    {
                        qDebug() << " sq " << sq->value(numcoll).toString().trimmed();
                    }
                }
    
    
                int st =  query->value(0).toInt();
                qDebug() << "st = " << st << "  " << query->value(0).toInt();
    
                ui->lineEditRate->setText(query->value(1).toString().trimmed());
                qDebug() << "rate = "<< query->value(1).toInt();
    
                ui->lineEditBillNo->setText(QString::number(Sales::maxno));
                ui->lineEditTotal->setText(QString::number((query->value(1).toInt()) * quantity));// (ui->lineEditQuantity->text().trimmed().toInt())));
    
                QMessageBox::information(this, "on_lineEditQuantity_editingFinished", "st = " + QString::number(st));
    
                int stk = st - quantity;
                if (stk < 0)
                {
                    ui->lineEditRate->setText("");
                    ui->lineEditBillNo->setText("");
                    ui->lineEditTotal->setText("");
                    QMessageBox::critical(this, "Sales", "Not enough stock\n available only " + QString::number(st));
                    return;
                }
                else
                {
                    ui->lineEditStock->setText(QString::number(stk));
    
                    sql = "update tableProductRecords set stock = "+ QString::number(stk) + " "+ "where productID = "+ ui->lineEditProductNumber->text().trimmed()+";";
                    query->prepare(sql);
                    if(query->exec())
                    {
                        QMessageBox::information(this, "Sales", "record updated");
                    }
                }
            }
            else
            {
                QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
            }
        }
        else
        {
            QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
        }
    }
    
    o/p :-
    true
    prdnumber =  1
    sq isvalid
    rd.fieldName(1)  "Stock"
    QSqlQuery::value: not positioned on a valid record
    QSqlQuery::value: not positioned on a valid record
    st =  0    0
    QSqlQuery::value: not positioned on a valid record
    QSqlQuery::value: not positioned on a valid record
    rate =  0
    QSqlQuery::value: not positioned on a valid record
    
    and also qDebug() << query->prepare(sql); is true
    

  • Qt Champions 2019

    Sorry but why are you now creating a new query inside the if statements??
    You should simply inspect QSqlRecord what it contains and print it out to see if it's what you expect...



  • i got its solution : "query->seek(0)" added before int st = query->value(0).toInt();
    and it happened


Log in to reply