DBnull, Date and DataAdapter

[Guest]

Is there a way to assign a datetime column in SQL Server to Null using a
SQLDataAdapter? I know you can do it by calling a stored procedure and
setting a parameter equal to DBnull.Value. But it does not make since to me
that you can not set a datetime column to null from a SQLDataAdapter.

Thanks

 

[Marina]

Are you talking about call the Update method on a dataset? What do you mean
by 'using a SqlDataAdapater'?

 

[Guest]

I have a strongly-type dataset with a datatable from SQL Server. This
datatable has a column called CloseDate that allows nulls. But when trying
to assign DbNull.Value to the column in code, I get "Value of type
'System.DbNull' cannot be converted to 'Date'. For example, dr.CloseDate =
DbNull.Value

I read somewhere that you cannot update a datetime column in SQL Server
using a DataAdapter. Is that true? If not, how do you assign a null value
to a datetime column in a SQL Server table using a DataAdapter?

Thanks

 

[Marina]

NO, that is not true. Assigning DBNull.Value to a date column should work
just fine.

I think the issue is that the typed DataSet has a strongly typed column.
Whereas in a regular one, everything is of type Object, so assignment to
DBNull.Value can be compiled. I am guessing this is a compile time issue?
Your posts are very vague and do not provide all the details of your
problem. Remember, people reading this newsgroup have no idea of your
situation,you have to be very specific.

I would look for a method in the typed datatable or datarow that can set the
column to NULL. In this case, you would never be able to set a column of any
type to DBNull.Value, since they would all have a specific type (String,
Integer, etc.).

 

[Guest]

One sleazy way to do it is just sneak around the datatype conversion code
(the source of the exception) by indexing the row instead of referring to the
column property:

dr("CloseDate") = DbNull.Value

I have difficulties with this as well, and if there is a better way to
handle it, I don't know it.

Alternately, if your problem is validating and/or translating user entry
into a bound textbox on a windows form, then you can declare at the top of
your forms class:

Private WithEvents MyBinding As Binding

then copy the datetime textbox binding to it in, say, form Load:

MyBinding = MyTextBox.DataBindings.Item("Text")

and finally, handle the Format and Parse events for this binding:

Private Sub MyBinding_Format(ByVal sender As Object, ByVal e As
System.Windows.Forms.ConvertEventArgs) Handles MyBinding.Format
If (e.DesiredType Is GetType(System.String)) Then
If (e.Value Is System.DBNull.Value) Then
e.Value = ""
Else
e.Value = CType(e.Value, DateTime).ToString("d")
End If
End If
End Sub

Private Sub MyBinding_Parse(ByVal sender As Object, ByVal e As
System.Windows.Forms.ConvertEventArgs) Handles MyBinding.Parse
If (e.DesiredType Is GetType(DateTime)) Then
If (e.Value = "") Then
e.Value = System.DBNull.Value
Else
Try
e.Value = DateTime.Parse(e.Value)
Catch ex As FormatException
MessageBox.Show("The value '" & e.Value & "' is not a
recognizable date.")
End Try
End If
End If
End Sub

And if it's ASP code you're after, then you need someone else...

Good luck,
Mark

 

[Cor Ligthert]

Marina,

I am not sure anymore, however I thought that the by the designer prevented
strongly typed dataset prevents the use of DBNull.value. When you make
yourself a strongly typed dataset of course not.

I thought that I advices some weeks ago somebody to inherit that by the
designer generated dataset and than set in that the DBNull value (that to
prevent when you use it and you want to redesigning and does it directly in
the dataset that you have to set it every time again)

However I am not sure anymore so more to give you and the OP a hint.

Cor

 

[Cor Ligthert]

prevented = created of course

I was already thinking about something else while typing.

And advices = adviced just because the character s is beside the d

Cor

 

[Guest]

Actually I found what I was needing, but you are right, dr("CloseDate") works
fine. But in sticking with the strongly type theme, the datarow exposes a
sub called dr.SetCloseDateNull() that does the work for you. I guess I did
not look hard enough for it.

Thanks

 

[Eric Barr]

In typed datasets there is a "set__null" funciton for each nullable
field. Something like:

MyDataset.TheObject.Item(0).Set<YourFieldName>Null


HTH,
-eric

 

[Marina]

To be honest, I never use strongly typed data sets. They have always been
more trouble then they are worth to me - and here is another reason why.

I was just pointing out that the issue seemed to be a compile time issue,
not an execution time issue (since the post did not make that clear). And
was suggesting there may be a method or something to do it, but I had no
idea, since I don't use them.