MySQL: How to assign Null Value to Field when Inserting a record?

I am using MySQL. How do I assign a Null value to a CHAR Field when inserting a record?

Dim Row As New DatabaseRecord Row.Column("ID") = "ABC" Row.Column("Contents") = Nil mDB.InsertRecord("ABC", Row)

I had tried

Row.Column("Contents") = Nil

but getting compilation error.

Any help is greatly appreciated.

Just don’t assign anything to that field. If the field is not included in the insert, it will be set to NULL (unless you set it to not null with a default value in the database).

Hello Tim,

Thanks for the workaround.

Hello everybody,

First of all I would like to say the best (and save) way to insert or update a MySQL column with NULL (or any) values is using the MySQLPreparedStatement, as follow:

This example shows how to insert “ABCD” and 20 values in a table:

  1. Dim ps As PreparedSQLStatement
  2. Dim mDB As New MySQLCommunityServer
  3. db.Host = “”
  4. db.Username = “admin”
  5. db.Password = “admin”
  6. ’ Write here your Database connection code.
  7. ps = mDB.Prepare(“INSERT INTO TABLENAME (Column1, Column2) VALUES (?, ?)”)
  8. ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  9. ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)
  10. ps.Bind(0, “ABCD”)
  11. ps.Bind(1, 20)
  12. ps.SQLExecute()
  13. if not mDB.Error then
  14. mDB.SQLExecute(“COMMIT”)
  15. MsgBox(“Insert successfully.”)
  16. return
  17. else
  18. MsgBox("Error " + mDB.ErrorMessage)
  19. return
  20. end if

And now, if you want to insert expressly NULL value, for example, in Column2, instead of value 20, replace line 11
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)
with this other:
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_NULL)
Then simply skip line 13,( not assign any value to column) and all will work ok.

This method insert a NULL value in Column2, instead of any integer value. (of course, column definition has to allow NULL values)
Use the same for update sentences.