How to get a query result into LineEdit widget?

qtnoob420

Hello,
so i already successfully estasblished a connection to my PostgreeSQL database and can open it.

Now i want to get the information into my qt designer ui. I have about 50 LineEdits, 200 CheckBoxes and some ComboBoxes and Dates.
My idea is it to get the needed information with a search field to get a set of data then input everything into their dedicated fields.

Here short here the part where i try to do that.

db.open();
QSqlQuery query = QSqlQuery(db);
query.exec("SELECT * FROM my_table WHERE id = 1"); //
query.exec("INSERT INTO lineedit_1, lineedit_2, ..."); //here is where i need help, i guess

i hope i could state my problem correct

Thanks in advance

JonB

@qtnoob420
You are not doing the right thing here. You use i to access a column in the query, yet you increment i for each new row fetched from query.next(). So you ask for:

row 0, column 0
row 1, column 1
row 2 column 2

And if you get only one row back, as per your query WHERE id = 1, you will only get one row and therefore only the first output from above.

To iterate all columns in rows, you mean something more like:

while (query.next())    // next *row*
{
    int col = 0;
    QVariant v = query().value(col);    // first *column*
    while (v.isValid())    // column exists, will return invalid QVariant once `col` goes beyond last column
    {
        ui->bem_1->insertPlainText(v.toString());
        col++;
        v = query().value(col);    // next *column*
    }
}

DJaq

@qtnoob420
Hello,
I'm a beginner with Qt and development in general, but one thing that I come to think of real quickly, is to iterate through the results of your query.

open the connection to the DB;
create and exec your SELECT query;
iterate into the results thanks to https://doc.qt.io/qt-5/qsqlquery.html#next ;
3,5: while iterating, you can get your components declared in your .UI file using findChild<T>("widget_name_to_find_goes_here") and you can use setText or setValue or setChecked on them (depending on their type).

Hope I understand your question correctly ^^

DJaq

@DJaq Oh and I forgot:
4. close your db connection ;)

jsulm

@qtnoob420 said in How to get a query result into LineEdit widget?:

I have about 50 LineEdits, 200 CheckBoxes and some ComboBoxes and Dates

Sounds like completely overloaded UI. Is all this on the same page/widget?

Pl45m4

@DJaq said in How to get a query result into LineEdit widget?:

findChild<T>("widget_name_to_find_goes_here")

You don't need to findChild here.
If @qtnoob420 has the ui pointer available, ui->widgetName (e.g. lineEdit_1) is enough to address the widget.

qtnoob420

@DJaq thanks for this first help.
i guess my exact problem is to find the commands (and make them work) to put these values into the widgets.

Pl45m4

@qtnoob420

Use the documentation:

jsulm

@qtnoob420 said in How to get a query result into LineEdit widget?:

to put these values into the widgets

You can't do that with SQL.
To set values you got from database using SQL you need to call whatever is appropriate for the particular widget. For example to set text in a QTextEdit call https://doc.qt.io/qt-5/qtextedit.html#setText

qtnoob420

@jsulm yeah i know it is a lot. it's splitted up into 3 pages. the requirements are set and is have to build a GUI for the database.

the overall topic is the control and maintenance of trees at the streets for my local government. there a ton problems, symptoms and general information required. so really nothing can be changed here. i guess it sound more than it actually is. i could provide a picture of my ui if that helps.

qtnoob420

@Pl45m4 okay thanks. now i found that i cann address them with ui->lineEdit_1->setText(); //now i need to find out how to put the specific query-part i need, into the matching fields

Pl45m4

@qtnoob420 said in How to get a query result into LineEdit widget?:

now i need to find out how to put the specific query-part i need, into the matching fields

https://doc.qt.io/qt-5/sql-sqlstatements.html#navigating-the-result-set

JonB

