query SQL column for specific data and display results

valerio.j

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

SGaist

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.

valerio.j

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

}

SGaist

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