Syntax error?

Richard_Summers · June 19, 2014, 4:14pm

Hi,
Can someone please tell me why I am getting the following error: Error 1 - Unable to Prepare Statement

I have 2 properties in the window:

FinalHexadecimal As String
FinalRgb As String

Hers is my code to insert into the database:

[code] // SET THE VARIABLE VALUES
FinalHexadecimal = ("&c" + ColoursWindow.Hex1Field.text + ColoursWindow.Hex2Field.text + ColoursWindow.Hex3Field.text)
FinalRgb = “RGB(” + ColoursWindow.RField.text +"," + ColoursWindow.GField.text + “,” + ColoursWindow.BField.text + “)”

// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO Colours (Hex, Rgb) VALUES (?, ?);”)

// BIND THE VARIABLE’S VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, FinalHexadecimal)
ps.Bind(1, FinalRgb)

// INSERT THE VALUES INTO THE DATABASE
ps.SQLExecute[/code]

Could it be something to do with the “id” Primary Key Integer NOT NULL ?

Here is a screenshot of my database:

Thank you all in advance.

Patrick_Delaney · June 19, 2014, 4:25pm

My immediate thought is the “,” delimiter when building FinalRgb is causing a problem - the statement may all of a sudden think you have an extra few parameters.

Try, temporarily changing to a “-” or something to see if thats the problem…

Richard_Summers · June 19, 2014, 4:33pm

Unfortunately - it made no difference

Patrick_Delaney · June 19, 2014, 4:38pm

that’s a good thing! It means the DB driver is escaping the string when it tries to prepare the statement.

What are the values of FinalHexadecimal and FinalRgb when you run this?

Patrick_Delaney · June 19, 2014, 4:40pm

Hang on… Columns Position and id don’t allow nulls?

Should id be an auto increment field and you be entering a position value.

(Unless that create statement in your screenshot is wrong)

Richard_Summers · June 19, 2014, 4:40pm

As the Method is in the same window, I have removed the window path (in order to make my code more legible).

[code] // SET THE VARIABLE VALUES
FinalHexadecimal = ("&c" + Hex1Field.text + Hex2Field.text + Hex3Field.text)
FinalRgb = “RGB(” + RField.text +"," + GField.text + “,” + BField.text + “)”

// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO Colours (Hex, Rgb) VALUES (?, ?);”)

// BIND THE TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, FinalHexadecimal)
ps.Bind(1, FinalRgb)

// INSERT THE VALUES INTO THE DATABASE
ps.SQLExecute[/code]

Peter_Fargo · June 19, 2014, 4:42pm

I think Patrick nailed it. No value for ID. Autoincrement is most likely what you want.

Richard_Summers · June 19, 2014, 4:47pm

I will try that now - stay tuned (please)

Richard_Summers · June 19, 2014, 4:50pm

Changed the id Column to INTEGER PRIMARY KEY AUTOINCREMENT.
Same error?

Did you say that I also need to set a value for the Position Column?

Peter_Fargo · June 19, 2014, 4:54pm

#1 AUTOINCREMENT - At least I know this works.

Richard_Summers · June 19, 2014, 4:55pm

Just unsure now about the Position Column?
Must that Column have a value set immediately?

Peter_Fargo · June 19, 2014, 5:00pm

Create Table Colours(id PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, Hex TEXT, Rgb TEXT, Position INTEGER);

Patrick_Delaney · June 19, 2014, 5:01pm

If that create table statement is accurate

1 - you need to enter a value for the id column as it doesn’t accept Nulls. Making it an auto increment field makes sense to me, then it’ll be auto-populated

2 - the position column doesn’t allow nulls so you’ll have to enter a value for it you have two choices

put a default constraint on the column so if you don’t pass a value, it’ll use the default value
adjust your prepared statement to add a 3rd parameter - Position.

Richard_Summers · June 19, 2014, 5:10pm

Can I set the Position column to INTEGER AUTOINCREMENT UNIQUE, as I am only using this column so that the listbox entries can be drag re-arranged, and then the order saved).

If I CAN set the Position column to INTEGER AUTOINCREMENT UNIQUE - what value do I put in the final bind statement??

My code is now as follows:

[code] // SET THE VARIABLE VALUES
FinalHexadecimal = ("&c" + Hex1Field.text + Hex2Field.text + Hex3Field.text)
FinalRgb = “RGB(” + RField.text +"," + GField.text + “,” + BField.text + “)”

// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO Colours (Hex, Rgb, Position) VALUES (?, ?, ?);”)

