Syntax error?

Richard_Summers · June 19, 2014, 6:46pm

Maybe if we start at the very beginning?
When my app starts, I create the database if it does not exist, and then connect to it.
All ok so far.

In the ColoursWindow Open event , I have the following code:

// POPULATE THE COLOURS LISTBOX PopulateColours(Listbox2,db,"SELECT id, Hex, Rgb, Position from Colours ORDER BY Position ASC")

My PopulateColours method contains 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, when I have entered the colour values into my textfield, I press the save button which calls a method called PrepareAndInsertColours.

It contains the following 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) 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

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

Else

// UPDATE THE LISTBOX
PopulateColours(Listbox2,db,“select id, Hex, Rgb, Position from Colours order by Position Asc”)

// CLOSE THE WINDOW
Self.Close

end if[/code]

Does this look right, so far?
I believe the position column may give me problems here??
This is where I get lost

Wayne_Golding · June 19, 2014, 7:40pm

You are ordering by position, but never setting the value when you insert your record into the database. This also why the prepared statement is failing. Remember too that any error in the prepared statement will show when you execute the statement, not when it’s prepared and will always be “Unable to Prepare”.

I’m guessing you should be using listbox.listcount as the position as that’s the way you’re using the position column. So update your code to:

[code] // 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, Listbox2.ListCount)

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

HTH

Wayne

Richard_Summers · June 19, 2014, 7:48pm

Wayne - that made no difference.
Still get the same error

Does anyone have TeamViewer?
They could then look at my code directly.

Peter_Fargo · June 19, 2014, 7:53pm

Richard is this a small single user database? If it is then you can add/edit/drag records to your hearts content and then when finished replace your data by deleting all the existing records and inserting from the listbox in a loop.

[code]Dim bInsertError As boolean = false

db.sqlexecute “BEGIN TRANSACTION”

db.delete from Colours

For i As integer = 0 to listbox2.listcount - 1

prepared statement as Wayne shows with the last bind changed to:

ps.Bind(2, i)

ps.sqlexecute

if db.error then

bInsertError = true

msgbox db.errormessage

exit

end if

db.rollback

else

db.commit

end if[/code]

If it is multiuser then you have a lot more to worry about.

Wayne_Golding · June 19, 2014, 7:54pm

You have updated the database for autoincrement? And deleted the database file or dropped the table first? How about another screenshot of your database.

Peter_Fargo · June 19, 2014, 7:57pm

I’ve got TeamViewer installed. Haven’t used it in … forever but if it will help I’ll give it a go.

Peter_Truskier · June 19, 2014, 8:37pm

If the table does, indeed, have a NOT NULL constraint on Position, then you must supply a value for it when you do the INSERT. Given that, I’m surprised that Wayne’s last suggested code (using Listbox2.ListCount, though I’m not sure ListCount is the right value) didn’t work.

If your TeamViewer exercise doesn’t work out, I’d suggest additional information as Wayne suggested…

Richard_Summers · June 19, 2014, 9:19pm

Thanks to the above and beyond help from Peter Fargo, (and also those of you in this thread) - the problem is now solved.
I would like to thank you ALL for spending the time to help

Wayne_Golding · June 19, 2014, 9:24pm

And the solution was?

Peter_Fargo · June 19, 2014, 10:21pm

A couple of issues. One was referencing “db” when this database was “db2”. Another was a typo. Richard has a nice little app started which will be great for new users once he feels comfortable that everything is working

One of the difficult things with trying to help troubleshoot an OO app is that they often jump from button code to method to method and getting all the relevant bits into a post is not as easy as stepping through the debugger. I tried to show how I approach debugging and Richard was a sponge!

Enthusiasm and appreciation go a long way in getting people to invest their time to help.

Nice to give back a bit of what I’ve received here over the years.

Richard_Summers · June 19, 2014, 10:34pm

Sorry Wayne - I was getting away from the computer for a while in order to clear my head.

As Peter explained, nothing major, just the typical missing digit which got lost somewhere in a couple of hundred lines of code

Tim_Hare · June 19, 2014, 10:36pm

That’s what I suspected. No database connection. That’s one of the few things that could account for “cannot prepare”.

Richard_Summers · June 19, 2014, 10:38pm

Error 1: Unable to Prepare = No Connection (normally) - I will put that in my Developing 101 notes!

