Sqlite

jsulm

@Rockerz How about answering my question?
Your id column is called Id_No - why do you use Id in your delete query?!

Rockerz

@jsulm It will take default as row id and it works maximum id number will be deleted when button clicked and I put Id_No also work value will be deleted ?

I tried this method but not work

query.prepare("SELECT ROWID FROM Employee ORDER BY ROWID DESC LIMIT 1");
if (query.exec() && query.size()) {
    int rowid = query.value(0).toInt();
    query.addBindValue(rowid);
   query.exec();
}

jsulm

@Rockerz I give up, you're now answering questions...
And the code you just posted makes no sense as you're binding id in the same query which has nothing to bind.

Driftwood

@Rockerz - This is the query you need:

query.prepare("DELETE FROM Employee WHERE rowid = (SELECT MAX(rowid) FROM Employee)");

Hope it helps.

Christian Ehrlicher

@Driftwood Will not work since the column name does not match as @jsulm already pointed out several times....

Driftwood

@Christian-Ehrlicher - It'll work just fine as it deletes the last entry made, finding it using MAX(rowid). So, assuming the table name's correct, the query will do its job.

Christian Ehrlicher

@Driftwood said in Sqlite:

MAX(rowid)

I created table employee
query.exec("CREATE table Employee(Name varchar(20),Age Integer,Id_No Integer,Gender varchar(10))");

...

Driftwood

@Christian-Ehrlicher - You must not know SQLite very well. MAX(rowid) are both parts of SQLite. My query uses only the table name for a reason. That's because MAX(rowid) will find the largest rowid of the table. It doesn't matter what field names are in your table for this to work because it uses nothing but the table name.

Hope this helps.

Christian Ehrlicher

@Driftwood Reading the create table statement would reveal that there is no column 'rowid' as @jsulm already pointed out.
And yes, maybe my sql is not that good...

Driftwood

@Christian-Ehrlicher - SQLite's rowid is available for every table SQLite accesses. It is not not not a user-created column; it's an SQLite counting mechanism used to keep track of how many rows a table has.

As an aside, please know what you're talking about before discarding working answers.

Hope this helps.

ChrisW67

@Driftwood said in Sqlite:

SQLite's rowid is available for every table SQLite accesses.

Every table except virtual tables or those declared "WITHOUT ROWID". So, most tables are ROWID tables.

The ROWID is not guaranteed to be montonically increasing, except in certain circumstances, although it usually is. The rest of the time it is random or inserted by the user. From Sqlite Autoincrement:

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used.

and

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

Relying on max(rowid) being the last inserted row will usually work, until it doesn't, probably at the least convenient moment.

Driftwood

@ChrisW67 - My query has an excellent chance of working for the Op. If it fails, Op needs to provide table creation information.

JonB

@ChrisW67 , @Driftwood
Depending on what exactly is meant by "the last inserted row", and to allow for insertions after deletions where ROWID might be re-used, the OP should consider using one of:

A column defined as created_at TIMESTAMP DEFAULT now() NOT NULL. Then delete from Employee where created_at = ( select max(created_at) from Employee);. However, if multiple rows were created at exactly the same time this could delete more than one.
Use explicit AUTOINCREMENT, as described in detail at 3. The AUTOINCREMENT Keyword in https://www.sqlite.org/autoinc.html. E.g. INTEGER PRIMARY KEY AUTOINCREMENT.

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing.

Maybe in practice "DELETE FROM Employee WHERE rowid = (SELECT MAX(rowid) FROM Employee) is sufficient for the OP's purposes, and does not require any changes to the table's definition. But I put this here for completeness.

Rockerz

This post is deleted!

JonB

@Rockerz said in Sqlite:

Parameter count mismatch

Sadly this error message can arise almost whatever an error is, not necessarily what it seems to indicate. Try/prove:

Your database has a table named users.
That table has columns username and password.
I don't know what SQLite "reserved words" are or how you "quote" them. Might username be a reserved word? E.g. try [username] in your query?
Although it should work with unnamed parameters (?) try with named ones (:username) in case that solves?

KenAppleby 0

@Rockerz

Username and password have no meaning in SQLITE. An SQLITE database is just a local file.
There is no implicit table "users"
When you construct a QSqlQuery you must pass the database as an argument to the ctor or the query doesn't know what database connection you mean.

JonB

@KenAppleby-0
I assume the OP knows he needs and has a table users with columns username & password. Nothing to do with the system. He needs to verify this, per my previous.

When you construct a QSqlQuery you must pass the database as an argument to the ctor or the query doesn't know what database connection you mean.

Not so. It uses the default database (if none specified), here the database he has set/opened.

KenAppleby 0

@JonB

@JonB said in Sqlite:

I assume the OP knows he needs and has a table users with columns username & password

I realise that. I am assuming, based on his offered code, that he doesn't, perhaps being used to e.g. MySql.

@JonB said in Sqlite:

Not so. It uses the default database (if none specified), here the database he has set/opened.

I didn't know that. Thank you. But it is still better to specify which connection you are using IMO.