How To assign null values in MySql using VB.NET

I am not sure why VB makes everything such a pain. I have a fields which stores the date and time in the format required to store in MySQL database

 Dim AppDate As String = String.Empty
      If Not String.IsNullOrEm开发者_开发百科pty(Me.AppDate.Text.Trim) Then
         AppDate = Format(CDate(Me.AppDate.Text), "yyyy-MM-dd h:mm:ss")
      Else
        //Need to assign a null value to AppDate 
      End If

Now I need to assign the AppDate to NUll like DBNull, but I am not able to do it directly. If I change AppDate to Date then I am not getting the required format.

Any help is appreciated .

Thanks in Advance.

AppDate = Nothing should work, you could also use DateTime.MinValue and update your business logic to treat it appropriately.

AppDate = Nothing

the date and time in the format required to store in MySQL database

If you're building a datetime string to save to a database, you're doing it all wrong.

Using the MySql Connector/Net, you should be building your query like this:

Dim sql As String = "UPDATE `MyTable` SET `MyField` = @MyField WHERE `ID` = @MyID"
Using cn As New MySqlconnection("..your connection string here.."), _
      cmd As New SqlCommand(sql, cn)

    cmd.Parameters.Add("@MyField", MySqlDbType.DateTime).Value = MyDateTimeVar ''# NO FORMATTING NEEDED!
    cmd.Parameters.Add("@MyID", MySqlDbType.Int).Value = MyIDIntegerVar

    cn.Open()
    cmd.ExecuteNonQuery()
End Using

AppDate = DbNull.Value 'does this work?

The string value returned by the DbNull.ToString() method is the empty string (""), which may imply that the string representation of DbNull is the empty string.

Mysql allows NULL values for datetime only if you set ConvertZeroDateTime=True option in connectionstring. This value is set to false by default.

MySql Documentation Describes:

ConvertZeroDateTime (Default: False):

True to have MySqlDataReader.GetValue() and MySqlDataReader.GetDateTime() return DateTime.MinValue for date or datetime columns that have disallowed values.

AllowZeroDateTime (Default: False):

If set to True, MySqlDataReader.GetValue() returns a MySqlDateTime object for date or datetime columns that have disallowed values, such as zero datetime values, and a System.DateTime object for valid values. If set to False (the default setting) it causes a System.DateTime object to be returned for all valid values and an exception to be thrown for disallowed values, such as zero datetime values.

this actully the only way it worked for me , just use System.Data.SqlTypes.Sql Data Type .Null for example when an sql field is type Char(3) , then use System.Data.SqlTypes.SqlChars.Null , string.empty or nothing didn't work for me

    Dim SQLCom = New SqlCommand
    Dim SQLCon As New SqlClient.SqlConnection(_ConnString)
    Dim _Param5 As New SqlParameter("@FieldName", SqlDbType.Char, 3)
    With SQLCom
        .Connection = SQLCon
        .CommandText = "StoredProcedure_name"
        .CommandType = CommandType.StoredProcedure
        .Parameters.Add(_Parameter5)
    End With
  _Parameter5.Value = System.Data.SqlTypes.SqlChars.Null