Mismatched Parameter Error

Databases
1

I am trying to update a record in a SQLite database using an SQLPreparedStatement. When I call the method to do the update, I get a error “Mismatched parameter and type at 14.” I have checked the binding stuff multiple times.

The only odd thing in this table is a SQLITE_BLOB that is being bound to a Image.Picture on the form. But even if I comment out both lines I still get the message. All other bindings are SQLITE_TEXT or SQLITE_INTEGER. I find NO reference to this error anywhere. Does anyone know anything about it? My hair is almost completely gray now, so any clues would be appreciated.

2

Can you post some code here so we can take a look?

3

Sure Wayne. I should have done that the first time.

// save values from form to database
Dim pID As Integer = mSelectedPersonID
Dim sql As String

if Not ValidateFields Then
// notify user that data is missing
MsgBox(“Both FirstName and LastName must be filled in”)
Else
// okay to continue…use a SQL UPDATE to update the database record
Dim personName As String = Trim(FirstNameField.Text) + " " + Trim(LastNameField.Text)

sql = "UPDATE people SET name_last = ?, name_first = ?, birthdate = ?, address1 = ?, " + _
"address2 = ?, city = ?, stateprov = ?, postcode = ?, country = ?, phone_main = ?, phone_cell = ?, " + _
"email = ?, type = ?, photo = ? WHERE ID = ?"

Dim statement As PreparedSQLStatement
statement = gDB.Prepare(sql)

statement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)    // name_last
statement.Bind(0, LastNameField.Text)
statement.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)    // name_first
statement.Bind(1, FirstNameField.Text)
statement.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)    // birthdate
statement.Bind(2, BirthdateField.Text)
statement.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)    // address1
statement.Bind(3, Address1Field.Text)
statement.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)    // address2
statement.Bind(4, Address2Field.text)
statement.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)    // city
statement.Bind(5, CityField.Text)
statement.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)    // stateprov
statement.Bind(6, StateProvField.Text)
statement.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)    // postcode
statement.Bind(7, PostcodeField.Text)
statement.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)    // country
statement.Bind(8, CountryPopup.Text)
statement.BindType(9, SQLitePreparedStatement.SQLITE_TEXT)    // phone_main
statement.Bind(9, PhoneField.Text)
statement.BindType(10, SQLitePreparedStatement.SQLITE_TEXT)    // phone_cell
statement.Bind(10, CellPhoneField.Text)
statement.BindType(11, SQLitePreparedStatement.SQLITE_TEXT)    // email
statement.Bind(11, EmailField.Text)
statement.BindType(12, SQLitePreparedStatement.SQLITE_TEXT)    // type
statement.Bind(12, TypePopup.Text)
statement.BindType(13, SQLitePreparedStatement.SQLITE_BLOB)    // photo
statement.Bind(13, PersonPhoto.Image)
statement.BindType(14, SQLitePreparedStatement.SQLITE_INTEGER)   // ID
'statement.Bind(14, mSelectedPersonID)

statement.SQLExecute(mSelectedPersonID)

If DBError Then
  MsgBox("Error saving the record for " + personName)
End If

end if

// tidy up
LoadPeople
ClearFields
self.EditMode = “Update”

4

you have 14 items in you BIND statements… but only 13 in your SQL statement

statement.BindType(14, SQLitePreparedStatement.SQLITE_INTEGER) // ID <---- PERHAPS YOU MEANT TO COMMENT THIS OUT TOO??
'statement.Bind(14, mSelectedPersonID)
5

Try uncommenting and changing statement.Bind(14, mSelectedPersonID) to statement.Bind(14, pID) (Nitpicking, but you declared it for some reason).

Also, try just statement.SQLExecute without any parameters.

6

You should also be using the specific PreparedStatement class

Dim statement As SQLitePreparedStatement