// BIND THE VARIABLE VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, FinalHexadecimal)
ps.Bind(1, FinalRgb)
ps.Bind(2, WHAT GOES HERE?)

// INSERT THE VALUES INTO THE DATABASE
ps.SQLExecute[/code]

Patrick_Delaney · June 19, 2014, 5:35pm

Auto-increment fields don’t need to be defined by the Insert/Update statement, they get populated automatically by the database engine.

Besides all that, SQLite only allows Primary Key columns of type Integer to be auto increment columns so you couldn’t do that on the Position column anyway!

I’m guessing here but are you looping over a listbox and inserting/updating the database? If so could you simply use the counter variable?

Alternatively - from the listbox docs
Items in single-column ListBoxes can be accessed using the List property. The List property is an array. Arrays are zero-based. This means that the first row of the List property of a ListBox is row number 0 (zero).
Could you do something with that?

Richard_Summers · June 19, 2014, 5:44pm

Ok, I am trying to make my listbox rows draggable, and then saved, so as to appear in the required order upon next load.

When my listbox first gets populated from the database, I have the following code:

[code] // CLEAR THE LISTBOX
Listbox2.deleteAllRows

// RETURN THE RESULTS AS A RECORDSET
rs=db2.sqlSelect(sql )

// POPULATE THE COLOURS LISTBOX
while not rs.eof
Listbox2.AddRow
Listbox2.Cell(Listbox2.LastIndex, 1) = rs.Field(“Hex”).StringValue
Listbox2.Cell(Listbox2.LastIndex, 2) = rs.Field(“Rgb”).StringValue
Listbox2.RowTag(Listbox2.LastIndex) = rs.Field(“id”).IntegerValue
rs.moveNext
wend[/code]

Then, somehow, when I add my first entry into the database, I need to merge this code:

[code]For i As Integer = 0 To Listbox2.ListCount-1
db.SQLExecute("UPDATE Colours SET Position = " + str(i) + " WHERE id = " + Listbox2.RowTag(i).StringValue)

IF db.Error THEN
MsgBox "Something went wrong: "+ db.ErrorMessage
END IF

NEXT[/code]

With this code:

[code] // SET THE VARIABLE VALUES
FinalHexadecimal = ("&c" + Hex1Field.text + Hex2Field.text + Hex3Field.text)
FinalRgb = “RGB(” + RField.text +"," + GField.text + “,” + BField.text + “)”

// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO Colours (Hex, Rgb, Position) VALUES (?, ?, ?);”)

// BIND THE TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, FinalHexadecimal)
ps.Bind(1, FinalRgb)
ps.Bind(2, WHAT GOES HERE?)

// INSERT THE VALUES INTO THE DATABASE
ps.SQLExecute

// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Return

Else

//// UPDATE THE LISTBOX
//ColoursWindow.Timer3.mode = MainWindow.Timer2.ModeSingle

// CLOSE THE WINDOW
Self.Close

end if[/code]

Therefore, I have 2 problems:

What goes in the final bind statement?
How do I merge the final 2 code segments when I create my first database entry?

Hope that made sense

Tim_Hare · June 19, 2014, 5:50pm

Does Prepare really know enough about the other columns to predict an error on non-null? I would expect Prepare to succeed and Execute to fail.

Do you have a valid connection to the database? Try this code without using prepared statements. Does that succeed?

Richard_Summers · June 19, 2014, 5:55pm

weeping uncontrollably

I think I am going to have to give up on this - I am now totally lost.
My brain has now gone off in 23 different directions

I have a connection - yes.

Patrick_Delaney · June 19, 2014, 6:33pm

Maybe I’m missing something but are you overcomplicating it? (I probably am!)

Hopefully we’re round the ID column issue and an auto increment does the job there.

As for the Position column, If it were me I’d have an Insert and an Update method which both accept three parameters

FinalHexadecimal
FinalRgb
Position

Then your insert and update statements just bind these parameters.

To call it, I’m guessing, your looping over the listbox and calling the database Update/Insert method(?) If so, just call the relevant method passing in the FinalHexadecimal/FinalRgb constructed strings and the loop counter itself into the Position parameter. Hope that makes sense.

What am I missing?

Richard_Summers · June 19, 2014, 6:40pm

Patrick - I think the only thing you are missing, is the fact that I seem to have a chicken brain, and can’t picture how to do what you just said
I now have 3 different versions of my original code and I am more lost than 10 lost people, in the most lost person competition.