Re: execute stored procedure in ASP
From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 03/10/04
- Next message: Bob Barrows [MVP]: "Re: can't connect to DSN"
- Previous message: Umashankar: "Problem loading list view control in asp page."
- In reply to: Matt: "execute stored procedure in ASP"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 10 Mar 2004 08:40:33 -0500
Matt wrote:
> I want to exexute stored procedure in ASP, but it has error "Microsoft
> VBScript compilation (0x800A0401) Expected end of statement" on line
> (1).
>
> The stored procedure "sp_emp" contain "select * from Employee;"
>
> <%
> Dim objRS, sqlStmt
> set objRS = Server.CreateObject("ADODB.Recordset")
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.open strConnect
> Set objRS = conn.Execute "sp_emp" '<========(1) execute stored
> procedure
> //etc...
> %>
>
> please advise! thanks!!
You need parentheses aroung the argument for the Execute method because you
are using the result returned by the method (the recordset object being
returned). Like this:
Set objRS = conn.Execute("sp_emp")
more correct:
Set objRS = conn.Execute("exec sp_emp")
even more correct:
Set objRS = conn.Execute("exec sp_emp",,1)
You should tell ADO that you are passing a string to be executed on the
server by using the Options argument. The "1" in the above statement is the
value enumerated by the adCmdText constant. If you had the ADO constants
defined, either by using #include to include the adovbs.inc file, or by
using a METADATA tag in your global.asa file to reference the ADO type
library (http://www.aspfaq.com/show.asp?id=2112), you could make the above
line a little more readable by changing it to:
Set objRS = conn.Execute("exec sp_emp",,adCmdText)
Better yet, you could be really efficient and execute the procedure as a
sotred procedure rather than using a sql string. Since you have no output
parameters and aren't interested in reading the value reuturned by the
RETURN statement in your procedure, you can use the
"stored-procedure-as-connection-method" technique. Like this:
set objRS = Server.CreateObject("ADODB.Recordset")
conn.sp_emp objRS
If your procedure required parameters, you would do it like this:
conn.sp_emp paramval1, ... , paramvalN, objRS
If your procedure does not return a resultset, simply leave off the
recordset variable:
conn.sp_emp paramval1, ... , paramvalN
HTH,
Bob Barrows
-- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
- Next message: Bob Barrows [MVP]: "Re: can't connect to DSN"
- Previous message: Umashankar: "Problem loading list view control in asp page."
- In reply to: Matt: "execute stored procedure in ASP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
