Stored Procedures and ASP

Results 1 to 3 of 3

Thread: Stored Procedures and ASP

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedures and ASP

    I&#039;m executing a stored procedure from an ASP script and having a problem. The stored procedure does a number of updates before eventually selecting a recordset. The recordset object being returned to ASP isn&#039;t the last select statement of the procedure but rather the first UPDATE. How dow I ensure that the recordset object gets the final select statement of the stored procedure?<BR><BR>--Brian

  2. #2
    royv Guest

    Default RE: Stored Procedures and ASP

    If you are using SQL Server, you can use SET NOCOUNT ON statement at the beginning of your stored procedure, and then right before you do your final SELECT statement do a SET NOCOUNT OFF.

  3. #3
    Join Date
    Dec 1969

    Default RE: Stored Procedures and ASP

    Be sure your ADODB object instance has the correct property setting.<BR>------------------<BR>Dim cmd &#039; -- reference to ADODB object<BR>Dim rs &#039; -- reference to recordset<BR>set cmd = server.createobject("ADODB.Command")<BR>With cmd<BR> .ActiveConnection = conn &#039; -- your connection reference<BR> .CommandType = &H0004 &#039; -- from ( adCmdStoredProc)<BR> .CommandText = "YourStoredProcName"<BR> &#039; -- if you have params append them here<BR> set rs = .Execute() &#039; -- set a reference to this Execution<BR>End With<BR>set cmd = nothing<BR>----------------<BR>Now rs can be used like a ADODB.Recordset object and should contain a reference to the data/recordset from your stored procedure.<BR><BR>If your stored proc still doesn&#039;t show the most recent select statement, make sure your stored proc code is utilizing a sub-query for your update commands and not returning this sub-query as a recordset. Try executing the stored procedure from the SQL query analyzer to see what the results are. If there are no return parameters just use the "EXECUTE [ProcedureName]" command in the Query Analyzer.<BR><BR>Hope this helps,<BR>Reginald

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts