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

Databases
1

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.

2

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).

3

Hello Tim,

Thanks for the workaround.

4

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 = “127.0.0.0”
  4. db.Username = “admin”
  5. db.Password = “admin”
  7. ’ Write here your Database connection code.
  9. ps = mDB.Prepare(“INSERT INTO TABLENAME (Column1, Column2) VALUES (?, ?)”)
  10. ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  11. ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)
  12. ps.Bind(0, “ABCD”)
  13. ps.Bind(1, 20)
  14. ps.SQLExecute()
  15. if not mDB.Error then
  16. mDB.SQLExecute(“COMMIT”)
  17. MsgBox(“Insert successfully.”)
  18. return
  19. else
  20. MsgBox("Error " + mDB.ErrorMessage)
  21. return
  22. 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.

Regards.
Miguel.