Can't pass a NULL date parameter via ADO to SQL stored procedure 
Author Message
 Can't pass a NULL date parameter via ADO to SQL stored procedure

In a tree tiered app, the ASP code call a COM+ Method passing a null
parameter: a date not filled in the form. In the middle tier the VB
method can't create a ADO parameter with a NULL date to call the SQL
Stored Procedure. The VB code looks like the following:

Public Function Insert(Optional ByVal BirthDate As String)
...
AdoCmd.Parameters.Append AdoCmd.CreateParameter("BirthDate",
adDBTimeStamp, adParamInput, , CDate(Data_Nascimento))
...

PS. I am sure that the BirthDate string is a valid date.

Thanks in advance for any help!



Tue, 24 Dec 2002 03:00:00 GMT  
 Can't pass a NULL date parameter via ADO to SQL stored procedure

I don't know if this will help, but I always create the parameter without a
value, then set the value or the parameter in another statement.  This way,
you could check to see if the value is a null and pass something else if
that's what's causing the problem.


Quote:
> In a tree tiered app, the ASP code call a COM+ Method passing a null
> parameter: a date not filled in the form. In the middle tier the VB
> method can't create a ADO parameter with a NULL date to call the SQL
> Stored Procedure. The VB code looks like the following:

> Public Function Insert(Optional ByVal BirthDate As String)
> ...
> AdoCmd.Parameters.Append AdoCmd.CreateParameter("BirthDate",
> adDBTimeStamp, adParamInput, , CDate(Data_Nascimento))
> ...

> PS. I am sure that the BirthDate string is a valid date.

> Thanks in advance for any help!



Wed, 25 Dec 2002 03:00:00 GMT  
 Can't pass a NULL date parameter via ADO to SQL stored procedure
Your value assignment is:

..., CDate(Data_Nascimento))

If Data_Nascimento contains a NULL of course this would fail.  CDate() does
not handle NULLs.  If you want to process NULLS, I would do the following to
ensure coercion to date data type or NULL:

Public Function Insert(Optional ByVal BirthDate As String)
   Dim dteData As Variant

   If IsDate(BirthDate) Then
      dteData = CDate(BirthDate)
   Else
      dteData = Null
   End If
...
AdoCmd.Parameters.Append AdoCmd.CreateParameter("BirthDate",
adDBTimeStamp, adParamInput, , dteData)


Quote:
> In a tree tiered app, the ASP code call a COM+ Method passing a null
> parameter: a date not filled in the form. In the middle tier the VB
> method can't create a ADO parameter with a NULL date to call the SQL
> Stored Procedure. The VB code looks like the following:

> Public Function Insert(Optional ByVal BirthDate As String)
> ...
> AdoCmd.Parameters.Append AdoCmd.CreateParameter("BirthDate",
> adDBTimeStamp, adParamInput, , CDate(Data_Nascimento))
> ...

> PS. I am sure that the BirthDate string is a valid date.

> Thanks in advance for any help!



Thu, 26 Dec 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Pass Null to stored procedure parameter using ADO?

2. Passing parameter to Sybase ASA Stored procedure via ADO

3. Passing parameter from VB 6 ADO Data Control to SQL Server Stored Procedure

4. How to passing parameters from and to a SQL Server stored procedure using ADO

5. report run with null parameter values via store procedure

6. pass nulls to stored procedure parameter?

7. How to pass null parameters to a stored procedure VB6/Oracle 8

8. How to pass null parameters to a stored procedure VB6/Oracle 8

9. How do you pass a null date to a stored procedure based report

10. Passing Stored Procedure parameters via VB6 into Crystal Report (ver 8)

11. ado recordsets from stored procedures, stored procedures have input parameters

12. Passing date parameter to stored procedure

 

 
Powered by phpBB® Forum Software