Null update on date field does not work

[Anson Chuang]

Dear All,

I have been trying to update a table with a date field. It refuses update to
a null value in the database...

I get the table like this

=====
strSQL = "SELECT 'This Level - " & SQLSafe(Me.Text.Replace(" > Resource
Forecast", "")) & "' as Level, SubTaskID, Name, Description, Deadline,
Complete,OrderNum " & _
"FROM AuditProjectSubTasks " & _
"WHERE (ParentTaskID = " & _ParentTaskID & ") order by OrderNum "
daFiller = New SqlClient.SqlDataAdapter(strSQL, cnnSqlDBv1Shared)

daFiller.Fill(dsMain, "Tasks")

daFiller.Dispose()

tableTasks = dsMain.Tables("Tasks")

=======

Then I update the value like this

========

tableTasks.Rows(2).Item("Deadline") = system.DBNull.Value

========

When I look at the value

tableTasks.Rows(2).Item("Deadline")

it says its dbnull

however, when I look at the database (SQL 2000) it shows the value to be
"01/01/1900". I also checked the SQL Profiler, it shows this update
statement when you do the update.

Update AuditProjectSubTasks set Name='v2 new task 2', Description='',
Deadline='', Complete='' WHERE (AuditProjectSubTaskID = 41)
What am I doing wrong? why does the dataadaptor use '' rather than null?

Anson

 

[William Ryan eMVP]

You aren't calling Update on the dataadapter, all you are doing is filling
the Datatable. Also, you may want to use OleDbParameters instead of Dynamic
SQL ...

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups