SQLite Prepared Statement Failing

Databases
1

Any idea why the code below is coming back with Unable to Prepare Statement

mDatabase.ActiveDatabase is an open database at the point the following is called.

[code]
Dim sSQL As String
Dim ps As SQLitePreparedStatement

sSQL = “INSERT INTO tblContacts(supplier,name,address,postcode,phone,mobile,email,website) VALUES (?, ?, ?, ?, ?, ?, ?, ?)”
ps = mDatabase.ActiveDatabase.Prepare(sSQL)
ps.SQLExecute(1,“aa”,“aa”,“aa”,“aa”,“aa”,“aa”,“aa”)//Just test data ignore what it is.
if mDatabase.ActiveDatabase.Error then
MsgBox ActiveDatabase.ErrorMessage
end if

[/code]
2

table name is correct ?
column names are correct ?
I can’t think of anything else

3

[quote=72130:@Norman Palardy]table name is correct ?
column names are correct ?
I can’t think of anything else[/quote]
Yes and yes. It’s got me stumped.

4

You didn’t BindType, could that be it?

5

I think that is just for SELECTS or at least it is according to the UserGuide-Framework.pdf page 129. I would expect to bind the types normally in PHP but the user guide suggests not??? Bamboozled

6

I’d expect BindType to be relevant for any type of statement without having read the docs on it.

Did you try it?

7

I’ll give it a go…

8

bind type might be it

9

I did some testing, and you may have a problem with your table.

If I create the database and just try to prepare the statement, i.e., before creating a table or fields, I get the message you got. After I create the table and fields, I get the message “Bind types were not specified, see BindType”.

10

My code:

  dim db as new SQLIteDatabase
  Dim sSQL As String
  Dim ps As SQLitePreparedStatement
  
  if not db.Connect() then
    AddToResult "Couldn't connect."
    return
  end if
  
  sSQL  = "CREATE TABLE tblContacts ( supplier INTEGER, name TEXT, address TEXT, postcode TEXT, phone TEXT, mobile TEXT, email TEXT, website TEXT )"
  db.SQLExecute sSQL
  if db.Error then
    AddToResult db.ErrorMessage
    return
  end if
  
  sSQL = "INSERT INTO tblContacts(supplier,name,address,postcode,phone,mobile,email,website) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
  ps = db.Prepare(sSQL)
  if db.Error then
    AddToResult db.ErrorMessage
    return
  end if
  
  ps.SQLExecute(1,"aa","aa","aa","aa","aa","aa","aa")//Just test data ignore what it is.
  if db.Error then
    AddToResult db.ErrorMessage // This is where we detect an error
    return
  end if
11

Finally, if I add the BindTypes, it all works.

12

Thanks kem. I tried adding the bind types and still no joy. Where did you add them, straight after the prepare?

13

Yes, but as I said, that error indicates a problem with the table or fields. Are you sure you spelled everything correctly?

14

i do have an id integer primary key field not null but that shouldn’t affect anything here should it?

15

A quick way to check that would be to try the statement without using a preparedStatement. e.g.,

INSERT INTO tblContacts(supplier,name,address,postcode,phone,mobile,email,website) VALUES (1,"aa","aa","aa","aa","aa","aa","aa");

Usually, you’ll get a meaningful error message that way.

16

Aha… I think I may have been leading myself and everyone else up the garden path. Thanks Peter, my latest error message is The Operation Cannot be completed because the database is closed. I’m not sure why it is closed so I will investigate this and get back to you… Whoops :wink:

17

Is it possible “name” is a reserved word?

18

Its even more possible that the connection to my database was closed. I’m really sorry folks, small oversight. Funny its working now.

19

It’s always something… :slight_smile:

20

Thanks all - As always very helpful. Much appreciated.