column out of range problem

[rahulvishwakarma]

i am building small project, in Qt 5.7(on client) and mysql( on server). then in following program I stuck in "QMYSQLResult::data: column 1 out of range" problem. please help how to get rid of this.

void Sales::on_comboBoxName_currentTextChanged(const QString &arg1)
{
    QString sql = "select productID, Rate, Stock from tableProductRecords where productname Like '%"+arg1+ "%';";
    query->prepare(sql);
    if(query->exec(sql))
    {
        if(query->next())
        {
            ui->lineEditProductNumber->setText(query->value(0).toString().trimmed());
            qDebug() << "ui->lineEditProductNumber->text().trimmed();" << ui->lineEditProductNumber->text().trimmed();
            QString str1 = query->value(1).toString().trimmed();
 
            ui->lineEditRate->setText(str1); // here is error of column out of range
            qDebug() <<" ui->lineEditRate->text().trimmed(); " << ui->lineEditRate->text().trimmed();
            QString str2 = query->value(2).toString().trimmed();
            ui->lineEditStock->setText(str2);// here is error of column out of range
            qDebug() <<"ui->lineEditStock->text().trimmed();" <<   ui->lineEditStock->text().trimmed();
        }
    }
}

void Sales::on_comboBoxName_currentTextChanged(const QString &arg1)
{
    QString sql = "select productID, Rate, Stock from tableProductRecords where productname Like '%"+arg1+ "%';";
    query->prepare(sql);
    if(query->exec(sql))
    {
        if(query->next())
        {
            ui->lineEditProductNumber->setText(query->value(0).toString().trimmed());
            qDebug() << "ui->lineEditProductNumber->text().trimmed();" << ui->lineEditProductNumber->text().trimmed();
            QString str1 = query->value(1).toString().trimmed();

            ui->lineEditRate->setText(str1); // here is error of column out of range
            qDebug() <<" ui->lineEditRate->text().trimmed(); " << ui->lineEditRate->text().trimmed();
            QString str2 = query->value(2).toString().trimmed();
            ui->lineEditStock->setText(str2);// here is error of column out of range
            qDebug() <<"ui->lineEditStock->text().trimmed();" <<   ui->lineEditStock->text().trimmed();
        }
    }
}

To copy to clipboard, switch view to plain text mode 

output :-

ui->lineEditProductNumber->text().trimmed(); "1"
 
QMYSQLResult::data: column 1 out of range
 
 ui->lineEditRate->text().trimmed();  ""
 
QMYSQLResult::data: column 2 out of range
 ui->lineEditStock->text().trimmed(); ""
 
ui->lineEditProductNumber->text().trimmed(); "1"
 
 ui->lineEditRate->text().trimmed();  "7000"
 ui->lineEditStock->text().trimmed(); "4"

ui->lineEditProductNumber->text().trimmed(); "1"

QMYSQLResult::data: column 1 out of range

 ui->lineEditRate->text().trimmed();  ""

QMYSQLResult::data: column 2 out of range
 ui->lineEditStock->text().trimmed(); ""

ui->lineEditProductNumber->text().trimmed(); "1"

 ui->lineEditRate->text().trimmed();  "7000"
 ui->lineEditStock->text().trimmed(); "4"

To copy to clipboard, switch view to plain text mode 

how do I get rid of "column out of range".

[d_stranz]

ui->lineEditProductNumber->setText(query->value(0).toString().trimmed());

It always amazes me that programmers can write this kind of code, with absolutely no error checking, assuming that every pointer points to a valid instance and that every variable will contain or method call will result in a valid piece of data.

Don't they teach "defensive programming" any more? Even simple error checking? Or does everyone do like Boeing, and let a couple of planes full of people crash into the ground before they fix the bugs in the software?

how do I get rid of "column out of range".

QSqlQuery::isNull(), QSqlQuery::isValid(), QVariant::isValid(), QVariant::isNull(), QVariant::canConvert() are just a few of the methods available that might result in more reliable code and fewer error messages.

[rahulvishwakarma]

I tried like this :-

bool str1 = query->isValid();
bool flag = query->isActive();

QMessageBox::information(this, "on_comboBoxName_currentTextChanged", " flag : " + QString(flag) + " str1 : " + str1);
but gives nothing
column out of range _ pic.jpgcolumn out of range _ pic.jpg

[d_stranz]

Why do you think checking the status of the entire query will help in determining whether the contents of a specific column is valid or not? If you are getting these errors it means that 1) your database is corrupt or 2) there is no data in the columns you are asking for (those columns are NULL). Why don't you open your database in MySQL Workbench and examine what is really in the columns you are trying to retrieve?

[ChrisW67]

The first observation would make is that the error messages cannot possibly be coming from the lines indicated in the original code listing. The error plainly comes from the underlying SQL driver and the indicated lines are not accessing anything that would touch that. The messages originate with the line before in both cases.

The error message implies that the record you are looking at does not have field at index 1 or 2. This is not the same as these fields existing but being NULL or some type that cannot be converted to a QString. Rather it implies that the SQL the query object is returning rows from returns only a single column (since index 0 exists). The code you posted does not seem to match the result you claim if taken in isolation.

Where is the "query" variable declared? It is not local to this function (perhaps a member variable?), which opens the possibility that "query" is changing as result of other events outside this function. For example, is line 9 triggering a slot that modifies "query"?

qDebug value 0, 1, and 2 before line 9 to see what you have.

[Lesiok]

Before line 9 put this code :

QSqlRecord rec = query.record();
for(int i = 0; i < rec.size(); i++)
    qDebug() << rec.fieldName(i);

QSqlRecord rec = query.record();
for(int i = 0; i < rec.size(); i++)
    qDebug() << rec.fieldName(i);

To copy to clipboard, switch view to plain text mode 

You will see how many columns the record has and what they are called.

[rahulvishwakarma]

your code I put it in above line 9.

QSqlRecord rec = query.record();
for(int i = 0; i < rec.count(); i++)// size is not member of QSqlRecord
qDebug() << rec.fieldName(i);

and having this output :-

Starting /opt/projects/Qt/cbs_soft/build-cbs-Desktop_Qt_5_7_0_GCC_64bit-Debug/cbs...
"productID"
"Rate"
"Stock"
QMYSQLResult::data: column 1 out of range
QMYSQLResult::data: column 2 out of range

Starting /opt/projects/Qt/cbs_soft/build-cbs-Desktop_Qt_5_7_0_GCC_64bit-Debug/cbs...
"productID"
"Rate"
"Stock"
QMYSQLResult::data: column 1 out of range
QMYSQLResult::data: column 2 out of range

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

Yes, and what have you done with that information? After line 9 what does that Lesiok's debug code return?

[rahulvishwakarma]

i posted output in on above post. I didn't get your point by "what have you done with that information?"

[Lesiok]

And what happens when you replace indexes with names, that is:

ui->lineEditProductNumber->setText(query->value("productID").toString().trimmed());

ui->lineEditProductNumber->setText(query->value("productID").toString().trimmed());

To copy to clipboard, switch view to plain text mode 

etc.

[rahulvishwakarma]

this outputs as following :-

QSqlQuery::value: unknown field name 'Stock'
ui->lineEditStock->setText(query->value("Stock").toString().trimmed()); :  ""

QSqlQuery::value: unknown field name 'Stock'
ui->lineEditStock->setText(query->value("Stock").toString().trimmed()); :  ""

To copy to clipboard, switch view to plain text mode 

and

ui->lineEditProductNumber->setText(query->value("productId").toString().trimmed());
ui->lineEditProductNumber->text : "1"

ui->lineEditProductNumber->setText(query->value("productId").toString().trimmed());
ui->lineEditProductNumber->text : "1"

To copy to clipboard, switch view to plain text mode 

[d_stranz]

QSqlQuery::value: unknown field name 'Stock'

HINT, HINT: Your query is not returning you the record you think it is. The table you are querying has a field named "productId", but it doesn't have fields named "Rate" or "Stock" and so the record your query returns doesn't have more than one column.

As I said earlier, open your database using MySQL Workbench and actually look at the structure of the database and the tables in it. Then look at the query you are constructing in your code and see if it matches what you see in the Workbench.

[Lesiok]

HINT, HINT: Your query is not returning you the record you think it is. The table you are querying has a field named "productId", but it doesn't have fields named "Rate" or "Stock" and so the record your query returns doesn't have more than one column.

As I said earlier, open your database using MySQL Workbench and actually look at the structure of the database and the tables in it. Then look at the query you are constructing in your code and see if it matches what you see in the Workbench.

It is very strange to me. If the table has no columns, query->exec() should return false. The list of the record columns showed 3 names.
Either the code shown is different from the real code or MySQL is behaving strangely.

[d_stranz]

It is very strange to me. If the table has no columns, query->exec() should return false. The list of the record columns showed 3 names.
Either the code shown is different from the real code or MySQL is behaving strangely.

That was my thought too, but the OP claims everything is working except for the missing columns. There is no code to show how "query" is being created and used prior to the code snippet given in the first post..

[ChrisW67]

i posted output in on above post.

No, you posted the output before line 9 as Lesiok asked. I asked for the output after line 9.

The information from your output seems to be what is expected at that point in your program flow, but we know that later in the procedure the record is not what is expected. What happens in between, and how can it happen? (I also asked some questions that hint at how something unexpected might happen.)

I didn't get your point by "what have you done with that information?"

I guess my point is that I (we) would like to know what you have tried to debug the problem.