Tim_Hare · June 19, 2014, 10:41pm

That, and an actual error in the statement. But there was no error in the statement, so…

Richard_Summers · June 19, 2014, 10:42pm

I seem to have learnt so much in the last 2 weeks, that my head feels like it is going to burst
I should have stayed in my regular job.

Peter_Fargo · June 19, 2014, 11:47pm

At some point you need to focus on applying what you know. Repetition will drill it in. I don’t think a week goes by where I don’t both struggle through something I know I used to know and learn something new that seems so obvious I wonder how I missed it all these years.

Richard_Summers · June 20, 2014, 12:17am

Peter, if I put the code below into the ColourWindow.close event, would this then ensure that the newly drag-reordered rows are correctly saved?

[code] // CREATE AN ERROR FLAG VARIABLE
Dim InsertError As boolean = false

// BEGIN DATABASE TRANSACTION
db2.sqlexecute “BEGIN TRANSACTION”

// EMPTY THE DATABASE COMPLETELY
db2.delete from Colours

// LOOP THROUGH THE LISTBOX
For i As integer = 0 to listbox2.listcount - 1

// 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, i)

// INSERT THE NEW VALUES INTO THE DATABASE
ps.SQLExecute

// CHECK FOR DATABASE ERROR
if db2.error then
  InsertError = true
  
  // DISPLAY ERROR MSGBOX
  msgbox ("db2.errormessage")
  exit
end if

// REVERSE THE CHANGES
db.rollback

else
// COMMIT THE CHANGES
db.commit

End if[/code]

Wayne_Golding · June 20, 2014, 12:46am

Any error in the SQL statement will result in Unable to Prepare.

Running this

[code] Dim db As New SQLiteDatabase

Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM customers;”)

Break[/code]

At the break point there is no database error, but if I were to try & execute the statement then I’d get the “Unable to Prepare” error.

It actually makes tracking errors very difficult. The only way to get a useful error is to use Inject the SQL statement & look at the error returned from that.

Peter_Fargo · June 20, 2014, 9:37am

It appears OK. I would recommend putting a test for a db2.error after the delete from. You want to rollback the transaction if any single statement results in an error. I also would not put it in the close event. If it fails you will lose your work. Put it in the CancelClose event and return the InsertError Flag. If the error flag is true the window will remain open.

[code]// EMPTY THE DATABASE COMPLETELY

db2.delete from Colours

if db2.error then

msgbox “Failed to delete colour records” + endofline + db2.errorMessage
InsertError = true

else

// LOOP THROUGH THE LISTBOX
For i As integer = 0 to listbox2.listcount - 1

...

Next
end if

…
[/code]

Richard_Summers · June 20, 2014, 11:31am

Hi Peter,
My ColoursWindow CancelClose event code is shown below, but there’s a slight problem:

It deletes all database entries ok, but DOES NOT repopulate the database with new entries afterwards?
I have included a screenshot of my database (at the end of this post), and it looks like some kind of additional table has been created?

[code] // CREATE AN ERROR FLAG VARIABLE
Dim InsertError As boolean = false

// BEGIN DATABASE TRANSACTION
db2.sqlExecute “BEGIN TRANSACTION”

// EMPTY THE DATABASE COMPLETELY
db2.sqlExecute “DELETE FROM Colours”

// CHECK FOR ERROR
if db2.error then

// DISPLAY AN ERROR MSGBOX
msgbox "Failed to delete values!" + endofline + db2.errorMessage
InsertError = true

else
// LOOP THROUGH THE LISTBOX
For i As integer = 0 to Listbox2.listcount - 1

  // 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, i)
  
  // INSERT THE NEW VALUES INTO THE DATABASE
  ps.SQLExecute
  
  // CHECK FOR DATABASE ERROR
  if db2.error then
    InsertError = true
    
    // DISPLAY ERROR MSGBOX
    msgbox ("db2.errormessage")
    exit
  end if
  
Next

End if

if InsertError then

// REVERSE THE CHANGES
db2.rollback  // SHOULDNT THIS LINE BE............. db2.SQLExecute "ROLLBACK"

else
// COMMIT THE CHANGES
db2.commit // SHOULDNT THIS LINE BE… db2.SQLExecute “COMMIT”
End if[/code]

DATABASE CONTENTS SCREENSHOT: