Passing null value in stored procedure call

[graabein]

Hello, I've read the FAQ but couldn't see the answer there. Not much luck googling either.

The question is simple. How do I pass a NULL value to a stored procedure that's expecting a datetime value?

cmd.ActiveConnection = myConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_StoredProc"

cmd.Parameters.Append cmd.CreateParameter("@Number", adInteger, , , nIntValue)
cmd.Parameters.Append cmd.CreateParameter("@Date", adDate, ...

graabein

 

[graabein]

OK found this thread. I'll try it out.

http://www.tek-tips.com/viewthread.cfm?qid=1008118&page=8

graabein

 

[graabein]

I have another problem now.

When I execute the stored procedure command I want the returned rows in a recordset and also I want to check some output parameters.

But the output parameters seem to be empty.

Here is my code:

...
cmd.Parameters.Append cmd.CreateParameter("@Output", adTinyInt, adParamOutput)
Set rs = cmd.Execute
Debug.Print "@Output = " & cmd.Parameters("@Output").Value

And my stored procedure have the following code snippets:

CREATE PROCEDURE dbo.usp_StoredProc(
  @Number int,
  @Date datetime,
  @Output tinyint output) 
AS 
set @Output = 0
select * from SomeTempTable
GO

I can't see what I am doing wrong?


graabein

 

[graabein]

Yet again I find the solution after I've posted the question... According to

http://bytes.com/forum/thread59221.html

I have to close the recordset before I get access to the output parameters.

So if I do like this:

Set rsTemp = cmd.Execute
Set rs = rsTemp 'I want to process rs later
rsTemp.Close
Set rsTemp = Nothing
Debug.Print "@Output = " & cmd.Parameters("@Output").Value

I actually get a value in the output parameter!


graabein