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
精彩评论