Select data from Database to line edit
-
Good morning
i have made a project in which a user enters variables to line edits and they are stored into a mysql db but while i can store them using the INSERT INTO sql statment i canot retrieve them back.
Here is the code
1. QString V_1; 2. QSqlQuery query; 3. query.prepare("SELECT (V_1) FROM vathmoi"); 4. query.exec(); 5. 6. if(!query.exec()) { 7. qDebug() << "SQL Statement Error 2" << query.lastError(); 8. } 9. 10. while (query.next()) 11. { 12. V_1 = query.value(2).toString(); 13. 14. } 15. if(!query.next()) { 16. qDebug() << "SQL Statement Error 3" << query.lastError(); 17. }
When i debug it says :
- QMYSQLResult::data: column 1 out of range //(it says that about 20 times perhaps because i have used while)
- SQL Statement Error 3 QSqlError("", "", "")
when i use break in line 3 like 13. break; the first error apears only once and the second does not appear at all
Although the program starts normaly and i can send the variables normaly i can not fetch them.
Thank you in advance.
-
You're selecting just one column (and perhaps many rows?) from your database, but in line 12 you try to access the 3. column. I guess this is where you get your "out of range" error. Try
query.value(0).toString();
to access the value in the first column.The next is that the error handling in line 15 will always print a debug message because the while-loop runs till the last record (line 10) so lin line 15 there can't be a "next" value. Line 15 makes no sense i think...
-
@micland
Thanks for your answer.It works quite well but when i tried to do the same for the second line edit(lineEdit_3) nothing happens it retrieves only the data from the first lineEdit.
Here is the code:
{ QString V_1; QSqlQuery query; query.prepare("SELECT (V_1) FROM vathmoi"); query.exec(); if(!query.exec()) { qDebug() << "SQL Statement Error 2" << query.lastError(); } while (query.next()) { V_1 = query.value(0).toString(); break; } if(!query.next()) { qDebug() << "SQL Statement Error 3" << query.lastError(); } ui->lineEdit->setText(V_1); } { QString V_2; QSqlQuery query2; query2.prepare("SELECT (V_2) FROM vathmoi"); query2.exec(); if(!query2.exec()) { qDebug() << "SQL Statement Error 2" << query2.lastError(); } while (query2.next()) { V_2 = query2.value(0).toString(); break; } if(!query2.next()) { qDebug() << "SQL Statement Error 3" << query2.lastError(); } ui->lineEdit_3->setText(V_2); }
except from the first line edit i have other 39 which stand for the grade in every lesson that a student has. The user gives the grade that he scored in each lesson this grade is stored in the db and the app calculates the gp of the student degree. When the user opens the app again it has to bring from the db every grade that the user inserted from the previous session.
-
Hi,
Why are you executing your queries twice ?
Also, your
if (!queryX.next())
tests are fishy. You first run a while loop that will stop after one iteration and then you deemed that if there's not anymore values, it's a error. What about he case where you query returns only one element ? -
@Lazar1 it maybe seems to not works (but... if this compil on this simple code, then it works) because in your table, the last row has NULL value insde the column you call.
like said sgaist, your C++ code is not optimized and show that you miss understand some points of interest coding and SQL statements.
Then, your query SQL has no condition (Select "V_1" FROM table WHERE user = :name ;)
then query2.bindValue(":name", my_user); /// take care of type variables
from that, you should have answers for user concerned by the selection related to the user you search datas (in simple alone table without foreign keys).
Also, inside you query catch return from database answer, don't break the loop, but use variable type adated for push answers inside instead:QList answer2; while(query2.next()) answer2 << query2.value(0);
you can also call Select for all users and use a QMap or QHash container for put answer inside by userName or whatever you want.
that is the idea.
I read your comment and think you will add a user name edit line (or better: QCombobox)...And then, you can just use one function for call queries came from your lineEdit2 by use event catch signal
// (answer1 and answer2 are declared as private QList<QVariant>... void on_lineEdit2_editingFinished() { answer2 = askDB(ui->lineEdit2->text()); } void on_lineEdit1_editingFinished() { answer1 = askDB(ui->lineEdit1->text()); } QList<QVariant> askDB(const QString &q) { QList<QVariant> answer; if(!query.exec()) qDebug() << "ERR2:" << query2.lastError(); while(query.next) answer << query.value(0); }
like that, first your query code will works for all and no double redondant code writing to. then you should also catch all the return from database query call, and then... you can use all type of data from QVariant to when need it.
i think also from there you can modify and find best idea for what you want to do. The idea is to never have double codes and separate functions for use them when need and try to do it simple (in the way that, when some other one read and know C++.. it seems simple). If you need to do many things with database (update, create, delete...) you can also create a dedicated class for taht and inherit each time you need it (like that you will use the a powerfull design pattern of OO nice C++ language to).But don't forget that, directly ask SQL query from edit lines could become a secure problem. So the idea could maybe be to use QlistView for show answers, use QComboBox for ask for entries or restricted container input entries, or a function for check and valid entries questions to pass to the database, and use SQL for is powerfull relationships.
having fun