query SQL column for specific data and display results



  • 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
    
    

  • Lifetime Qt Champion

    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.



  • 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: ";
    }
    }
    

    }


  • Lifetime Qt Champion

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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.