MySQLPreparedStatement.MYSQL_TYPE_NULL

I’m trying to track down a MySQL problem where I have a null date and I’m using stmt.BindType(i, MySQLPreparedStatement.MYSQL_TYPE_NULL).

When I call stmt.SQLExecute I get a DB error saying that I’ve only bound 2 values. I have verified that I have indeed bound all three and it seems that the only ‘odd’ thing is Null BindType.

Anyone seen this before?

Not an exact answer, but with MSSql via ODBC I’ve had to use nil to save as a NULL. I.e.:

    If dcPolicyEffectiveDate.Checked Then
      ps.Bind(5, dcPolicyEffectiveDate.DateValue.SQLDate) //policy_effective_date
    Else
      ps.Bind(5, Nil)
    End If

(This was with Bjorn’s dateControl.)

Good suggestion, but when I do that I get a compiler error: There is more than one item with this name and it’s not clear to which this refers.

Ug. Turns out I was missing a step. The scenario is that I’m inserting data into a table.

[code] dim sSQL as string = “INSERT INTO tblperson(FirstName,LastName,DateOfBirth) VALUES (?,?,?)”
dim ps as MySQLPreparedStatement = db.Prepare(sSQL)
if db.error then
dim sError as string = db.ErrorMessage
break
return
end

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(2, MySQLPreparedStatement.MYSQL_TYPE_DATE)[/code]

Then I start my binding process.

ps.Bind(0, "bob") ps.bind(1, "keeney")

And here’s where I was screwing up (at least on MySQL). If the date is nil (as it will be in this scenario), I have to go back and set the bind type to null and make sure I set the bind value to nil as well.

dim d as date //oh wait, we have a null value for our date, go back to bind a null value if d = nil then ps.BindType(2, MySQLPreparedStatement.MYSQL_TYPE_NULL) ps.bind(2, nil) //<-I was forgetting this else ps.bind(2, d) end

I never thought to change the bind type, but I guess mine works because it’s a string that ODBC submits anyway. Bookmarked this for future reference…