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.
