Call SQL Stored Procedure from ASP

• Martin Balbo

  SSC Eights!

  Points: 874

  More actions

  November 4, 2005 at 1:44 pm

  #102909

  Hi friends, i want to call a SP from ASP (not dot net) that returns some many rows.

  The conn string is:

  <%

      Set objCon = CreateObject("ADODB.Connection")

      Set objRS = CreateObject("ADODB.Recordset")

      objcon.connectionstring = "Provider=SQLOLEDB.1;User ID=USER;Password=XXXX;Persist Security Info=True;Initial Catalog=opessa;Data Source=SERVER01"   

      iCursorType                = 1

      iLockType                = 3

      objCon.Open

      %>

  Then i use the following sentence to call the SP from ASP

   SQLtxt = "exec SPNAME '" & PARAM1 & "','" & PARAM2 & "','" & PARAM3 & "'"  objRS.open SQLTXT, objcon

  And last, i try to print the results:

   WHILE NOT ObjRS.EOF

             Response.Write "

  " & ObjRS("XXXXX").VALUE & "" & ObjRS("XXXXX").VALUE & "" & ObjRS("XXXXX").VALUE & " Mb" & "" & ObjRS("XXXXX").VALUE & " Mb" & "" & ObjRS("XXXXX").VALUE & " %" & "

  "

  ObjRS.Movenext

  WEND

  ObjRS.CLOSE

  But i receive the following error:

  ADODB.Recordset error '800a0e78'

  Operation is not allowed when the object is closed.

  Whats wrong??? Anyone can help me?

  Thanks in advance.

   

   

• Martin Balbo

  SSC Eights!

  Points: 874

  More actions

  November 4, 2005 at 1:52 pm

  #602468

  I have a cursor inside the SP.

• Martin Balbo

  SSC Eights!

  Points: 874

  More actions

  November 4, 2005 at 1:52 pm

  #602467

  I have a cursor inside the SP.

• Martin Balbo

  SSC Eights!

  Points: 874

  More actions

  November 4, 2005 at 1:52 pm

  #602466

  I have a cursor inside the SP.

• David.Poole

  SSC Guru

  Points: 76001

  More actions

  November 4, 2005 at 2:16 pm

  #602476

  I would use the ADO Command object to call your stored procedure.

  I don't have my ADO books to hand but it is a case of setting

  Command.CommandText to the name of your stored procedure.

  Command.CommandType to the value of adStoredProcedure

  Command.ReturnsRecords to an appropriate setting.

  Appending parameters to the Command object, then Command.Execute.

  There are plenty of examples still available on the net such as http://www.developerfusion.co.uk/show/2270/

  LinkedIn Profile