DBnull, Date and DataAdapter

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
Wed Jan 05 10:53:59 CST 2005

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

"blue_nirvana" <bluenirvana@discussions.microsoft.com> wrote in message
news:E1E2A7CB-9114-4DF8-984A-F5DC2654FA46@microsoft.com...
> 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

bluenirvana
Wed Jan 05 11:53:04 CST 2005

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" wrote:

> Are you talking about call the Update method on a dataset? What do you mean
> by 'using a SqlDataAdapater'?
>
> "blue_nirvana" <bluenirvana@discussions.microsoft.com> wrote in message
> news:E1E2A7CB-9114-4DF8-984A-F5DC2654FA46@microsoft.com...
> > 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
Wed Jan 05 12:03:56 CST 2005

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.).


"blue_nirvana" <bluenirvana@discussions.microsoft.com> wrote in message
news:9F0922B5-24A2-4143-9667-09A5E7760A03@microsoft.com...
> 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" wrote:
>
> > Are you talking about call the Update method on a dataset? What do you
mean
> > by 'using a SqlDataAdapater'?
> >
> > "blue_nirvana" <bluenirvana@discussions.microsoft.com> wrote in message
> > news:E1E2A7CB-9114-4DF8-984A-F5DC2654FA46@microsoft.com...
> > > 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
> >
> >
> >

davisonm
Wed Jan 05 14:31:12 CST 2005

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


"blue_nirvana" wrote:

> 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

Cor
Wed Jan 05 14:43:54 CST 2005

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
Wed Jan 05 14:51:24 CST 2005

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

bluenirvana
Wed Jan 05 14:53:02 CST 2005

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

"davisonm" wrote:

> 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
>
>
> "blue_nirvana" wrote:
>
> > 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

Eric
Wed Jan 05 14:59:10 CST 2005


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

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


HTH,
-eric




blue_nirvana wrote:
> 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
Wed Jan 05 15:34:21 CST 2005

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.

"Cor Ligthert" <notmyfirstname@planet.nl> wrote in message
news:%23nORGc28EHA.3828@TK2MSFTNGP09.phx.gbl...
> 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
>
>