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