@qtnoob420
If you are prepared to use a QSqlQueryModel where you presently have a QSqlQuery, you could then hook up a QDataWidgetMapper to map any/all its columns to desired widgets. Especially nice if you have several columns/widgets to map. Up to you whether you find this simpler or more complicated than writing your own code off the QSqlQuery to map to widgets.

qtnoob420

@Pl45m4 ok i think i got it now. it just hard for me to understand things just from a documentation, because it always seems that my problems are too specific.

after trying to put stuff into a field now.
ui->lineEdit_1->setText(query.value(1).toString()); //this syntax is at least correct

i get this output message: "QSqlQuery::value: not positioned on a valid record"
so i guess that there is nothing in my query? maybe because i have a wrong "SELECT FROM WHERE" command?

Pl45m4

@qtnoob420 said in How to get a query result into LineEdit widget?:

because it always seems that my problems are too specific

There is nothing specific. This is basic QSql.. stuff usage.

so i guess that there is nothing in my query?

You are trying to access index 1, which means, you need to have at least two valid results here, which is unlikely, when picking a specific ID:
If you expect only one record, you need to pick index 0 (like almost everything in C++ or most programming languages starts at 0)

Ah made a mistake here.
The index is the field of your current record. But same rules apply: You need to have at least two fields in your record.

https://doc.qt.io/qt-5/qsqlquery.html#value

Using SELECT * is not recommended because the order of the fields in the query is undefined.

If your query is correct and your result is not empty, are you sure, that the second field contains a string?

You could check query.isValid() (https://doc.qt.io/qt-5/qsqlquery.html#isValid) before you continue to process the result.

JonB

@qtnoob420

: "QSqlQuery::value: not positioned on a valid record"

Please do look at the actual example given at https://doc.qt.io/qt-5/qsqlquery.html#details. You likely haven't called query.next()?

qtnoob420

@JonB thanks for the hint. i will look into it.

qtnoob420

@Pl45m4 ok i got it to work now. it atleast gives me the first value. probably i am mistaken here again.

int i=0;
while (query.next())
{
    ui->bem_1->insertPlainText(query.value(i).toString());
    i++;
}

it does not give other values than the id. i thought, that now all the values from the first row would be put into my plainText

JonB

@qtnoob420
You are not doing the right thing here. You use i to access a column in the query, yet you increment i for each new row fetched from query.next(). So you ask for:

row 0, column 0
row 1, column 1
row 2 column 2

And if you get only one row back, as per your query WHERE id = 1, you will only get one row and therefore only the first output from above.

To iterate all columns in rows, you mean something more like:

while (query.next())    // next *row*
{
    int col = 0;
    QVariant v = query().value(col);    // first *column*
    while (v.isValid())    // column exists, will return invalid QVariant once `col` goes beyond last column
    {
        ui->bem_1->insertPlainText(v.toString());
        col++;
        v = query().value(col);    // next *column*
    }
}

SGaist

Hi,

Did you try to first draw a suitable UI before diving into the SQL parts ?

As @jsulm noted, it looks like you are trying to implement a UI that might well be really difficult to use.

Depending on what your user needs to get you might want to consider a light page with only the basic information and then a second one with more details etc. You might even be better served by a simple table.

mrjj

@qtnoob420 said in How to get a query result into LineEdit widget?:

i could provide a picture of my ui if that helps.

Hi
Please do as lineEdits and checkboxes can easily be replaced by a table, making it much easier to load
and edit data and save it back to the database.

While it is possible to loop the QSqlQuery and assign each column to a given pre-placed widget, its
also likely to become a burden over time.
Just saving the data back will be "involving" as the widgets have no idea what column they belong to and
Qt already contains other data-aware classes to help with these tasks.
Like as @JonB mentions, using QSqlQueryModel + QDataWidgetMapper

Also, a QSqlTableModel + TableView would allow to directly edit a table with only a few lines of code, so
please show us the GUI so we can see if we can find a better way than manually updating and saving
50 LineEdits and 200 CheckBoxes.

alt text