ASP Stored Procedure HELP
I need to run a stored procedure against a SQL Server database this is my
code so far; see below. Obviously I copied this from a Wrox book that I bought last night. I am new to SQL Server and Stored Procs I NEED HELP PLEASE. I am not trying to get lazy here I just need to finish this project.
I need to return these parameters to the browser
UserLogId
From_dt
To_dt
Once I see this done I will be able to figure out what is going on. I could
also use some tips on the HTML for the form.
Thank you in advance!
<?php
Dim dbConnection, rs, strConnect, cmd
strConnect = Application("dbConnection")
Set dbConnection = Server.CreateObject("ADODB.Connection")
Set cmdExample = Server.CreateObject("ADODB.Command")
dbConnection.Open strConnect
cmd.ActiveConnection = dbConnection
cmd.CommandText = "ap_contactlist"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter _
("return",adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter _
("@UserLogId", adCurrency, adParamInput)
cmd.Parameters.Append cmd.CreateParameter _
("@From_dt", adCurrency, adParamInput)
cmd.Parameters.Append cmd.CreateParameter _
("@To_dt", adVarChar, adParamInput, 12)
cmd.Parameters("@UserLogId").Value = 10
cmd.Parameters("@From_dt").Value = 20
cmd.Parameters("@To_dt") = "psychology"
Set rs= cmd.Execute
Dim strTable
strTable = rs.GetString(adClipString, , "</TD><TD>", _
"</TD></TR><TR><TD>")
Response.Write "<TABLE BORDER=1><TR><TD>" & strTable & "</TABLE>"
rs.Close
Response.Write "Return value = " & _
cmd.Parameters("return").Value
Set cmd = Nothing
dbConnection.Close
Set rs = Nothing
Set dbConnection = Nothing
?>
Peter J. Boettcher posted this at 14:13 — 19th February 2001.
They have: 812 posts
Joined: Feb 2000
1aspfan,
1. If you have access to your global.asa file I would recommend putting the METADATA reference in it. Then all pages that require it have access to it.
2. Without seeing the SP it's hard for me to help you since I don't know what it's doing.
3. You said you have to return UserLogId, From_dt , and To_dt to the browser. You seem to be passing these values to the SP so it looks like you have them already.
4. Noting 2 + 3 above, I would write the ASP like follows:
Set dbConnection = Server.CreateObject("ADODB.Connection")
Set cmdExample = Server.CreateObject("ADODB.Command")
dbConnection.Open Application("dbConnection")
With cmdExample
.ActiveConnection = dbConnection
.CommandText = "ap_contactlist"
.CommandType = adCmdStoredProc
.Parameters.Append cmdExample.CreateParameter "return",adInteger,adParamReturnValue)
.Parameters.Append cmdExample.CreateParameter("UserLogId", adCurrency, adParamInput,8,10)
.Parameters.Append cmdExample.CreateParameter("From_dt", adCurrency, adParamInput,8,20)
.Parameters.Append cmdExample.CreateParameter("To_dt", adVarChar, adParamInput,12,"psychology")
.Parameters.Append cmdExample.CreateParameter("UserLogID_Output",adCurrency,adParamOutput,8,0)
.Parameters.Append cmdExample.CreateParameter("From_dt_Output",adCurrency,adParamOutput,8,0)
.Parameters.Append cmdExample.CreateParameter("To_dt_Output",adVarChar,adParamOutput,12,"EMPTY")
End With
Set dbConnection = cmdExample.Execute
dbConnection.Close
Set dbConnection = Nothing
Return_Value = cmdExample.Parameters("return").Value
UserLogID_Return = cmdExample.Parameters("UserLogID_Output").Value
From_dt_Return = cmdExample.Parameters("From_dt_Output").Value
To_dt_Return = cmdExample.Parameters("To_dt_Output").Value
Set cmdExample = Nothing
Note: I haven't done any error checking in this example. For real world use you should always include error handling.
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.