Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Update: Forum Guidelines & Code of Conduct

    Unsolved query SQL column for specific data and display results

    General and Desktop
    sql database sqlite3
    2
    4
    2344
    Loading More Posts
    • 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.
    • V
      valerio.j last edited by

      Hello,

      I have a simple SQL database with a few columns. What I'm trying to accomplish is search a specific column for data and if it matches, display contents of that row id into a form. For example I have a form with a text box where a search string is entered, once the search button is pressed the rest of the text fields are populated with all the data. Below is what I've done so far. I can connect to the database and fill the entire table by just calling fillSearchForm();. But having issues with the search. Hope someone can help.

      #include "mainwindow.h"
      #include "ui_mainwindow.h"
      #include <QSerialPort>
      
      QSerialPort serial;
      
      MainWindow::MainWindow(QWidget *parent) :
          QMainWindow(parent),
          ui(new Ui::MainWindow)
      {
              ui->setupUi(this);
              db = QSqlDatabase::addDatabase("QSQLITE");
              db.setDatabaseName("../SmartBins/dataBases/binsDatabase.sqlite3");
              db.open();
      
              query = QSqlQuery (db);
              query.exec("SELECT ID, intPartNumber, Manufacturer, manufacturerPartNumber, Description, imagePath, binLocation, binID FROM dataBins");
      
              if (query.isActive())
              {
                  ui->statusBar->showMessage("Connected to Database");
                  query.first();
                  //fillSearchForm();
              }
      
              else
              {
                  ui->statusBar->showMessage(QString("Database connection failed: error / ") + query.lastError().text());
              }
      }
      
      MainWindow::~MainWindow()
      {
          delete ui;
          db.close();
          serial.close();
      
      }
      
      void MainWindow::on_Search_clicked()
      {
      
          QString pnSearch, pnDbSearch;
          pnSearch = ui->intPartNumber->text();
          pnDbSearch = query.value(1).toString();
      
          //qDebug() << pnDbSearch;
          //qDebug() << pnSearch;
      
          if (pnSearch == pnDbSearch)
              query.next();
                  fillSearchForm();
          //query.first();   
      
      }
      
      void MainWindow::fillSearchForm()
      {
      
      ui->intPartNumber->setText(query.value(1).toString());
      ui->Manufacturer->setText(query.value(2).toString());
      ui->manufacturerPartNumber->setText(query.value(3).toString());
      ui->Description->setText(query.value(4).toString());
      //ui->imagePath->setText(query.value(5).toString());
      ui->binLocation->setText(query.value(6).toString());
      ui->binID->setText(query.value(7).toString());
      
      }
      
      
      #ifndef MAINWINDOW_H
      #define MAINWINDOW_H
      
      #include <QMainWindow>
      #include <QtSql>
      #include <QDebug>
      
      namespace Ui {
      class MainWindow;
      }
      
      class MainWindow : public QMainWindow
      {
          Q_OBJECT
      
      public:
          explicit MainWindow(QWidget *parent = 0);
          ~MainWindow();
      
      private slots:
      
          void on_Search_clicked();
      
      private:
          Ui::MainWindow *ui;
      
          QSqlDatabase db;
          QSqlQuery query;
          void fillSearchForm();
      };
      
      #endif // MAINWINDOW_H
      
      
      1 Reply Last reply Reply Quote 1
      • SGaist
        SGaist Lifetime Qt Champion last edited by

        Hi,

        What is exactly your problem ?

        Are you sure that you are looking at the right place for the database file ?

        You also don't check that it opened successfully.

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply Reply Quote 0
        • V
          valerio.j last edited by

          I don't have a problem accessing the database. I'm having issues pulling out a data and displaying the actual row. When I call on_Search_clicked(); it finds the data based on the condition, but it doesn't pull that out, it pulls the next id.

          void MainWindow::on_Search_clicked()
          {

          QSqlQuery checkQuery = QSqlQuery(db);
          checkQuery.prepare("SELECT ID, intPartNumber FROM dataBins WHERE intPartNumber = (:intPartNumber)");
          
          checkQuery.bindValue(":ID", query.value(0));
          checkQuery.bindValue(":intPartNumber", ui->intPartNumber->text());
          
          if (checkQuery.exec())
          {
              if (checkQuery.next())
              {
                  qDebug() << "intPartNumber Found: ";
                  fillSearchForm();
              }
          
          else
          {
              qDebug() << "intPartNumber not Found: ";
          }
          }
          

          }

          1 Reply Last reply Reply Quote 0
          • SGaist
            SGaist Lifetime Qt Champion last edited by

            That's strange, you are binding to :ID which is not available in your query.

            Interested in AI ? www.idiap.ch
            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

            1 Reply Last reply Reply Quote 0
            • First post
              Last post