Update.
We intend to create a resource center for all things Sybase. It is a
pretty good database, however, for people who have gotten used to MS type of customer support, Sybase support and
resources may not seem as thorough. Soon, you'll see here various stuff regarding making Sybase databases
work with ADO/OLEDB. For now, read this article.
OLEDB provider and ODBC driver for Sybase seem to
have this nasty bug, where it is impossible to pass NULL to a Date parameter in
a stored procedure. For instance, consider the following piece of code:
cmd.Parameters.Append cmd.CreateParameter("@mat_date", adDBTimeStamp, adParamInput, 0)
'This causes various errors when .Execute method is run
cmd.Parameters("@mat_date").Value = NULL
'This statement just basically passes 1, which the database translates to Jan 1, 1899 (???)
cmd.Parameters("@mat_date").Value = vbNull
The error messages vary from provider to provider (and
one even locks up the program), but the gist of it is that the provider is
expecting a data type with a certain Precision and
NumericScale. The error is somewhat misleading, since it is just convering up a bug.
So what should you do to fix this problem?
Nothing. Yep, absolutely nothing.
Not populating the date parameter will actually pass NULL to your stored
proc. In reality, what happens is by not setting the date parameter, the
value stored in the parameter will be Empty (yes, there is such
a value in VB). That, for
some strange reason, will force VB/ADO/ODBC to pass NULL to the stored
procedure.
Private Sub Populate_SP_DateParameter(dt as Date, prm as ADODB.Parameter)
If Not IsNull(dt) then
prm.Value = dt
End If
End Sub
Or alternatively you can simply set the stored proc parameter to Empty.
cmd.Parameters("@mat_date").Value = Empty