Solved

Run a store procedure and get the select result to be showed - how?

Posted on 2013-01-15
6
265 Views
Last Modified: 2013-01-30
Hello guys

I have a store procedure and I want to run it, but at the end of my store procedure, there is a query that show all the rows.

How to run the store procedure and get the rows to display it in asp?

thanks
0
Comment
Question by:hidrau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 38780082
just assign to a recordset like you would with a normal sql satatement:

sql = "exec storedProcedure 1, 2, 3"
set rs = conn.Execute( sql )

do while not rs.eof
    '--display data
    rs.MoveNext
loop
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 38780087
or if you're using a command object, prepare the command and use the following to put the data into a recordset:

rs.Open commandObject
0
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38781407
If you have a stored procedure in your sql database.  You will create a function to call the stored procedure and in your asp code call the function to get the value.  Below is what I use to get the most recent added record.  But you can loop through as suggested above to get all or part of the rows.  
<%
' include adovbs file
theID=AddNewThing("Tonka Truck")
response.write theID


Function AddNewThing(widget)

Set cmd = Server.CreateObject("ADODB.Command")
With cmd

   	.ActiveConnection = connMyConnection
    .CommandType = adCmdStoredProc
   	.CommandText = "addNewThing" ' name of your stored procedure
   
   	.Parameters.Append .CreateParameter("@newThing",adVarWChar, adParamInput, 50)
   	.Parameters("@newThing") = widget

   set rsName = .Execute
End With

 AddNewThing= rs(0) 
 
set cmd = nothing
set rs = nothing

end Function

%>

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:hidrau
ID: 38782112
Hello

I tried this
 Sql = " EXECUTE STP_CRMPOSITIVACAO " & Clie & ",-2 "
 Call abre_conexao_hidrau   ' open conection
 Set Rs = Server.CreateObject("ADODB.RecordSet") 
 Rs.ActiveConnection = CnxHidrau


I tried this way:  Rs.Open CnxHidrau.Execute(Sql)

I tried this way: set Rs = CnxHidrau.Execute(Sql)

 If Not Rs.Eof Then 
 End if

Open in new window


When I run my code I getting the error here "
 If Not Rs.Eof Then"

The message is:

Operations doesn't allow when the object is closed
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 500 total points
ID: 38782757
try this:

Sql = " EXECUTE STP_CRMPOSITIVACAO " & Clie & ",-2 "

 Call abre_conexao_hidrau   ' open conection

 Set Rs = Server.CreateObject("ADODB.RecordSet")

set Rs = CnxHidrau.Execute(Sql)

 If Not Rs.Eof Then
 End if

if it doesnt work, your 2nd line isn't returning a connection object.
0
 
LVL 1

Author Closing Comment

by:hidrau
ID: 38834941
thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Button to go back 3 44
Cannot open file from ASP if user is "logged in" 13 40
Html value of radio 14 53
Pass SQL to JSON. Page is in classic ASP and using Jquery 4 57
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question