SQL Stored Proc - ASP Page - Return Value

[Scott]

I have a SQL Stored Procedure that is I am passing several parameters to and
doing an insert into a table. The proc is doing a few other things and one
of the process is to return a confirmation number back to the the ASP page
that called the Stored Proc. How ever the DBA that created the stored proc
for me does not want to use an OUTPUT Parameter, but rather, as you can see
at the bottom of the proc, he is setting the return parameter like a result
set. My problem is, I am not sure how to get that "result set" back can you
help me. Attached is a copy of the head and tail part of the stored proc
and the part of the ASP code where I call the procedure in my site.
Everything within the procedure is working fine, I just need to know how to
get what is being returned in the "confirmation" parameter. I am not sure
if I pass it over as blank and it returns what I need or what? Hope this
makes sense.

Thanks in advance for all the help.

STORED PROCEDURE:

create proc p_eft_InsertPayment
@AgentID int,
@UserID int,
@AccountNumber varchar(50),
@Amount Float,
@PaymentType varchar(25) = null,
@CheckNbr int = null
as
set nocount on
.....
.....
....
....
set nocount off
commit tran
select @ConfirmationNbr - ***** this is what I need *********
GO


ASP CODE:
cmdpay.ActiveConnection = connpay
cmdpay.CommandText = "SYSTEM..p_eft_InsertPayment"
cmdpay.CommandType = adCMDStoredProc
cmdpay.Parameters.Append (cmdpay.CreateParameter("RetVal", adInteger,
adParamReturnValue))
cmdpay.Parameters.Append (cmdpay.CreateParameter("@AgentID", adInteger,
adParamInput,, Session("UserAgentID")))
cmdpay.Parameters.Append (cmdpay.CreateParameter("@UserID", adInteger,
adParamInput,, Session("UserID")))
cmdpay.Parameters.Append (cmdpay.CreateParameter("@AccountNumber",
adVarChar, adParamInput, 50, Session("UserPaymentAcctNo")))
cmdpay.Parameters.Append (cmdpay.CreateParameter("@Amount", adDouble,
adParamInput, 10, Request.Form("txtPaymentAmount")))
cmdpay.Parameters.Append (cmdpay.CreateParameter("@PaymentType",
adVarChar, adParamInput, 25, Request.Form("drpPaymentType")))
cmdpay.Parameters.Append (cmdpay.CreateParameter("@CheckNbr", adInteger,
adParamInput,, Request.Form("txtCheckNumber")))

 

[Bob Barrows [MVP]]

I have a SQL Stored Procedure that is I am passing several parameters
to and doing an insert into a table. The proc is doing a few other
things and one of the process is to return a confirmation number back
to the the ASP page that called the Stored Proc.

Among those other things, is it returning another resultset?

How ever the DBA
that created the stored proc for me does not want to use an OUTPUT
Parameter, but rather, as you can see at the bottom of the proc, he
is setting the return parameter like a result set.

This IS inefficient - why bring back a resultset for a single value? Oh well
...

My problem is, I
am not sure how to get that "result set" back can you help me.
Attached is a copy of the head and tail part of the stored proc and
the part of the ASP code where I call the procedure in my site.
Everything within the procedure is working fine, I just need to know
how to get what is being returned in the "confirmation" parameter.

well, there is no confirmation parameter, so get that out of your mind right
now. This Select statement is returning a resultset that will need to be
read by a recordset. If you are getting multiple recordsets from the stored
procedure, then you need to use the NextRecordset method of the recordset
object to load the recordset containing the resultset containing your
confirmation number.

Since you have no output parameters and you aren't interested in the Return
value, you can run this procedure without a command object, like this:

set rs=createobject("adodb.recordset")
connpay.DefaultDatabase= "SYSTEM"
connpay.p_eft_InsertPayment Session("UserAgentID"), _
Session("UserID"), Session("UserPaymentAcctNo"), _
Request.Form("txtPaymentAmount"), _
Request.Form("drpPaymentType"), _
Request.Form("txtCheckNumber"), rs

'if your procedure returns multiple resultsets, then process
'the first resultset, or create a clone, like this:

set rsInitial = rs.clone

'then get the next recordset:
set rs=rs.NextRecordset
confirmation=rs(0).value

HTH,
Bob Barrows

 

[Scott]

Thanks for the help - looks like it may be working accept for one problem -
the txtPaymentAmount field is a FLOAT data type and its returning a "Error
converting data type char to float" error - how would I tell it what data
type to pass? The old way I used adDouble.

Thanks again for your help

 

[Scott]

Never mind that stupid post - its way to early, I have it working, but its
not returning the result set. here is what I have in my ASP code to return
and at least write the return

