you are referring wrong textbox to pass datetime value, you should refer proper textboxes to read the values, simply try by changing the code like below
hi, if you have valid values in the textboxes then you should not get any error, the earlier replies code itself works fine. i hope the datetime textboxes does not have valid value
Dim Mycmd As SqlCommand = New SqlCommand
Mycmd.Connection = myConnection
Mycmd.CommandText = "OfflineBoothsUpdate"
Mycmd.CommandType = CommandType.StoredProcedure
Mycmd.Parameters.Add("@OffId", SqlDbType.Int).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffIdTxt.Text))
Mycmd.Parameters.Add("@OffBNum", SqlDbType.Int).Value = IIf(OffBNumTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffBNumTxt.Text))
Mycmd.Parameters.Add("@OffBDescription", SqlDbType.VarChar).Value = IIf(OffBDescriptionTxt.Text.Length = 0, Nothing, OffBDescriptionTxt.Text)
Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = IIf(OffBegDateLbl.Text.Length = 0, Nothing, OffBegDateLbl.Text)
Mycmd.Parameters.Add("@OffEndDate", SqlDbType.DateTime).Value = IIf(OffEndDateTxt.Text.Length = 0, Nothing, OffEndDateTxt.Text)
Mycmd.Parameters.Add("@Original_Offid", SqlDbType.Int).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffIdTxt.Text))
'still if you get error try below approach Dim dt As DateTime
If OffBegDateLbl.Text.Length = 0 Then
Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = Nothing
Else
DateTime.TryParse(OffBegDateLbl.Text, dt)
Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = dt
End If
When I have the dates in the OffBegDateTxt, it works fine without any errors. The error comes when I have no dates at all. There are times when I need the fields to be null.
Nighthawk071...
Member
121 Points
106 Posts
Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:09 PM|LINK
I need to be able to write a null value if the text box is left blank. Below is my Code:
Dim Mycmd As SqlCommand = New SqlCommand Mycmd.Connection = myConnection Mycmd.CommandText = "OfflineBoothsUpdate" Mycmd.CommandType = CommandType.StoredProcedure Mycmd.Parameters.Add("@OffId", SqlDbType.Int).Value = OffIdTxt.Text Mycmd.Parameters.Add("@OffBNum", SqlDbType.Int).Value = OffBNumTxt.Text Mycmd.Parameters.Add("@OffBDescription", SqlDbType.VarChar).Value = OffBDescriptionTxt.Text Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = OffBegDateLbl.Text Mycmd.Parameters.Add("@OffEndDate", SqlDbType.DateTime).Value = OffEndDateTxt.Text Mycmd.Parameters.Add("@Original_Offid", SqlDbType.Int).Value = OffIdTxt.TextI need to have the OffBegDateTxt have the option if it is null to write a null value to the DB. Any help is always greatly appreciated.
karthicks
All-Star
31378 Points
5422 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:15 PM|LINK
hi, you can do below
Mycmd.Parameters.Add("@OffId", SqlDbType.Int).Value = IIF(OffIdTxt.Text.Length==0,Nothing,OffIdTxt.Text)
like above you can do for all parameters
Karthick S
Nighthawk071...
Member
121 Points
106 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:20 PM|LINK
Karthicks,
When I put your suggestion in, I get the following error:
Compiler Error Message: BC30201: Expression expected.
karthicks
All-Star
31378 Points
5422 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:30 PM|LINK
sorry, it should be like below , use single equal to (=)only
<div sizcache="2" sizset="14">Mycmd.Parameters.Add("@OffId", SqlDbType.Int).Value = IIF(OffIdTxt.Text.Length=0,Nothing,OffIdTxt.Text)
mark both the reply as answered one
</div>
Karthick S
Nighthawk071...
Member
121 Points
106 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:34 PM|LINK
I get the following error with your suggestion:
Failed to convert parameter value from a String to a DateTime
This is the line of code:
Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, OffIdTxt.Text)karthicks
All-Star
31378 Points
5422 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:43 PM|LINK
you are referring wrong textbox to pass datetime value, you should refer proper textboxes to read the values, simply try by changing the code like below
Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = IIf(OffBegDateLbl.Text.Length = 0, Nothing, Convert.ToDateTime(OffBegDateLbl.Text))
else here is full code
Dim Mycmd As SqlCommand = New SqlCommand
Mycmd.Connection = myConnection
Mycmd.CommandText = "OfflineBoothsUpdate"
Mycmd.CommandType = CommandType.StoredProcedure
Mycmd.Parameters.Add("@OffId", SqlDbType.Int).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffIdTxt.Text))
Mycmd.Parameters.Add("@OffBNum", SqlDbType.Int).Value = IIf(OffBNumTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffBNumTxt.Text))
Mycmd.Parameters.Add("@OffBDescription", SqlDbType.VarChar).Value = IIf(OffBDescriptionTxt.Text.Length = 0, Nothing, OffBDescriptionTxt.Text)
Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = IIf(OffBegDateLbl.Text.Length = 0, Nothing, Convert.ToDateTime(OffBegDateLbl.Text))
Mycmd.Parameters.Add("@OffEndDate", SqlDbType.DateTime).Value =IIf(OffEndDateTxt.Text.Length = 0, Nothing, Convert.ToDateTime(OffEndDateTxt.Text))
Mycmd.Parameters.Add("@Original_Offid", SqlDbType.Int).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffIdTxt.Text))
Karthick S
Nighthawk071...
Member
121 Points
106 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 27, 2012 02:49 PM|LINK
Now I get the following error with the corrected code:
Nighthawk071...
Member
121 Points
106 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 30, 2012 12:23 PM|LINK
Does anyone have any other suggestions? I followed the above to the tee with what my value would.
karthicks
All-Star
31378 Points
5422 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 30, 2012 12:36 PM|LINK
hi, if you have valid values in the textboxes then you should not get any error, the earlier replies code itself works fine. i hope the datetime textboxes does not have valid value
Dim Mycmd As SqlCommand = New SqlCommand Mycmd.Connection = myConnection Mycmd.CommandText = "OfflineBoothsUpdate" Mycmd.CommandType = CommandType.StoredProcedure Mycmd.Parameters.Add("@OffId", SqlDbType.Int).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffIdTxt.Text)) Mycmd.Parameters.Add("@OffBNum", SqlDbType.Int).Value = IIf(OffBNumTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffBNumTxt.Text)) Mycmd.Parameters.Add("@OffBDescription", SqlDbType.VarChar).Value = IIf(OffBDescriptionTxt.Text.Length = 0, Nothing, OffBDescriptionTxt.Text) Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = IIf(OffBegDateLbl.Text.Length = 0, Nothing, OffBegDateLbl.Text) Mycmd.Parameters.Add("@OffEndDate", SqlDbType.DateTime).Value = IIf(OffEndDateTxt.Text.Length = 0, Nothing, OffEndDateTxt.Text) Mycmd.Parameters.Add("@Original_Offid", SqlDbType.Int).Value = IIf(OffIdTxt.Text.Length = 0, Nothing, Convert.ToInt32(OffIdTxt.Text)) 'still if you get error try below approach Dim dt As DateTime If OffBegDateLbl.Text.Length = 0 Then Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = Nothing Else DateTime.TryParse(OffBegDateLbl.Text, dt) Mycmd.Parameters.Add("@OffBegDate", SqlDbType.DateTime).Value = dt End Ifhi, mark all my replies as answered one
Karthick S
Nighthawk071...
Member
121 Points
106 Posts
Re: Writing a dbnull from a text box to ms sql
Apr 30, 2012 12:42 PM|LINK
When I have the dates in the OffBegDateTxt, it works fine without any errors. The error comes when I have no dates at all. There are times when I need the fields to be null.