PDA

View Full Version : Sql query to retrieve database table data



Cyrebo
29th March 2013, 17:40
Hi, how do I retrieve certain content like name from a table? I want to retrieve all the names and then set them to a label in my form. For now I just want to how to get all the names. So far I've tried using a select count query to first get the number of rows but it didn't work. I'm not sure what functions to use for this if anyone can please help.

Here's what I have done:

db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(Path_to_DB);
QFileInfo checkFile(Path_to_DB);

if(checkFile.isFile())
{
if(db.open())
{
qDebug() << "Connected to database file";
}
}else{
qDebug() << "Database file not found";
}

QSqlQuery query;
QString qry = QString("SELECT Count(*) FROM customers");

query.prepare(qry);

if (query.exec())
{
int count = query.result(); //<<<Where I'm stuck
qDebug() << count;
for ( int i = 0; i < count; i++)
{
//create labels for each customer
QLabel *label = new QLabel(QString());
QCheckBox *chkbox = new QCheckBox;

Ui::MainWindow *ui;

ui->gridLayout->addWidget(label,0,0);
ui->gridLayout->addWidget(chkbox,0,1);

}
}
else
{
query.lastError();
}


Thanks in advance!

Lykurg
29th March 2013, 18:34
Hi,

see QSqlQuery::value(). Further there is no need to query the total size first. Just do SELECT name FROM customers and then loop through it using QSqlQuery::next(). This way you can replace your current for loop.


EDIT: And here you do not need prepare!

Cyrebo
29th March 2013, 23:56
Thanks for the quick response, the sql is sort of working because this will not actually let me implement the second part of my code..as you can see the last half of my code is commented out because my program flat out crashes when its not. I get the dreaded "The program has finished unexpectedly error" that gives no feedback on what caused it to so I'm not sure what I'm doing wrong here...

How would I implement a loop to create a label and checkbox for each item in the db individually?



QSqlQuery qry;

if (qry.exec("SELECT name FROM customers"))
{
while(qry.next())
{
qDebug() << qry.value(0).toString();
// QLabel *label = new QLabel(QString());
// QCheckBox *chkbox = new QCheckBox;

// Ui::MainWindow *ui;

// ui->gridLayout->addWidget(label,0,0);
// ui->gridLayout->addWidget(chkbox,0,1);
}
}
else
{
qDebug() << qry.lastError();
}

ChrisW67
30th March 2013, 07:20
:confused: Uhh, exactly like you have, except you would not try to put all of the labels into the same cell of the grid layout.

Lesiok
30th March 2013, 09:01
In line 11 You define pointer ui. Next You use this pointer in line 13 and 14 but the pointer is NOT initialised.
I suggest first learn the basics of C++.

Cyrebo
30th March 2013, 09:31
In line 11 You define pointer ui. Next You use this pointer in line 13 and 14 but the pointer is NOT initialised.
I suggest first learn the basics of C++.
I saw this later on and have changed my code quite a bit since that post. At the moment, only 1 label and lineEdit is showing up with 1 record, how do I make it one for each record? And my question on how to loop through all the total number of names from the sql query?

My code now:


QSqlQuery qry;

if (qry.exec("SELECT name FROM customer"))
{
while(qry.next())
{
qDebug() << qry.value(0).toString();
if(qry.isValid())
{
QString cust = qry.record().value(0).toString();
QLabel *label = new QLabel(QString(cust));
QLineEdit *lineEdit = new QLineEdit;
lineEdit->setInputMask("0");
lineEdit->setMaxLength(1);
lineEdit->setGeometry(0,0,41,31);
label->setGeometry(0,0,150,41);

ui->gridLayout->addWidget(label,0,0); //<<<<<<<<<<<Fills the entire width of layout,why?
ui->gridLayout->addWidget(lineEdit,0,1);//<<<<<<<<<<<Fills the entire width of layout,why?
}
}
}
else
{
qDebug() << qry.lastError();
}

Lesiok
30th March 2013, 11:12
And what if the base will be a few thousand records ? Read about QSqlTableModel

Cyrebo
30th March 2013, 11:59
I've managed to get it to read the names now and create a spinbox for each name rather than lineEdit/checkbox as previously. Now my problem is getting the value of the spinbox for each name like for example, the first name may get 1/2/3 value in the spinbox, how do i get this value and increment it in the database for that name? I don't know how to do this using QSqlTableModel for unknown names.

Here is where I'm at:


QSqlQuery qry;

if (qry.exec("SELECT name FROM customer"))
{
while(qry.next())
{
qDebug() << qry.value(0).toString();
if(qry.isValid())
{
QString cust = qry.record().value(0).toString();
QLabel *label = new QLabel(QString(cust));
QSpinBox *spinbox = new QSpinBox;
spinbox->setMaximum(3);
label->setGeometry(0,0,80,41);

ui->verticalLayout->addWidget(label);
ui->verticalLayout->addWidget(spinbox);
}
}
}
else
{
qDebug() << qry.lastError();
}

Need to know how to get the spinbox value and increment it for the name beside the spinbox in the label. I'm thinking it requires some kind of pointer but not sure...

Lesiok
30th March 2013, 18:11
Tell us what your problem is rather than how You try to solve it.

Cyrebo
30th March 2013, 19:20
Ok I want to read the value from the qspinbox I created specific to the label that the spin box that its beside. So something like name spinbox(1). I want to store that value to the person in the db table like on the form.