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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryrossCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.