Problem passing an adDBTimeStamp value

[Shanti]

I entered 04/25/2005 into the DisableDate field in my form and hit submit, it calls this code:

	dim disDate 
	
	if trim(Request.Form("DisableDate")) = "" then 
		disDate = NULL
	else
		disDate = Request.Form("DisableDate") ' will be in mm/dd/yyyy format
	end if
	
	Response.Write("DisableDate in ChangePassword: " & disDate)
	RetCode = SetDisableDate(conn, disDate, PerUsrKey)

which calls this function:

	Function SetDisableDate(conn, disDate, PerUsrKey)
		Response.Write("<br>DisableDate in TIHFunctions: " & disDate)
		Dim cmd
		Set cmd = server.createobject("ADODB.Command")
	
		cmd.CommandText = "sp_SetDisableDate"
		cmd.ActiveConnection = conn
		cmd.CommandType = adCmdStoredProc
		cmd.Parameters.Append cmd.CreateParameter("disDate", adDBTimeStamp, adParamInput)
		cmd.Parameters.Append cmd.CreateParameter("PerUsrKey", adInteger, adParamInput)
	
		cmd.Execute
	
		Set cmd = Nothing
	End Function

calling this stored proc

	PROCEDURE [sp_SetDisableDate] @disDate DATETIME, @PerUsrKey INT
	AS 
	BEGIN
	UPDATE Users
	SET UsrDisableDate = @disDate
	WHERE UsrKey = @PerUsrKey
	END

It returns an error saying "Procedure 'sp_SetDisableDate' expects parameter '@disDate', which was not supplied.", but both of the response.writes for disDate print 04/25/2005. Is there something I'm not formatting right? Is it something to do with adDBTimeStamp? I've tried asDate and adDBDate, but they both return errors that the optional feature is not implemented.

Any help is most appreciated!!

 

[pbaldy]

I think your use of CreateParameter is wrong. Try this:

cmd.Parameters.Append cmd.CreateParameter( "@disDate", adDBTimeStamp, adParamInput, , disDate)

 

[Shanti]

I'm sorry, that generates the same error. I've left off the length and default value arguments in CreateParameter since they were listed as optional and I wasn't sure what to list as the length. I have several other functions that send arguments to stored procedures with this exact same syntax that work perfectly, but none of them use the adDBTimeStamp as an adParamInput. It's got to be something with that, I just don't know what.

I have tested my stored procedure with SQL Query Analyzer and it works perfectly from there.

 

[pbaldy]

It's not that. Here's part of a procedure that I have in a heavily used db, calling a SQL Server SP:

With cmd
    'various other lines here
    .Parameters.Append .CreateParameter( _
      "@StartDate", adDBTimeStamp, adParamInput, , txtFmDate)

Works flawlessly for me. @StartDate is the name of the parameter in the SP, txtFmDate is the control with the desired date.

 

[pbaldy]

And I'm surprised yours works that way, since it seems to go against the structure in Help, which is:

command.CreateParameter (Name, Type, Direction, Size, Value)

You have the value where the name should be. There must be some forgiveness in that department, I suppose.

 

[Shanti]

The @ symbol must be optional. However, I did discover that while the length and default value are optional, you still need to leave an empty space for each arguement. DUH! hehe I changed my call to:

cmd.Parameters.Append cmd.CreateParameter("disDate", adDBTimeStamp, adParamInput, , disDate)

And it works perfectly. Thanks so much for pointing me in the right direction!