Sqlite
-
@jsulm
I created table employee
query.exec("CREATE table Employee(Name varchar(20),Age Integer,Id_No Integer,Gender varchar(10))");Inserting values
query1.exec("INSERT into Employee(Name,Age,Id_No,Gender)" "VALUES('" + ui->name->text() + "','" + ui->age->text() + "','" + ui->id->text() + "','" + ui->checkBox->text() + "')");
deleting values:
query2.exec("DELETE from EmployeeWHERE Name = '"+ui->name_2->text()+"'");
update values:
query->setQuery("Update Employee set Name ='" +ui->name_2->text() + "' WHERE Name = '" + ui->name_2->text()+ "'");How to delete the last inserted value ie. Inserted 10 values deleting 10 th value next 9 , 8....?
-
@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(); }
-
@Driftwood Will not work since the column name does not match as @jsulm already pointed out several times....
-
@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. -
@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))");...
-
@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 becauseMAX(rowid)
will find the largestrowid
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.
-
@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... -
@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.
-
@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.
-
@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
. Thendelete 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. -
-
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
andpassword
. - 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?
- Your database has a table named
-
- 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.
-
@KenAppleby-0
I assume the OP knows he needs and has a tableusers
with columnsusername
&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.