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. query SQL column for specific data and display results
Forum Updated to NodeBB v4.3 + New Features

query SQL column for specific data and display results

Scheduled Pinned Locked Moved Unsolved General and Desktop
sql databasesqlite3
4 Posts 2 Posters 3.0k 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.
  • V Offline
    V Offline
    valerio.j
    wrote on last edited by
    #1

    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
    1
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      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
      0
      • V Offline
        V Offline
        valerio.j
        wrote on last edited by
        #3

        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
        0
        • SGaistS Offline
          SGaistS Offline
          SGaist
          Lifetime Qt Champion
          wrote on last edited by
          #4

          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
          0

          • Login

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