I’m building a prepared statement dynamically based on the controls on a form. Here’s the essential part of the code:
[code]dim values() as string // array of data values
dim fields() as string // array of database field names
dim bindtypes() As integer // array of BIND types for the prepared statement
dim statementParts() as string // pieces used to build the full query
dim query as string // the full query
dim ps as MySQLPreparedStatement
. . . [fill the above arrays]
// now build the prepared statement:
for i as integer = 0 to values.Ubound
statementParts.append fields(i) + “=?”
next
query = "UPDATE singers SET " + Join(statementParts, ", “) + " WHERE singer_id=” + str(currentSingerId)
ps = db.prepare(query)
system.debuglog "QUERY: " + query
// now do the binding:
for i as integer = 0 to fields.Ubound
system.debuglog “Binding field #” + str(i) +": ‘" + fields(i) + "’ to ‘" + values(i) + "’ bindtype=" + str(bindtypes(i))
ps.BindType(i, bindtypes(i))
ps.Bind(i, values(i))
next
ps.SQLExecute query[/code]
This code looks OK to me, but obviously it isn’t, because I’m getting this error message right after the ps.SQLExecute line:
1 parameters are being bound, but 19 types were specified
Here’s the output from the debug log; it sure looks like 19 parameters are being bound.
QUERY: UPDATE singers SET firstname=?, lastname=?, nickname=?, email_primary=?, email_other=?, address1=?, address2=?, zip=?, city=?, phone_cell=?, phone_other=?, height_ft=?, height_in=?, occupation=?, company=?, state=?, other_groups=?, medical_conditions=?, special_accom=? WHERE singer_id=1
Binding field #0: 'firstname' to 'Ford' bindtype=254
Binding field #1: 'lastname' to 'Prefect' bindtype=254
Binding field #2: 'nickname' to 'Chevy' bindtype=254
Binding field #3: 'email_primary' to 'a@b.com' bindtype=254
Binding field #4: 'email_other' to 'b@c.com' bindtype=254
Binding field #5: 'address1' to '4242 Main Street' bindtype=254
Binding field #6: 'address2' to 'Suite 42' bindtype=254
Binding field #7: 'zip' to '12345' bindtype=254
Binding field #8: 'city' to 'Cornwall-upon-Foobar' bindtype=254
Binding field #9: 'phone_cell' to '888-555-1234' bindtype=254
Binding field #10: 'phone_other' to '888-555-4321' bindtype=254
Binding field #11: 'height_ft' to '6' bindtype=254
Binding field #12: 'height_in' to '4' bindtype=254
Binding field #13: 'occupation' to 'Alien' bindtype=254
Binding field #14: 'company' to 'Hitchhiker's Guide' bindtype=254
Binding field #15: 'state' to 'VA' bindtype=254
Binding field #16: 'other_groups' to '' bindtype=254
Binding field #17: 'medical_conditions' to '' bindtype=254
Binding field #18: 'special_accom' to '' bindtype=254
Any help is appreciated, as I’m sure I’m doing something dumb. Thanks!