B4J Question SQL Delete

[Pelky]

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]

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

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

REMOVE the *

 

[udg]

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]

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]

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]

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?

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

If you want to pass a value then:

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

But

DateTime.Now

returns the ticks as Long.

Therefore:

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

 

[Pelky]

Isn't this obvious that DelDate is unknown?

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

If you want to pass a value then:

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

But

DateTime.Now

returns the ticks as Long.

Therefore:

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

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

 

[Pelky]

Isn't this obvious that DelDate is unknown?

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

If you want to pass a value then:

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

But

DateTime.Now

returns the ticks as Long.

Therefore:

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

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