Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. can not getting sql result.
Forum Updated to NodeBB v4.3 + New Features

can not getting sql result.

Scheduled Pinned Locked Moved Solved General and Desktop
9 Posts 4 Posters 558 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • R Offline
    R Offline
    rahulvishwakarma
    wrote on last edited by
    #1

    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

    Pablo J. RoginaP 1 Reply Last reply
    0
    • Christian EhrlicherC Online
      Christian EhrlicherC Online
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on last edited by
      #2

      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

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      1 Reply Last reply
      2
      • R rahulvishwakarma

        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

        Pablo J. RoginaP Offline
        Pablo J. RoginaP Offline
        Pablo J. Rogina
        wrote on last edited by
        #3

        @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?

        Upvote the answer(s) that helped you solve the issue
        Use "Topic Tools" button to mark your post as Solved
        Add screenshots via postimage.org
        Don't ask support requests via chat/PM. Please use the forum so others can benefit from the solution in the future

        1 Reply Last reply
        1
        • R Offline
          R Offline
          rahulvishwakarma
          wrote on last edited by
          #4

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

          Christian EhrlicherC 1 Reply Last reply
          0
          • hskoglundH Offline
            hskoglundH Offline
            hskoglund
            wrote on last edited by
            #5

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

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

            does it return true or false?

            1 Reply Last reply
            0
            • R rahulvishwakarma

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

              Christian EhrlicherC Online
              Christian EhrlicherC Online
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on last edited by
              #6

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

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              1 Reply Last reply
              0
              • R Offline
                R Offline
                rahulvishwakarma
                wrote on last edited by
                #7

                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
                
                1 Reply Last reply
                0
                • Christian EhrlicherC Online
                  Christian EhrlicherC Online
                  Christian Ehrlicher
                  Lifetime Qt Champion
                  wrote on last edited by
                  #8

                  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...

                  Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                  Visit the Qt Academy at https://academy.qt.io/catalog

                  1 Reply Last reply
                  1
                  • R Offline
                    R Offline
                    rahulvishwakarma
                    wrote on last edited by
                    #9

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

                    1 Reply Last reply
                    0

                    • Login

                    • Login or register to search.
                    • First post
                      Last post
                    0
                    • Categories
                    • Recent
                    • Tags
                    • Popular
                    • Users
                    • Groups
                    • Search
                    • Get Qt Extensions
                    • Unsolved