Problems setting database field to null

  • Thread starter Thread starter rosiew
  • Start date Start date
R

rosiew

I am having problems setting a field in my database to null. I tried
to follow some online advise on using a DataSet for setting the value
but I am gettting an error and not sure what I am doing wrong.
My table has only one row and no primary key. The "Frequency" field
is numeric and allows null input.

Dim daFreqs As OleDbDataAdapter
Dim ds As New DataSet()

daFreqs = New OleDbDataAdapter("SELECT * FROM AcDF_Frequency",
AcDF_Conn)
daFreqs.Fill(ds)

ds.Tables(0).Rows(0)("Frequency") = System.DBNull.Value
daFreqs.Update(ds)

I get the following error:
Update requires a valid UpdateCommand when passed DataRow collection
with modified rows.
 
You have implemented only SelectCommand of your dataadapter (by passing
select statement and connection string to constructor).
You have to implement Insert/Update/DeleteCommand, too.
There are several ways: "manually", use designer/wizard or use
CommandBuilder and have fun with black box.
 
You have implemented only SelectCommand of your dataadapter (by passing
select statement and connection string to constructor).
You have to implement Insert/Update/DeleteCommand, too.
There are several ways: "manually", use designer/wizard or use
CommandBuilder and have fun with black box.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & developmentwww.rthand.com
Blog:http://cs.rthand.com/blogs/blog_with_righthand/


I am having problems setting a field in my database to null. I tried
to follow some online advise on using a DataSet for setting the value
but I am gettting an error and not sure what I am doing wrong.
My table has only one row and no primary key. The "Frequency" field
is numeric and allows null input.
Click to expand...
Dim daFreqs As OleDbDataAdapter
Dim ds As New DataSet()
Click to expand...
daFreqs = New OleDbDataAdapter("SELECT * FROM AcDF_Frequency",
AcDF_Conn)
daFreqs.Fill(ds)
Click to expand...
ds.Tables(0).Rows(0)("Frequency") = System.DBNull.Value
daFreqs.Update(ds)
Click to expand...
I get the following error:
Update requires a valid UpdateCommand when passed DataRow collection
with modified rows.
Click to expand...
Click to expand...

I ended up using this:
cmd = New OleDbCommand("Update AcDF_Frequency SET [Frequency] =
NULL", AcDF_Conn, trans)
cmd.ExecuteNonQuery()

I had originally tried:
"Update AcDF_Frequency SET [Frequency] = " & system.DbNull.value
which did not work. But using "NULL" in the command made it work.

Thanks for your help.
 
Back
Top