How to get the count of rows

Question

I want to get the count of rows from last query.
I have used size() and numRowsAffected() functions, but none of them work.

I have written the following code:

int counter = 0;
QStringList TableHeader;
QSqlQuery qry;
qry.prepare("SELECT *, (SELECT COUNT(*) FROM users) AS count FROM users");
qry.exec();

qDebug() << qry.value("count").toString(); // <<-- print the count of rows

ui->tableWidget->setRowCount(10);
ui->tableWidget->setColumnCount(3);
TableHeader << "Username" << "Password" << "Age";
ui->tableWidget->setHorizontalHeaderLabels(TableHeader);
while (qry.next()) {
    ui->tableWidget->setItem(counter, 0, new QTableWidgetItem(qry.value("username").toString()));
    ui->tableWidget->setItem(counter, 1, new QTableWidgetItem(qry.value("password").toString()));
    ui->tableWidget->setItem(counter, 2, new QTableWidgetItem(qry.value("age").toString()));
    ui->tableWidget->item(counter, 0)->setData(Qt::UserRole, qry.value("id").toString());
    counter++;
}

But unfortunately this does not work fine and gives me an error QSqlQuery::value: not positioned on a valid record.

I want to get the count of rows whether by using a query or function.

Possibly a duplicate of: http://stackoverflow.com/questions/9000123/qsqlquery-not-positioned-on-a-valid-record — Jeremy Friesner, Sep 07 '14 at 02:03
@JeremyFriesner: Thanks, but the answer of the question that in the link not clear, I need an example in order to understand the solution. — Lion King, Sep 07 '14 at 02:33
@JeremyFriesner: Quote from the answer which in the link: `additionally if your query returns more than one row, you should iterate via query.next().`, as you seen in my previous code, I have used `qry.next()`. For this reason, I want an example to clear the mystery. — Lion King, Sep 07 '14 at 02:40

score 4 · Accepted Answer · answered Sep 07 '14 at 07:40

4

qry.size() is not supported with SQLite. QSqlQuery::last () retrieves the last record in the result, if available, and positions the query on the retrieved record. After calling last() you can retrieve index of the last record and position the query before the first record using first() and previous() :

int numberOfRows = 0;
if(qry.last())
{
    numberOfRows =  qry.at() + 1;
    qry.first();
    qry.previous(); 
}

answered Sep 07 '14 at 07:40

Nejat

31,784
12
106
138

I thank you for your answer. but why you use `qry.at() + 1` **plus one**, also why you use `qry.previous() `, note that you use `qry.first();`. – Lion King Sep 07 '14 at 14:59
@LionKing `QSqlQuery::at` returns the index of the row starting from zero. So the first row has an index of `0` and the last one has an index of `n-1`. So plusing one is the number of the rows. `qry.previous()` is called to position the query before the first row. This is the exact state when you run query. If you don't like it you can simply emit it. – Nejat Sep 07 '14 at 15:52

How to get the count of rows

1 Answers1