connpay.DefaultDatabase= "SYSTEM"
connpay.p_eft_InsertPayment Session("UserAgentID"), _
Session("UserID"), Session("UserPaymentAcctNo"), _
ccur(Request.Form("txtPaymentAmount")), _
Request.Form("drpPaymentType"), _
Request.Form("txtCheckNumber"), rspay

If Err.Number <> 0 then
'response.Write("ERR1: " & sErrDesc)
sErrCode = Err.Number
sErrDesc = Err.Description
End If

'//SET THE REDCORDSET OBJECT

If rspay.BOF And rspay.EOF Then
response.Write("ERR3: " & sErrDesc)
sErrCode = Err.Number
sErrDesc = Err.Description
Else
response.Write("ConfNo " & cstr(rspay("ConfirmationNbr")))
End If

connpay.Close
Set rspay = Nothing
Set cmdpay = Nothing
Set connpay = Nothing

 

[Bob Barrows]

Never mind that stupid post - its way to early, I have it working,
but its not returning the result set.

Are you getting an error message?

here is what I have in my ASP
code to return and at least write the return

connpay.DefaultDatabase= "SYSTEM"

You need to instantiate the recordset variable before executing the
statement that runs the stored procedure

set rspay = createobject("adodb.recordset")

connpay.p_eft_InsertPayment Session("UserAgentID"), _
Session("UserID"), Session("UserPaymentAcctNo"), _
ccur(Request.Form("txtPaymentAmount")), _
Request.Form("drpPaymentType"), _
Request.Form("txtCheckNumber"), rspay

Bob Barrows

 

[Scott]

No error message - and yes I am instantiate the recordset :

Set connpay = Server.CreateObject("ADODB.Connection")
Set rspay = Server.CreateObject("ADODB.Recordset")
Set cmdpay = Server.CreateObject("ADODB.Command")

 

[Bob Barrows]

No error message - and yes I am instantiate the recordset :

Set connpay = Server.CreateObject("ADODB.Connection")
Set rspay = Server.CreateObject("ADODB.Recordset")
Set cmdpay = Server.CreateObject("ADODB.Command")

That last one is not needed.


So the stored procedure executes but you do not get a resultset back?

Oh,wait! I see! It's this line:

response.Write("ConfNo " & cstr(rspay("ConfirmationNbr")))

I am surprised that this line did not raise an "item not found in this
collection" error. You're using On Error Resume Next, right? I bet if you
checked the Err object immediately after this line, you would see the error.

Explanation:

Your stored procedure uses this query to return the value:
select @ConfirmationNbr

It is not using a column alias so the column in the resultset being returned
does not have the name you expect it to have. To see what I mean, open Query
Analyzer and run this script:

declare @return int
set @return=50
select @return

When you see the results, do you see a column name above the 50? That's
because a column name was not specified, like this:

declare @return int
set @return=50
select @return [return] --the AS keyword is optional

Now when it's run, you will see a column heading.

There is no need to modify the stored procedure: instead of using the name
of the field to reference it, use its index number as I showed you in the
example I gave you (there is no need to pur parentheses around the
response.write argument):

response.Write "ConfNo " & cstr(rspay(0))

Using the index number is much more efficient than using the name of the
field, anyways.
If you are curious about what the name of the field is:
response.Write """" & rspay(0).name & """"


Bob Barrows

 

[Scott]

You are the MAN - Santa will be very good to you this year - thank you so
much - you are a life saver. Take the rest of the day off. Works great.
Not only did you make it work, you explained yourself and helped me learn.
There are few (let me rephrase that) none on here that I have worked with
that do that. Thanks again for your help.

Scott

No error message - and yes I am instantiate the recordset :

Set connpay = Server.CreateObject("ADODB.Connection")
Set rspay = Server.CreateObject("ADODB.Recordset")
Set cmdpay = Server.CreateObject("ADODB.Command")

That last one is not needed.


So the stored procedure executes but you do not get a resultset back?

Oh,wait! I see! It's this line:

response.Write("ConfNo " & cstr(rspay("ConfirmationNbr")))

I am surprised that this line did not raise an "item not found in this
collection" error. You're using On Error Resume Next, right? I bet if you
checked the Err object immediately after this line, you would see the error.

Explanation:

Your stored procedure uses this query to return the value:
select @ConfirmationNbr

It is not using a column alias so the column in the resultset being returned
does not have the name you expect it to have. To see what I mean, open Query
Analyzer and run this script:

declare @return int
set @return=50
select @return

When you see the results, do you see a column name above the 50? That's
because a column name was not specified, like this:

declare @return int
set @return=50
select @return [return] --the AS keyword is optional

Now when it's run, you will see a column heading.

There is no need to modify the stored procedure: instead of using the name
of the field to reference it, use its index number as I showed you in the
example I gave you (there is no need to pur parentheses around the
response.write argument):

response.Write "ConfNo " & cstr(rspay(0))

