Insert empty value in Datefield (MS Access and ADO.NET)

  • Thread starter Thread starter Arjan
  • Start date Start date
A

Arjan

Hello,

I would like to overwrite existing Datefield with an empty value. (with
update command)
In Access it could be done with: MyDateField.Value= vbEmpty, for SQL server
it goes with: MyDateField.Value=DBNULL.Value with ADO.NET in VS2003.
How to achieve this for an access table? DBNull.Value gives me error
message. 'Can't convert DBNull.Value to Date Field..'

Thanks in advance.

Arjan
 
Hi,

NULL is not an empty string. It means no value at all. Empty string is a
value, that just does not have any characters. What you need to do is just
to assign "" (blank string) to your value

MyDateField.Value=""
 
Hi,

this trick doesn't work. Because when the user clears the textbox with date
in it, the textbox has already the value "" when I check this with a
breakpoint.
The error message says: "" can't be converted to datetime format..

Add(New OleDb.OleDbParameter("@iStartDate", _

OleDb.OleDbType.Date)).Value = txtIstart.Text '-> This value contains
already "" when clearing the textbox.

Hope you can still help.

Thanks for helping me.

Grtz,

Arjan
 
OK, in a case of numeric or date types you cannot assign blank string,
because date or numeric field cannot hold string datatype. In this case you
have to assign NULL value, but first check if textbox is not a date

If not IsDate(MyeTextBox.Text) then
.Value=DbNull.Value
else
.Value=Convert.Todate(MyeTextBox.Text)
endif

--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Val, thanks for replying.
Still got the problem because DBNull.value doesn't work with Access and me I
think. You say it has to work? In SQL server it does, but Access doesn't!
If I'm wrong, please correct me.

Grtz,

Arjan.
 
Hi,

It work with Access as well, but if your field declared in a Access table as
required (cannot contain Null), then you have two choices - changed
definition of this field in a table to allow Null values or to assign value
in a code

--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Val,

you're right. It works when I place the DBNull.Value directly in the
OleDb.OleDbParameterstatement.
Like in:

cmdSQL.Parameters("@InitActionStart").Value = DBNull.Value

But when I want to place the DBNull in a variable I get the Error message.

I've got:

Dim initStart as DateTime

If me.txtInitStart.Text="" Then
initStart=DBNull.Value 'Error: DBNull.Value can't be converted to Date..
Else
initStart=me.txtInitStart.Text
End if

So this is not possible on this way. Could this also be solved?

Thanks in advance.
Grtz,
Arjan
 
Back
Top