View Full Version : How to pass datetime paramter form ASP to stored procedure

09-23-2005, 06:40 PM
The ASP code I have looks like this:
strDate = "2005-09-19"
rs.Open "my_proc " & cdate(strDate) , conn

In the stored procedure I would like to do something like:

@DateStamp DateTime
EXEC('SELECT * FROM tableName WHERE TimeStamp > '+ @DateStamp)

Obviously this doesn't work. I am getting an error like:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near '/'.

Can you show me how to pass datetime from ASP to stored procedure to populate a record set?

09-23-2005, 08:48 PM
Are you sure your stored Proc is formated correctly and runs in SQL Server? I would check this out first, before thinking it is a calling error with in your ASP code.

Below is how you can set Parameters for stored procs in ASP:

dim objCmd
Set objCmd = server.CreateObject("ADODB.Command")
objCmd.CommandType = adCmdstoredProc
objCmd.ActiveConnection = oConn
objCmd.CommandText = "storedProcName"

dim objParam
Set objParam = objCmd.CreateParameter("@ProcParamter",adVarChar,adParamInput,10,ValueToBePassedIn)

objCmd.Parameters.Append objParam