Execute a Stored Procedure

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Execute a Stored Procedure

Logged in as: Guest

arrSession:exec spGetSession 2,3,66

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> ASP >> Execute a Stored Procedure
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Execute a Stored Procedure - 5/31/2001 12:17:43 AM

rahul27

Posts: 19
Score: 0
Joined: 5/23/2001
From: India
Status: offline

Hi,

I need to be able to execute an Oracle stored procedure from VBScript - can anyone give me any pointers with how to do it?

Thanks

Post #: 1

Re: Execute a Stored Procedure - 5/31/2001 12:19:09 AM

pia

Posts: 13
Score: 0
Joined: 5/31/2001
From: USA
Status: offline

This works with SQL Server but should work as well with Oracle...

open your connection with standard methods

use the following to retrieve a recordset

sqlQuery = "exec sp_name '" & anyvariables1 & "', '" & anyvariables2 & "'" 'etc...
recordSet.Open sqlQuery,connectionObject,adOpenForwardOnly 'or whatever keyset (adOpenForwardOnly = 0)

Hope this helps,
PIA

(in reply to rahul27)

Post #: 2

Re: Execute a Stored Procedure - 5/31/2001 12:20:37 AM

rahul27

Posts: 19
Score: 0
Joined: 5/23/2001
From: India
Status: offline

That sounds simple enough to me. Just one thing, do I need to Open a recordset if I just want to execute a stored procedure and not return anything?
Thanks very much for your help.

(in reply to rahul27)

Post #: 3

Re: Execute a Stored Procedure - 5/31/2001 12:21:04 AM

rahul27

Posts: 19
Score: 0
Joined: 5/23/2001
From: India
Status: offline

I also don't know how to open a connection to the datrabase using VBScript - can anyone provide me with the syntax for this also?

Thanks again

(in reply to rahul27)

Post #: 4

Re: Execute a Stored Procedure - 5/31/2001 12:22:06 AM

pia

Posts: 13
Score: 0
Joined: 5/31/2001
From: USA
Status: offline

Go to the link below for an excellent tutorial on creating ASP ADODB connections...

http://www.able-consulting.com/ADO_Faq.htm

I believe you can simply open your connection then execute against the connection ( conn.Execute sqlQuery ) or you can open a recordset as detailed above but use the static cursor keyset. I list the cursor constants below...

Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

(in reply to rahul27)

Post #: 5

Re: Execute a Stored Procedure - 5/31/2001 12:23:26 AM

Therarez

Posts: 12
Score: 0
Joined: 5/22/2001
From: Mexico
Status: offline

here is a example right out of my .ASP page to open a DSN-less connection to SQL

Set Conn = server.CreateObject("ADODB.Connection")
Conn.Open "driver=SQL

Server;server=yourserver.com;uid=userid;pwd=password;database=databasename;"
Set RS = Conn.Execute("SELECT Count(*) AS Recs FROM [Copy-PartMaster Lite]

Where [STOCK_CODE] Like N'%" & pnum & "%' And [Description] Like N'%" & desc & "%'")
Set RS1 = Conn.Execute("SELECT [STOCK_CODE], [Description], [SELLING_PRICE] FROM

[Copy-PartMaster Lite] Where [STOCK_CODE] Like N'%" & pnum & "%' And [Description] Like

N'%" & desc & "%'")
Set RS2 = Conn.Execute("SELECT * From Customers Where [CUSTOMER] ='" & Customer

& "'")
Response.Write "Terms: " & RS2("PYMTRMID")
%>

I am opening 2 recordsets and this example shows how to put in variable sform your page "pnum" and "desc" are being passed from the page before:

put these two line above your connection code.

<%pnum=request.querystring("PartNumber")%>
<%desc=request.querystring("Description")%>

PartNumber and Description are text boxes on the page that calls this page

(in reply to rahul27)