B4J Question UPDATE and SQL Database

[Pelky]

I have looked in the tutorial and loaded the examples etc. Also copied the code from the SQL Manual and changed to suit and failed.

        SQL1.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, CONSTRAINT BkKey UNIQUE (Booth, BookingsDate, TimeOfDay))")

    SQL1.ExecNonQuery2("Update Booking Set BookedBy = ?, Email = ? WHERE Booth = ?, BookingDate = ?, TimeOfDay = ?" Array As String(dtaBookedby, dtaemail, dtaBooth, dtaBookingDate, dtaTimeOfDay))

this is a simplified UPDATE format as the actual line is so long.
I have a key which may be the problem? I have no problem reading writing to the Database but I cannot get the update to work

Thankyou in advance

 

[josejad]

You must get some error in the logs.
Probably something saying there's an error near WHERE.
You're not using AND with the WHERE. It should be something like

"Update Booking Set BookedBy = ?, Email = ? WHERE Booth = ? AND BookingDate = ? AND TimeOfDay = ?"

For example, check:

SQL: AND Condition

This SQL tutorial explains how to use the SQL AND condition with syntax and examples. The SQL AND condition (also known as the AND Operator) is used to test for two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

www.techonthenet.com

 

[Pelky]

Thank you for your response. I did have it the other way but it also failed.

DB.ExecNonQuery2("Update Booking Set BookedBy = ?, Email = ? WHERE Booth = dtaBooth and BookingDate = dtaBookinDate and TimeOfDay = dtaTimeOfDay" Array As String(dtaBookedby, dtaeMail, dtaBooth, dtaBookingDate, dtaTimeOfDay))

this way edited Original.

Compile Error:-
Error occurred on line: 656 (B4XMainPage)
DB.ExecNonQuery2("Update Booking Set BookedBy = ?, Email = ? WHERE Booth = dtaBooth and BookingDate = dtaBookinDate and TimeOfDay = dtaTimeOfDay" Array As String(dtaBookedby, dtaemail, dtaBooth, dtaBookingDate, dtaTimeOfDay))

just cant see the problem

 

[josejad]

Check your SQL first in a DB Browser, for example, for sqlite, you can test with:

Tip: DB Browser for SQLite

I highly recommend: https://sqlitebrowser.org/ Especially when you need to create or alter complex tables.

www.b4x.com

 

[Pelky]

I did try that and the Database looks fine. I didnt use your browser however i will certainly have a look at it. I have actually cut down on the entire update line
to make it actually work but again i fail

what i did in another piece of this app was to just drop the table - recreate it and then insert. sadly this area is way to big to do that

 

[klaus]

Not sure if this could be the problem, but I think that the column names are case sensitive.
In CreateTable you define Bookedby.
And in the Update you use BookedBy.

 

[DaleA]

DB.ExecNonQuery2("Update Booking Set BookedBy = ?, Email = ? WHERE Booth = dtaBooth and BookingDate = dtaBookinDate and TimeOfDay = dtaTimeOfDay" Array As String(dtaBookedby, dtaeMail, dtaBooth, dtaBookingDate, dtaTimeOfDay))

The first thing I see here is that the number of query points, the question marks, doesn't match the number of parameters being passed in. You have 2 question marks and are passing in 5 values.

The way I see it, your statement should be something like

DB.ExecNonQuery2("Update Booking Set BookedBy = ?, Email = ? WHERE Booth = dtaBooth and BookingDate = dtaBookinDate and TimeOfDay = dtaTimeOfDay", Array As String(dtaBookedby, dtaeMail))

 

[Pelky]

Good Evening Klaus - thank you for your response.

I have amended the line to read:-

dtaBookingDate = BookingsData.Get("BookingDate")
dtaBookedby = BookingsData.Get("BookedBy")
dtaTimeOfDay = BookingsData.Get("TimeOfDay")
dtaemail = BookingsData.Get("Email")
dtaBooth = BookingsData.Get("Booth")
Log("Data User "& dtaBookedby)
Log("Data eMAIL "& dtaemail)
Log("Booth "&dtaBooth)


DB.ExecNonQuery2("Update Booking Set Bookedby = ?, Email = ? WHERE Booth = dtaBooth and BookingDate = dtaBookinDate and TimeOfDay = dtaTimeOfDay", Array As String(dtaBookedby, dtaemail))

and I get the following error at runtime

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

I cant see how it is looking at dtaBooth (variable) as a column

 

[Andrew (Digitwell)]

Not sure if this could be the problem, but I think that the column names are case sensitive.
In CreateTable you define Bookedby.
And in the Update you use BookedBy.

Also Create table has Bookingsdate and update has Bookingdate.

Why have date and time fields as text?

 

[klaus]

I cannot see it either, but with just some few code snippets it is impossible to give a concrete advice.
You use DB.ExecNonQuery2 therefor you could also use ? for WHERE conditions.
Then dtaBooth is a variable, but introduce it as text in the query.
And Booth is a TEXT column, you need to put the value between single quotes.
WHERE Booth = '& dtaBooth & "' And ...
Maybe for the other too.

 

[Pelky]

would the ? imply the correct condition?

 

[udg]

Try

DB.ExecNonQuery2($"Update Booking Set Bookedby = ?, Email = ? WHERE Booth = '${dtaBooth}' and BookingDate = '${dtaBookinDate}' and TimeOfDay = '${dtaTimeOfDay}' "$, Array As String(dtaBookedby, dtaemail))

I agree with @klaus : better to use ? in the WHERE clause too, adding items to the array, as José showed in post#2

 

[Pelky]

I have copied your line into my code and the log showed it not written - however i also tried the before with spelling error sorted etc
and the log showed that written however the data was not updated.

Try
DB.ExecNonQuery2($"Update Booking Set Bookedby = ?, Email = ? WHERE Booth = '${dtaBooth}' and BookingDate = '${dtaBookingDate}' and TimeOfDay = '${dtaTimeOfDay}' "$, Array As String(dtaBookedby, dtaemail))
' DB.ExecNonQuery2("Update Booking Set Inmate = ?, Bookedby = ?, Email = ? WHERE Booth = ? And BookingsDate = ? And TimeOfDay = ?", Array As String(dtaInmate, dtaBookedby, dtaemail))
Log("written away")
Catch
Log("not written")
End Try

by the way i included an additional field so I could check the update on the screen

 

[Pelky]

Okay just to let you all know and update you as to where I am.

It is WORKING! ... though why I don't know. I now have it updating the correct records having gone back in my code and realised i was being daft
but mainly because of your help.

I returned to UDG and your ${dtaBooth} type of coding. What does the ${.. } do?

I dont know how to thank you all - it is so comforting to know that the community is out there to help those of us who are not up to speed.

Really appreciated - Thank you

 

[josejad]

I returned to UDG and your ${dtaBooth} type of coding. What does the ${.. } do?

Feel the power of Smart Strings Literal