B4J Question SQL Delete

Pelky · Jan 4, 2022

I have a database table:-
DB.ExecNonQuery ("CREATE TABLE Booking(Booth TEXT NOT NULL, BookingsDate TEXT NOT NULL, TimeOfDay TEXT Not Null, Visitor TEXT,Occupation TEXT, Company TEXT, Contact TEXT, Email TEXT, Inmate TEXT, Cell TEXT, Nomis TEXT, TypeOfBooking TEXT, TimeIn TEXT, TimeOut TEXT, S40E TEXT, Bookedby TEXT, DateBooked TEXT, Secure TEXT, CONSTRAINT BkKey UNIQUE (Booth, BookingsDate, TimeOfDay))")

and I need to delete all the records older than today -
I did -

Dim DelDate As String = DateTime.Now
DB.ExecNonQuery("DELETE * FROM Booking WHERE BookingsDate < DelDate")

I get the error -
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "*": syntax error)

Can someone tell me what I am doing wrong as I copied this directly from the tutorial.

Thank you

jahswant · Jan 4, 2022

Pelky said:
DB.ExecNonQuery("DELETE * FROM Booking WHERE BookingsDate < DelDate")

B4X:

DB.ExecNonQuery("DELETE FROM Booking WHERE BookingsDate < DelDate")

REMOVE the *

udg · Jan 4, 2022

In Sqlite is DELETE FROM, there's no asterisk after delete.

Edit: are you sure you can compare dates like that? Did you select a "proper" format (e.g. yyyy-MM-dd or similar) for recording and checking?

Pelky · Jan 4, 2022

jahswant said:
B4X:

DB.ExecNonQuery("DELETE FROM Booking WHERE BookingsDate < DelDate")

REMOVE the *

I thank you for your help - it is much appreciated however I tried that and I get this error:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: DelDate)

Pelky · Jan 4, 2022

udg said:
In Sqlite is DELETE FROM, there's no asterisk after delete.

Edit: are you sure you can compare dates like that? Did you select a "proper" format (e.g. yyyyMMdd or similar) for recording and checking?

thank you .... I tried and get
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: DelDate)

aeric · Jan 4, 2022

Pelky said:
thank you .... I tried and get
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: DelDate)

Isn't this obvious that DelDate is unknown?

SQL:

DELETE FROM Booking WHERE BookingsDate < date('now')

If you want to pass a value then:

B4X:

Dim DelDate As String = DateTime.Now
DB.ExecNonQuery2("DELETE FROM Booking WHERE BookingsDate < ?", Array As Object(DelDate))

But

B4X:

DateTime.Now

returns the ticks as Long.

Therefore:

B4X:

Dim DelDate As String = DateTime.Date(DateTime.Now)

Pelky · Jan 4, 2022

aeric said:
Isn't this obvious that DelDate is unknown?

SQL:

DELETE FROM Booking WHERE BookingsDate < date('now')

If you want to pass a value then:

B4X:

Dim DelDate As String = DateTime.Now DB.ExecNonQuery2("DELETE FROM Booking WHERE BookingsDate < ?", Array As Object(DelDate))

But

B4X:

DateTime.Now

returns the ticks as Long.

Therefore:

B4X:

Dim DelDate As String = DateTime.Date(DateTime.Now)

Thank You so very much.... I will give that a go

Pelky · Jan 4, 2022

aeric said:
Isn't this obvious that DelDate is unknown?

SQL:

DELETE FROM Booking WHERE BookingsDate < date('now')

If you want to pass a value then:

B4X:

Dim DelDate As String = DateTime.Now DB.ExecNonQuery2("DELETE FROM Booking WHERE BookingsDate < ?", Array As Object(DelDate))

But

B4X:

DateTime.Now

returns the ticks as Long.

Therefore:

B4X:

Dim DelDate As String = DateTime.Date(DateTime.Now)

Thank You so very much.... I will give that a go

B4J Question SQL Delete

Pelky

Active Member

jahswant

Well-Known Member

udg

Expert

Pelky

Active Member

Pelky

Active Member

aeric

Expert

Pelky

Active Member

Pelky

Active Member

Similar Threads

B4J Question SQL Delete

Active Member

Well-Known Member

Expert

Active Member

Active Member

Expert

Active Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency