Getting return values from a stored procedure - asp/vbscript

I have a stored procedure that inserts a row into a table, then
gets the id for that row as a return value.

the sp is called from a form on my page.  my question is,
how can i get that return value back on the page?

tia,
Dena
LVL 1
denamAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jerryrossConnect With a Mentor Commented:
I had this problem about two days ago...

You need to close the recordset first before you can get the return value.



Create a recordset variable and Set it equal to the cmd.execute() statement that executes your stored procedure.

in ASP:

dim myRS as recordset
dim cmd as command
dim cn As New ADODB.Connection
dim returnVal as integer

cn.Open ("Data Source=myDSN;Database=DBName;User Id=myID;Password=myPwd;")

cmd.ActiveConnection = cn
 
cmd.CommandText = Your_SP_Name
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("Retval",adInteger,adParamReturnValue,4)

.... (additonal parameter.append statements go here
....


Set myRS = cmd.execute()

myRS.Close

returnVal = cmd.Parameters.Item(0).Value

You would need to change the above code a bit to set up the connection string and also execute your stored procedure (just look through the code and make the appropriate changes)
0
 
denamAuthor Commented:
Jerry, Sorry to be so slow in accepting your answer.   I'm
struggling to get this page working and dont' really know
whether your answer will be correct til i have the page
working.  Thanks for your patience.
0
 
denamAuthor Commented:
jerry, maybe you can help me figure out why I'm getting
this error message:

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'CommandType'

/management/MinDetail.asp, line 22

here is the code that is supposed to create and execute
calls to the database:

<%@ LANGUAGE="VBSCRIPT" %>

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open("DSN=myDSN; UID=myUID; PWD=myPWD")

if( Request("action").Count > 0 ) then
      action = request("action")
      cmd = server.CreateObject ("ADODB.Command")
      cmd.CommandType = adCmdStoredProc
      cmd.ActiveConnection = Conn
      tmpParam = cmd.CreateParameter("Return Value", 3, 4, 4)
      cmd.Parameters.Append(tmpParam)

      else
      action = "nothing"
      end if

if( Request("MinId").Count > 0 ) then
      'Response.Write isNumeric(Request("MinId"))
      if( isNumeric( Request("MinId")) = FALSE ) then
            MinId = CInt( Request("MinId"))
            end if
      end if


if( action = "change" ) then
      cmd.Text = ( "updCommitteeMinutes" )
      tmpParam = cmd.CreateParameter ("@sPrettyName", 200, 1, 255, Request("prettyname"))
      cmd.Parameters.Append(tmpParam)
      tmpParam = cmd.CreateParameter ("@sDate", 200, 1, 255, sDate)
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@sNew", 200, 1, 255, Request("New"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@sDocName", 200, 1, 255, Request("docname"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@sCity", 200, 1, 255, Request("City"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@tBody", 201, 1, 2147483647, Request("Body"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@iComId", 3, 1, 4, Request("ComId"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@iId", 3, 1, 4, Request("MinId"))
    cmd.Execute()
    cmd = null
      MinId = CInt( Request("MinId"))      
      end if
      

if( action = "add" ) then
      cmd.Text = ( "addCommitteeMinutes" )
      tmpParam = cmd.CreateParameter ("@sPrettyName", 200, 1, 255, Request("prettyname"))
      cmd.Parameters.Append(tmpParam)
      tmpParam = cmd.CreateParameter ("@sDate", 200, 1, 255, sDate)
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@sNew", 200, 1, 255, Request("New"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@sDocName", 200, 1, 255, Request("docname"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@sCity", 200, 1, 255, Request("City"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@tBody", 201, 1, 2147483647, Request("Body"))
    cmd.Parameters.Append(tmpParam)
    tmpParam = cmd.CreateParameter ("@iComId", 3, 1, 4, Request("ComId"))
    cmd.Parameters.Append(tmpParam)
    res = cmd.Execute()
    MinId = CInt(res("id"))
    cmd = null
      action = "change"
      end if
      

As you can see the page is in vbscript.  Does the error mean that i've created the wrong type of recordset?  If so, how should
i create the recordset?  The line that generates the
error is: cmd.CommandType = adCmdStoredProc


Thanks,
Dena

btw, This is really
more involved than the original question, I can either increase the points or submit it as a separate question.  since they're so
interrelated, I'd prefer to increase the points.  But I'm open, depending on what your preference is.  
0
 
jerryrossCommented:
Do you have the following line at the top of your asp page?

<!-- #include file="adovbs.inc" -->

This file contains all ADO constants for vbscript.  This way you don't have to use numbers with the createParameter statement.

Also, your line of code which reads:

cmd = server.CreateObject ("ADODB.Command")

should be:

SET cmd = server.CreateObject ("ADODB.Command")

If you don't include the above file into your page then the value for adCmdStoredProc is:  &H00004

If you need the adovbs.inc file, I can send it to you.

Hope this helps...please increase points.
Thank you,
Jerry


0
 
denamAuthor Commented:
Jerry, I'm getting a little farther.

I've changed some of the code to:

if( Request("action").Count > 0 ) then
      action = request("action")
      set cmd = server.CreateObject ("ADODB.Command")
      cmd.CommandType = 4
      cmd.ActiveConnection = Conn
      cmd.Parameters.Append
      cmd.CreateParameter("Return Value", 3, 4, 4)

and now i'm getting this error:

Microsoft VBScript compilation error '800a0414'

Cannot use parentheses when calling a Sub

/management/MinDetail.asp, line 25

cmd.CreateParameter("Return Value", 3, 4, 4)
--------------------------------------------^

Is this ASP?  If so, why the difference in syntax when
I'm using vbscript?  Obviously i'm pretty confused :)

thanks,
Dena

p.s.  i appreciate you're help.  if you don't think you're getting
enough points, or if this drags on, i'll increase the points.  I
need to get this working.
0
All Courses

From novice to tech pro — start learning today.