Re: SP - output parameter not coming back

Axel wrote:
> Hi
>
> I have a stored procedure that returns a recordset with search
> results. I also want it to return the number of records in an output
> parameter. The problem is it does not seem to transmit anything to
> the server page - what could be wrong? (just for test purposes I
> tried to assign a fixed value, or change the order of parameters but
> it is simply not read) - instead it seems to assign Null no matter
> what I tried. The recordset is returned as expected though. I changed
> the cursor type to client side in order to get the correct count.
>
> Also is there any way I can test output params in SQL Query Analyzer?

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/msg/2c935bd7c531d82b

>
> thanks in advance
> Axel
>
> ---------- Code follows --------
>
> Stored Proc code:
> CREATE PROCEDURE dbo.spFindProducts
> @mycount integer OUTPUT,
> @whereString varchar (1000)
> AS
>
> --SET NOCOUNT ON

Why do you have this commented out? You need to have this turned on to avoid
having your procedure return extra resultsets containing the number of
records affected by each statement. This statement being turned on will NOT
affect the contents of @@ROWCOUNT


<snip>
>
> Set CmdSP = Server.CreateObject("ADODB.Command")
> '-- Make an ODBC connection to the (local) SQL server,
>

Don't use ODBC:
http://www.aspfaq.com/show.asp?id=2126

>
> Dim rs
> Dim lRecCount
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> CmdSP.ActiveConnection = Application("cnn")

If you are storing a Connection object in Application, that is a bad idea:
http://www.aspfaq.com/show.asp?id=2053


If Application("cnn") is a connection string, then you are still using a bad
practice: ALWAYS use an explicit Connection object so as not to disable
connsection/session pooliing:

set cn=createobject(adodb.connection")
cn.open Application("cnn")
Set CmdSP.ActiveConnection = cn

> CmdSP.CommandText = "spFindProducts"
> CmdSP.CommandType = 4 ' adCmdStoredProc
>

You forgot to append the return parameter. Even though you are not using it,
it must be the first parameter appended to the collection.

>
> ' CmdSP.Parameters.Append _
> ' CmdSP.CreateParameter("@mycount", adInteger, adParamOutput)
> CmdSP.Parameters.Append _
> CmdSP.CreateParameter("@mycount", 3, 2)
>
> ' CmdSP.Parameters.Append
> CmdSP.CreateParameter("@whereString",adVarChar=200, 1, len(sWhere),
> sWhere) ' adVarChar, adParaminput, ...)
> CmdSP.Parameters.Append _
> CmdSP.CreateParameter("@whereString", adVarChar, _
> adParamInput, len(sWhere), sWhere)
>
>
> ' RECORDSET OBJECT
> ' Open recordset (from stored Proc)
> Dim dummyArr
> Set rs = CmdSP.Execute( ) ' RecordCount can not be used
> ' set cursor type to clientside for this to work? =>
> ' @mycount: THIS PARAMETER EXISTS, BUT IT HAS NO VALUE FOR SOME
> ' UNKNOWN REASON!
> ' tried: changing param order
> ' assigning constant in SP
> ' not executing & filling the recordset
> ' seems to always pass back empty
> ' append identifier ".Value"
> lRecCount = CmdSP.Parameters("@mycount").Value
>
>
> ' in order to use recordcount we need to
> ' set the cursortype to something different than forward only
> ' then movelast - get RecordCount and MoveFirst...
>

Output and Return parameter values are not sent until all the resultsets
generated by the procedure are sent to the client. This means that you must:
1. When using forward-only cursors, close your recordset before attempting
to read the output and return parameters
2. When using a client-side cursor, the entire static resultset is sent when
the recordset is opened, so the output and return parameters are immediately
sent.

You may be interested in my free stored procedure code generator available
here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Closed recordset on Integrated Security?
    ... > There is a VB 6 ActiveX.exe that maintains an ADO connection object. ... > either a recordset or a variable array of the recordset ... > Using the same scenario, using SQL server logons, the recordset is NOT ... > ONLY arises when a cursor is in the stored procedure. ...
    (microsoft.public.vb.database.ado)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Not Getting results from Stored procedure
    ... So, running a stored procedure from VB6 can be done fairly easily and while there are fewer whitepapers on how to best do so, I thought my website might still have a few but there are not that many left--the problem is, Microsoft is pulling old unsupported content from MSDN so a lot of those links are gone. ... In any event, when calling a stored procedure, you need to include phrase SET NOCOUNT ON, so that the 'records affected' message that occur from results of Stored proc processing aren't returned to the calling application. ... These messages appear to be returned to the calling application as a recordset. ... when calling a stored proc you need ignore the command object and use a syntactically correct string as the source parameter for the recordset's 'Open' method. ...
    (microsoft.public.vb.database)
  • Re: vbscript logon script getting return data from sql sp
    ... Loop ... > One way to retrieve values from a stored procedure is with a Recordset ...
    (microsoft.public.scripting.vbscript)
  • Re: Missing Resultset When Calling an Stored Procedure w/a Nested
    ... we tried advancing to the next recordset by using the NextRecordSet ... program only returned the nested stored procedure call resultset and failed ...
    (microsoft.public.data.ado)
Loading