Using the index number is much more efficient than using the name of the
field, anyways.
If you are curious about what the name of the field is:
response.Write """" & rspay(0).name & """"


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"

 

[Aaron Bertrand [MVP]]

There are few (let me rephrase that) none on here that I have worked with

that do that.

While complimenting one, you might want to try to resist such an obnoxious
jab at the rest of us.

Speaking only for myself, I am here for the sole purpose of helping others
learn. So I find your statement quite offensive, frankly.

 

[Bob Barrows]

While complimenting one, you might want to try to resist such an
obnoxious jab at the rest of us.

Speaking only for myself, I am here for the sole purpose of helping
others learn. So I find your statement quite offensive, frankly.

Oh geez, I didn't read past the first couple of sentences so I missed that!
If I had seen it I would have commented on it.

Scott,
There are many helpful people in these groups. If it seemed like I was more
helpful to you today than others have been, it was because I had more time
on my hands today than usual. On a normal day, I might have spent less time
with the explanations. The same is true for everyone else: when we have
time, we take the time to explain things as thoroughly as we can. When we
don't, they take short cuts such as replying with links to articles or posts
containing the answers to the questions.

Bob Barrows

 

[Roland Hall]

"Bob Barrows" wrote in message : Aaron Bertrand [MVP] wrote:
: >> There are few (let me rephrase that) none on here that I have worked
: >> with that do that.
: >
: > While complimenting one, you might want to try to resist such an
: > obnoxious jab at the rest of us.
: >
: > Speaking only for myself, I am here for the sole purpose of helping
: > others learn. So I find your statement quite offensive, frankly.
:
: Oh geez, I didn't read past the first couple of sentences so I missed
that!
: If I had seen it I would have commented on it.
:
: Scott,
: There are many helpful people in these groups. If it seemed like I was
more
: helpful to you today than others have been, it was because I had more time
: on my hands today than usual. On a normal day, I might have spent less
time
: with the explanations. The same is true for everyone else: when we have
: time, we take the time to explain things as thoroughly as we can. When we
: don't, they take short cuts such as replying with links to articles or
posts
: containing the answers to the questions.

I'm biting my tongue here but let me just say I wasn't offended. Scott did
clarify he was only referring to people on here that he had worked with. I
saw nothing negative about his comment nor do I believe it was intended to
be negative.

"Not only did you make it work, you explained yourself and helped me learn.
There are few (let me rephrase that) none on here that I have worked with
that do that."

1. He [Bob] provided a very detailed solution while taking the time to
educate Scott on the issue.
2. It was explained well enough that it made it easy for Scott to not only
understand but to also educated him in the process.

He didn't say others were not helpful, just none have ever been AS helpful
as Bob was today. I saw Bob go out of his way to help someone and I saw
that person make it a point to let him how thankful he really was. Bob even
stated this was not normal. Surely it is not offensive to praise someone,
even when compared to help you have received in the past.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

 

[Scott]

Man - see what I mean when I made the comment - Like I said of the people I
worked with on here, there have been few that have gone out of there way
that have been as helpful as Bob has. I am sure Aaron may do the same,
but to my knowledge, I have never had the experience of interacting with
Aaron. Anyway - Thank you again Bob for all your time and effort.

"Bob Barrows" wrote in message : Aaron Bertrand [MVP] wrote:
: >> There are few (let me rephrase that) none on here that I have worked
: >> with that do that.
: >
: > While complimenting one, you might want to try to resist such an
: > obnoxious jab at the rest of us.
: >
: > Speaking only for myself, I am here for the sole purpose of helping
: > others learn. So I find your statement quite offensive, frankly.
:
: Oh geez, I didn't read past the first couple of sentences so I missed
that!
: If I had seen it I would have commented on it.
:
: Scott,
: There are many helpful people in these groups. If it seemed like I was
more
: helpful to you today than others have been, it was because I had more time
: on my hands today than usual. On a normal day, I might have spent less
time
: with the explanations. The same is true for everyone else: when we have
: time, we take the time to explain things as thoroughly as we can. When we
: don't, they take short cuts such as replying with links to articles or
posts
: containing the answers to the questions.

I'm biting my tongue here but let me just say I wasn't offended. Scott did
clarify he was only referring to people on here that he had worked with. I
saw nothing negative about his comment nor do I believe it was intended to
be negative.

"Not only did you make it work, you explained yourself and helped me learn.
There are few (let me rephrase that) none on here that I have worked with
that do that."

1. He [Bob] provided a very detailed solution while taking the time to
educate Scott on the issue.
2. It was explained well enough that it made it easy for Scott to not only
understand but to also educated him in the process.

He didn't say others were not helpful, just none have ever been AS helpful
as Bob was today. I saw Bob go out of his way to help someone and I saw
that person make it a point to let him how thankful he really was. Bob even
stated this was not normal. Surely it is not offensive to praise someone,
even when compared to help you have received in the past.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp