Vb.net 2008 - OleDbParameter Date problem

[Rob W]

Greetings,

I'm setting my OleDbParameter from a datepicker (See below) to update an
access .accdb database :-
If dtpickerLastVisited.Checked = True Then

sqlUpdateMember += "cDateLastVisited =#?#,"

paramDateLastVisited = New OleDbParameter("?", OleDbType.DBDate)

paramDateLastVisited.Value = Me.dtpickerLastVisited.Value.ToShortDateString

MsgBox("Print the param value " & paramDateLastVisited.Value)

End If

Later on the parameter is added with

cmd.Parameters.Add(paramDateLastVisited)



When executing the SQL the debug message informs me the param has a value in
the format of 12/03/2008 for example, however my SQL has issues.

If I set the SQL as cDateLastVisited =#?#, it errors with a syntax error
"Syntax error in date in query expression '#?!

Or

If I set the SQL as cDateLastVisited =[?], it complains with a syntax error
"Param ?_1 has no default value" which I dont understand as when I print out
the paramDateLastVisited.Value I can see a date.

My other param substitutes works fine, only have this issue with a
datepicker/date value.

Can anyone advise what is the proper way to handle date values and
parameters in an SQL update for an access database (.accdb) please?



Cheers

Rob

 

[Rob W]

The access field cDateLastVisited is date/time with a shortDate format
applied.

 

[Armin Zingler]

Can anyone advise what is the proper way to handle date values and
parameters in an SQL update for an access database (.accdb) please?

Use
sqlUpdateMember &= "cDateLastVisited = ?,"
and
paramDateLastVisited.Value = Me.dtpickerLastVisited.Value

No need to convert to a String. That's one advantage of using
OleDbParameters instead of concatenating SQLs.


Armin

 

[Rob W]

Thanks, functioning fine now

Use
sqlUpdateMember &= "cDateLastVisited = ?,"
and
paramDateLastVisited.Value = Me.dtpickerLastVisited.Value

No need to convert to a String. That's one advantage of using
OleDbParameters instead of concatenating SQLs.


Armin