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

[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

 

[Val Mazur \(MVP\)]

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=""

 

[Arjan]

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

 

[Val Mazur \(MVP\)]

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

 

[Arjan]

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.

 

[Val Mazur \(MVP\)]

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

 

[Arjan]

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

 

[Val Mazur \(MVP\)]

Hi,

No, you cannot assign Null value to the variable. Only parameters support it

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

 

[Arjan]

Val, thank you very much for your help.

Regards,

Arjan