Newbie - Stored Procedures/ASP Question

[newbie]

I've created and tested a Stored Procedure in SQL Server 2000, and it works.
It stores data to 2 tables, and returns an output parameter and result code.

However, when I try to call it from ASP, nothing seems to happen. The
result code is returned empty, yet when compared to "0", returns "true" on
the ASP page. Also the stored procedure never executes in SQL.

The methodology I'm using is:
set cn = CreateObject("adodb.connection")
cn.ConnectionString =
"Provider=SQLOLEDB;Server=server;Database=db;uid=i d;pwd=password;"
cn.open

set cmd = server.CreateObject("adodb.Command")
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append
cmd.CreateParameter("@MyInput1",adVarChar,adParamI nput,10,request("FormInput
1"))
cmd.Parameters.Append
cmd.CreateParameter("@MyInput2",adVarChar,adParamI nput,10,request("FormInput
2"))
cmd.Parameters.Append
cmd.CreateParameter("@MyOutput",adInteger,adParamO utput,0)
cmd.Parameters.Append cmd.CreateParameter("@MyError", adInteger,
adParamReturnValue,0)
cmd.CommandText = "spMyProcedureName"

When I Do a Response.Write on the parameters, above, I get:
@MyInput1: correctly gets Whatever was typed on form
@MyInput2: correctly gets Whatever was typed on form
@MyOutput:
@MyError:

cmd.CommandText: { call spMyProcedureName(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?) }

Why doesn't the command text set the parameters, properly, since I'm
directly referencing them by name, and how can I fix the problem? The input
parameters match the name in the SQL procedure, and I've tried both the same
and different names from the SQL procedure for the output and error
variables. Also, why does asp process a true to @MyError = 0, when the
variable is actually returned empty?

Thanks in advance. This will help me a lot.

[Bob Barrows]

newbie wrote:

> I've created and tested a Stored Procedure in SQL Server 2000, and it
> works. It stores data to 2 tables, and returns an output parameter
> and result code.
>
> However, when I try to call it from ASP, nothing seems to happen. The
> result code is returned empty, yet when compared to "0", returns
> "true" on the ASP page. Also the stored procedure never executes in
> SQL.
>
> The methodology I'm using is:
> set cn = CreateObject("adodb.connection")
> cn.ConnectionString =
> "Provider=SQLOLEDB;Server=server;Database=db;uid=i d;pwd=password;"
> cn.open
>
> set cmd = server.CreateObject("adodb.Command")
> cmd.ActiveConnection = cn
> cmd.CommandType = adCmdStoredProc
>
> cmd.Parameters.Append
>

cmd.CreateParameter("@MyInput1",adVarChar,adParamI nput,10,request("FormInput

> 1"))
> cmd.Parameters.Append
>

cmd.CreateParameter("@MyInput2",adVarChar,adParamI nput,10,request("FormInput

> 2"))
> cmd.Parameters.Append
> cmd.CreateParameter("@MyOutput",adInteger,adParamO utput,0)
> cmd.Parameters.Append cmd.CreateParameter("@MyError", adInteger,
> adParamReturnValue,0)
> cmd.CommandText = "spMyProcedureName"
>

<snip>

> :
>
> cmd.CommandText: { call spMyProcedureName(?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?) }
>

This is wrong. Assuming your parameters are built correctly, simply do this:
cmd.CommandText = spMyProcedureName
cmd.Execute ,,adExecuteNoRecords

Actually, I'm not sure you've built your Parameters collection completely. I
see 15 ?'s, implying that your procedure needs 15 parameters passed, but I
see only 4 Append statements. You need to create and append ALL of your
parameter objects.

You may want to try out my ADO Stored Procedure code generator available at:
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

> Why doesn't the command text set the parameters, properly, since I'm
> directly referencing them by name, and how can I fix the problem? The
> input parameters match the name in the SQL procedure, and I've tried
> both the same and different names from the SQL procedure for the
> output and error variables. Also, why does asp process a true to
> @MyError = 0, when the variable is actually returned empty?
>
> Thanks in advance. This will help me a lot.

You need to make sure you've included the line
SET NOCOUNT ON
at the beginning of your stored procedure.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[newbie]

Thanks for trying--I tried both suggestions:

removing the quotes from the name of the procedure
-AND-
adding the ,,adExecuteNoRecords after my execute

but it did not change anything--the results were the same.

As for the parameters, I did not list all of them on my post, but they are
all covered in my code.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OJpjIMM9DHA.3648@TK2MSFTNGP11.phx.gbl...

> newbie wrote:

> > I've created and tested a Stored Procedure in SQL Server 2000, and it
> > works. It stores data to 2 tables, and returns an output parameter
> > and result code.
> >
> > However, when I try to call it from ASP, nothing seems to happen. The
> > result code is returned empty, yet when compared to "0", returns
> > "true" on the ASP page. Also the stored procedure never executes in
> > SQL.
> >
> > The methodology I'm using is:
> > set cn = CreateObject("adodb.connection")
> > cn.ConnectionString =
> > "Provider=SQLOLEDB;Server=server;Database=db;uid=i d;pwd=password;"
> > cn.open
> >
> > set cmd = server.CreateObject("adodb.Command")
> > cmd.ActiveConnection = cn
> > cmd.CommandType = adCmdStoredProc
> >
> > cmd.Parameters.Append
> >

>

cmd.CreateParameter("@MyInput1",adVarChar,adParamI nput,10,request("FormInput

> > 1"))
> > cmd.Parameters.Append
> >

>

cmd.CreateParameter("@MyInput2",adVarChar,adParamI nput,10,request("FormInput

> > 2"))
> > cmd.Parameters.Append
> > cmd.CreateParameter("@MyOutput",adInteger,adParamO utput,0)
> > cmd.Parameters.Append cmd.CreateParameter("@MyError", adInteger,
> > adParamReturnValue,0)
> > cmd.CommandText = "spMyProcedureName"
> >

> <snip>

> > :
> >
> > cmd.CommandText: { call spMyProcedureName(?, ?, ?, ?, ?, ?, ?, ?,
> > ?, ?, ?, ?, ?, ?, ?) }
> >

>
> This is wrong. Assuming your parameters are built correctly, simply do

this:

> cmd.CommandText = spMyProcedureName
> cmd.Execute ,,adExecuteNoRecords
>
> Actually, I'm not sure you've built your Parameters collection completely.

I

> see 15 ?'s, implying that your procedure needs 15 parameters passed, but I
> see only 4 Append statements. You need to create and append ALL of your
> parameter objects.
>
> You may want to try out my ADO Stored Procedure code generator available

at:

>

[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

>
>

> > Why doesn't the command text set the parameters, properly, since I'm
> > directly referencing them by name, and how can I fix the problem? The
> > input parameters match the name in the SQL procedure, and I've tried
> > both the same and different names from the SQL procedure for the
> > output and error variables. Also, why does asp process a true to
> > @MyError = 0, when the variable is actually returned empty?
> >
> > Thanks in advance. This will help me a lot.

>
> You need to make sure you've included the line
> SET NOCOUNT ON
> at the beginning of your stored procedure.
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

newbie wrote:

> Thanks for trying--I tried both suggestions:
>
> removing the quotes from the name of the procedure
> -AND-
> adding the ,,adExecuteNoRecords after my execute
>
> but it did not change anything--the results were the same.
>
> As for the parameters, I did not list all of them on my post, but
> they are all covered in my code.
>

It makes it tough to help you debug a problem when we are not given all the
information ...


You did not mention whether or not you added the SET NOCOUNT ON statement
into your procedure ...

You say the procedure never executes? No errors? Do you have an On Error
Resume Next statement masking your errors? Comment it out and see if you get
any errors.

I reiterate my suggestion to give my stored procedure code generator a try.
It my help you resolve this problem.

Bob Barrows



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Adrian Forbes [ASP MVP]]

> cmd.CommandText: { call spMyProcedureName(?, ?, ?, ?, ?, ?, ?, ?, ?,
?,

> ?, ?, ?, ?, ?) }

You don't really need to do that. Make your commandtype a stored proc, the
command text the name of your proc, add the parameters as you have been
doing then just call .execute.

Your return codes etc, you might find that you only get an OUTPUT or
ReturnValue param properly populated after you have read through any
recordset you are returning.

[newbie]

Thanks Bob, I'm still stuck, though:

I tried shortening the Stored Procedure, and using the script from your
generator. The stored procedure still works fine, when called to test from
within SQL server. The asp page using your script method is below:

<%
Dim cmd, param

set conn = CreateObject("adodb.connection")
conn.ConnectionString =
"Provider=SQLOLEDB;Server=server;Database=db;uid=i d;pwd=password;"
conn.open

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "spInsert"
set .ActiveConnection=conn
set param = .createparameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@First", adVarChar, adParamInput, 80,
"first")
.parameters.append param
set param = .createparameter("@ID", adInteger, adParamOutput, 0)
.parameters.append param
.execute ,,adexecutenorecords
end with

Response.Write "First: " & cmd("@First") & "<br>"
Response.Write "ErrorCode: " & cmd("@RETURN_VALUE") & "<br>"
Response.Write "Proc: " & cmd.CommandText & "<br>"
Response.Write "OrderID: " & cmd("@ID") & "<br>"
%>

The response I get is "Page cannot be displayed". When I comment out the
execute, line, I get the following response:
First: first
ErrorCode:
Proc: { ? = call spInsert(?, ?) }
OrderID:

The SQL Stored Procedure is below. Strangely, before I shortened the
procedure for testing purposes, your generated script was working (with the
old procedure). I then tried to shorten the procedure, so that I could test
my own page, line by line, to see where I went wrong. The reason is that I
set different parameters for half of the information, and call a different
procedure based on some of the data sent through from the previous page. At
any rate, here's the SQL procedure. Any ideas? Thank you for your help in
this.


CREATE PROCEDURE spInsert
@First varchar(80)
,@ID int OUTPUT
AS
DECLARE @ErrorSave INT
SET @ErrorSave = 0

BEGIN TRANSACTION
SET NOCOUNT ON
INSERT INTO TestTable (FirstName) VALUES (@First)

--On Error, Undo & Quit
IF @@error <> 0
BEGIN
SET @ErrorSave = @@Error
ROLLBACK TRAN
RETURN @ErrorSave
END


--Get ID
SELECT @ID = @@Identity

--Insert Card Information
INSERT INTO Table2 (ID, TransDate) VALUES (@ID, GetDate())

--On Error, Undo & Quit
IF @@error <> 0
BEGIN
SET @ErrorSave = @@Error
ROLLBACK TRAN
RETURN @ErrorSave
END

COMMIT TRANSACTION
RETURN @ErrorSave

GO

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Op8vutM9DHA.1548@tk2msftngp13.phx.gbl...

> newbie wrote:

> > Thanks for trying--I tried both suggestions:
> >
> > removing the quotes from the name of the procedure
> > -AND-
> > adding the ,,adExecuteNoRecords after my execute
> >
> > but it did not change anything--the results were the same.
> >
> > As for the parameters, I did not list all of them on my post, but
> > they are all covered in my code.
> >

>
> It makes it tough to help you debug a problem when we are not given all

the

> information ...
>
>
> You did not mention whether or not you added the SET NOCOUNT ON statement
> into your procedure ...
>
> You say the procedure never executes? No errors? Do you have an On Error
> Resume Next statement masking your errors? Comment it out and see if you

get

> any errors.
>
> I reiterate my suggestion to give my stored procedure code generator a

try.

> It my help you resolve this problem.
>
> Bob Barrows
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows [MVP]]

First of all, you need to turn off the "Friendly Error messages" option in
your browser so you can get a more informative error message than "Page
cannot be displayed".

More below:

newbie wrote:

> CREATE PROCEDURE spInsert
> @First varchar(80)
> ,@ID int OUTPUT
> AS
> DECLARE @ErrorSave INT
> SET @ErrorSave = 0
>
> BEGIN TRANSACTION
> SET NOCOUNT ON

This needs to be beffore the SET @ErrorSave statement. SET NOCOUNT ON should
come before any other SET, SELECT, etc. statement. Like this:
CREATE PROCEDURE spInsert
@First varchar(80)
,@ID int OUTPUT
AS
SET NOCOUNT ON

DECLARE @ErrorSave INT
SET @ErrorSave = 0

BEGIN TRANSACTION

If that does not solve your problem, then post a CREATE TABLE script so I
can try it out on my server.

I am a little concerned that the CommandText is being converted to the ODBC
execution syntax ( { ? = call spInsert(?, ?) } ). I've never seen that
happen. What version of MDAC are you using?

Oh wait! You commented out the Execute statement to avoid the error? I
suspect you don't have the adExecuteNoRecords constant defined. Add
Const adExecuteNoRecords = &H00000080
to your asp page, or use the technique described here:
[url]http://www.aspfaq.com/show.asp?id=2112[/url]

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.

[newbie]

Friendly messages were already on, but didn't show, for some reason, until I
moved the SET NOCOUNT ON to he top of the procedure, as you suggested. Then
I discovered, I forgot to give myself "execute" permission on my new test
query. Now, I ran it, and it did insert the records, correctly but the
results came back with the following (not the ODBC Syntax is still showing).
I don't have direct access to the server to find out the version of MDAC. I
do know it is a Windows 2000 box. I added the line you gave me to our
ADOVBS.INC file, but it still produces the same results. Now I'll just have
to compare that procedure to my other code for results. Thanks.

First: t1
ErrorCode: 0
Proc: { ? = call procInsertGANetTest2(?, ?) }
OrderID: 116745



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eYvdrKl9DHA.328@tk2msftngp13.phx.gbl...

> First of all, you need to turn off the "Friendly Error messages" option in
> your browser so you can get a more informative error message than "Page
> cannot be displayed".
>
> More below:
>
> newbie wrote:

> > CREATE PROCEDURE spInsert
> > @First varchar(80)
> > ,@ID int OUTPUT
> > AS
> > DECLARE @ErrorSave INT
> > SET @ErrorSave = 0
> >
> > BEGIN TRANSACTION
> > SET NOCOUNT ON

>
> This needs to be beffore the SET @ErrorSave statement. SET NOCOUNT ON

should

> come before any other SET, SELECT, etc. statement. Like this:
> CREATE PROCEDURE spInsert
> @First varchar(80)
> ,@ID int OUTPUT
> AS
> SET NOCOUNT ON
>
> DECLARE @ErrorSave INT
> SET @ErrorSave = 0
>
> BEGIN TRANSACTION
>
> If that does not solve your problem, then post a CREATE TABLE script so I
> can try it out on my server.
>
> I am a little concerned that the CommandText is being converted to the

ODBC

> execution syntax ( { ? = call spInsert(?, ?) } ). I've never seen that
> happen. What version of MDAC are you using?
>
> Oh wait! You commented out the Execute statement to avoid the error? I
> suspect you don't have the adExecuteNoRecords constant defined. Add
> Const adExecuteNoRecords = &H00000080
> to your asp page, or use the technique described here:
> [url]http://www.aspfaq.com/show.asp?id=2112[/url]
>
> 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.
>
>

[Bob Barrows [MVP]]

newbie wrote:

> Friendly messages were already on, but didn't show, for some reason,

No, I wanted you to turn them off :-)

> until I moved the SET NOCOUNT ON to he top of the procedure, as you
> suggested. Then I discovered, I forgot to give myself "execute"
> permission on my new test query. Now, I ran it, and it did insert
> the records, correctly but the results came back with the following
> (not the ODBC Syntax is still showing). I don't have direct access to
> the server to find out the version of MDAC. I do know it is a
> Windows 2000 box. I added the line you gave me to our ADOVBS.INC
> file, but it still produces the same results. Now I'll just have to
> compare that procedure to my other code for results. Thanks.
>
> First: t1
> ErrorCode: 0
> Proc: { ? = call procInsertGANetTest2(?, ?) }
> OrderID: 116745

Aren't these the correct results? There does not appear to be an error ...
or is that your point?


--
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.

[newbie]

Yes, the results are correct, from your exact script. However, when I try
to use your scripting technique, in my code, it still errors--the procedure
never executes. The only differences I can see is that I put
set cmdInsert = server.CreateObject("adodb.Command")
cmdInsert.CommandType = adCmdStoredProc
set cmdInsert.ActiveConnection = Conn
before the <HTML> tag, and then the rest of the parameters are set later in
the <body> tag. I also don't use the "With" block, as I need to set certain
parameters based on certain conditions and the .CommandText calls one of 2
procedures based on a condition--however, I haven't incorprated that portion
into my code, yet. Would the fact that the asp is not all grouped together
be causing a problem?

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uolwvyv9DHA.2324@tk2msftngp13.phx.gbl...

> newbie wrote:

> > Friendly messages were already on, but didn't show, for some reason,

>
> No, I wanted you to turn them off :-)
>
>

> > until I moved the SET NOCOUNT ON to he top of the procedure, as you
> > suggested. Then I discovered, I forgot to give myself "execute"
> > permission on my new test query. Now, I ran it, and it did insert
> > the records, correctly but the results came back with the following
> > (not the ODBC Syntax is still showing). I don't have direct access to
> > the server to find out the version of MDAC. I do know it is a
> > Windows 2000 box. I added the line you gave me to our ADOVBS.INC
> > file, but it still produces the same results. Now I'll just have to
> > compare that procedure to my other code for results. Thanks.
> >
> > First: t1
> > ErrorCode: 0
> > Proc: { ? = call procInsertGANetTest2(?, ?) }
> > OrderID: 116745

>
> Aren't these the correct results? There does not appear to be an error ...
> or is that your point?
>
>
> --
> 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.
>
>

[newbie]

Nevermind--I didn't realize I had to set up the parameters in order--I had
the return value set up towards the end.

"newbie" <netinsane@aol.REMOVETHIS.com> wrote in message
news:u4Q8fQw9DHA.2524@TK2MSFTNGP11.phx.gbl...

> Yes, the results are correct, from your exact script. However, when I try
> to use your scripting technique, in my code, it still errors--the

procedure

> never executes. The only differences I can see is that I put
> set cmdInsert = server.CreateObject("adodb.Command")
> cmdInsert.CommandType = adCmdStoredProc
> set cmdInsert.ActiveConnection = Conn
> before the <HTML> tag, and then the rest of the parameters are set later

in

> the <body> tag. I also don't use the "With" block, as I need to set

certain

> parameters based on certain conditions and the .CommandText calls one of 2
> procedures based on a condition--however, I haven't incorprated that

portion

> into my code, yet. Would the fact that the asp is not all grouped

together

> be causing a problem?
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:uolwvyv9DHA.2324@tk2msftngp13.phx.gbl...

> > newbie wrote:

> > > Friendly messages were already on, but didn't show, for some reason,

> >
> > No, I wanted you to turn them off :-)
> >
> >

> > > until I moved the SET NOCOUNT ON to he top of the procedure, as you
> > > suggested. Then I discovered, I forgot to give myself "execute"
> > > permission on my new test query. Now, I ran it, and it did insert
> > > the records, correctly but the results came back with the following
> > > (not the ODBC Syntax is still showing). I don't have direct access to
> > > the server to find out the version of MDAC. I do know it is a
> > > Windows 2000 box. I added the line you gave me to our ADOVBS.INC
> > > file, but it still produces the same results. Now I'll just have to
> > > compare that procedure to my other code for results. Thanks.
> > >
> > > First: t1
> > > ErrorCode: 0
> > > Proc: { ? = call procInsertGANetTest2(?, ?) }
> > > OrderID: 116745

> >
> > Aren't these the correct results? There does not appear to be an error

....

> > or is that your point?
> >
> >
> > --
> > 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.
> >
> >

>
>

[Bob Barrows [MVP]]

newbie wrote:

> Nevermind--I didn't realize I had to set up the parameters in
> order--I had the return value set up towards the end.
>

That's why I ALWAYS use the code generator ... ;-)

--
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.