By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

execute stored procedure in ASP

P: n/a
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!!
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Type the line out again manually. Sounds like you copied it from some other
source and it brought non-printing characters along with it? The syntax
looks fine to me.

Also might want to see http://www.aspfaq.com/2201

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Matt" <ma*******@hotmail.com> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...
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!!

Jul 19 '05 #2

P: n/a
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.
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.