B4J Question UPDATE and SQL Database

Pelky · Nov 24, 2021

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.

Table Creation:

        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:

    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 · Nov 24, 2021

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

B4X:

"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 · Nov 24, 2021

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 · Nov 24, 2021

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 · Nov 24, 2021

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 · Nov 24, 2021

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 · Nov 24, 2021

Pelky said:
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

B4X:

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

Pelky · Nov 24, 2021

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) · Nov 24, 2021

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 · Nov 24, 2021

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 · Nov 24, 2021

would the ? imply the correct condition?

udg · Nov 24, 2021

Try

B4X:

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 · Nov 24, 2021

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 · Nov 24, 2021

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 · Nov 24, 2021

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

Feel the power of Smart Strings Literal

B4J Question UPDATE and SQL Database

Pelky

Active Member

josejad

Expert

SQL: AND Condition

Pelky

Active Member

josejad

Expert

Tip: DB Browser for SQLite

Pelky

Active Member

klaus

Expert

DaleA

Member

Pelky

Active Member

Andrew (Digitwell)

Well-Known Member

klaus

Expert

Pelky

Active Member

udg

Expert

Pelky

Active Member

Pelky

Active Member

josejad

Expert

Similar Threads

B4J Question UPDATE and SQL Database

Active Member

Expert

Active Member

Expert

Active Member

Expert

Member

Active Member

Well-Known Member

Expert

Active Member

Expert

Active Member

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency