Returning value using Stored Procedure and ASP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Drew - 28 Feb 2008 14:53 GMT

I have been using SP's for some time now in my ASP applications, but have
hit a wall when trying to return the identity value from a SP. The SP looks
like this,

CREATE PROCEDURE InsertTrip
@TripDate smalldatetime,
@StartTime datetime,
@EndTime datetime,
@Duration float,
@TripPlace varchar(50),
@TripPurpose varchar(50),
@ChoicesGiven text
AS
INSERT INTO Trips
(TripDate,StartTime,EndTime,Duration,TripPlace,TripPurpose,ChoicesGiven)
VALUES
(@TripDate,@StartTime,@EndTime,@Duration,@TripPlace,@TripPurpose,@ChoicesGiven)
SELECT NEWID = SCOPE_IDENTITY()
GO

And the ASP looks like this,

dim varTripID, varRegNo, varTripDate, varStartTime, varEndTime,
varDuration, varTripPlace, varTripPurpose, varChoicesGiven

varTripDate = Request.Form("TripDate")
varStartTime = Request.Form("StartTime")
varEndTime = Request.Form("EndTime")
varDuration = Request.Form("Duration")
varTripPlace = Request.Form("TripPlace")
varTripPurpose = Request.Form("TripPurpose")
varChoicesGiven = Request.Form("ChoicesGiven")

'Insert into Trip table
conn.InsertTrip varTripDate, varStartTime, varEndTime, varDuration,
varTripPlace, varTripPurpose, varChoicesGiven, rs

varTripID = rs(0)

I am getting the following error, "Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another". But when I
remove the rs from the SP call code it works fine, although it does not
return the value.

How can I fix this?

Thanks,
Drew

Reply to this Message

Bob Barrows [MVP] - 28 Feb 2008 15:00 GMT

> I have been using SP's for some time now in my ASP applications, but
> have hit a wall when trying to return the identity value from a SP.
[quoted text clipped - 9 lines]
> @ChoicesGiven text
> AS

Your problem should be solved by adding this line right here:
SET NOCOUNT ON

Signature

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.

Reply to this Message

Drew - 28 Feb 2008 15:31 GMT

DOH! Great suggestion, but it didn't help out... I am still getting the
"Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another. " error. I have Response.Write the variables and
they are all good, so it must be that rs at the end of the line... will I
just need to resort to the old way of doing it, by building the query and
executing it like this?

set conn = CreateObject("ADODB.Connection")
conn.open MM_CliEvent_STRING
set rs = conn.execute(sql)

I really like the new way to execute SP's that you showed me (some time
ago)... it makes my code a little cleaner and easier to maintain. As a side
note, can you tell me what the conn.InsertTrip method is called? I have had
problems finding information about it...

Thanks,
Drew

>> I have been using SP's for some time now in my ASP applications, but
>> have hit a wall when trying to return the identity value from a SP.
[quoted text clipped - 12 lines]
> Your problem should be solved by adding this line right here:
> SET NOCOUNT ON

Reply to this Message

Bob Barrows [MVP] - 28 Feb 2008 15:46 GMT

OK, back to the asp code:

> dim varTripID, varRegNo, varTripDate, varStartTime, varEndTime,
> varDuration, varTripPlace, varTripPurpose, varChoicesGiven
[quoted text clipped - 6 lines]
> varTripPurpose = Request.Form("TripPurpose")
> varChoicesGiven = Request.Form("ChoicesGiven")

Ah! Since the procedure is being treated as a method of the connection
object, the datatypes of the parameters need to be correct.You have to
convert these to the proper datatypes:

For illustration:
varTripDate = CDate(Request.Form("TripDate"))

I would not trust the user input unless a calendar control was used ...
and even then ...
A more robust technique would be to get day, month and year values from
the form and use DateSerial to cast them as a date.

The same goes for the time values: are users entering them in military
clock format? Or by using AM/PM? And can you count on the users being
consistent? If you use three dropdowns, you can control the format
yourself.

As for the conn.InsertTrip technique, you won't find much about this
technique, which I call the "stored-procedure-as-connection-method"
technique. There is a brief mention in the ADO Documentation - see the
last paragraph of the Connection Object topic.

Signature

Reply to this Message

Drew - 28 Feb 2008 17:27 GMT

Man, do I feel stupid this morning!

After trying to get them into the right datatypes and that not working,
building a new table with all varchar datatypes and that not working... I
finally found my problem... it is really hard to open a recordset when you
haven't created it... There is a RS above this code, and after it is run I
closed it... just lame-brain, stupid mistakes... they'll getcha everytime!

Now it works without a hitch... now I need to go back and undo all of the
stuff I did to try to fix it!

Thanks for your help!
Drew

> OK, back to the asp code:
>> dim varTripID, varRegNo, varTripDate, varStartTime, varEndTime,
[quoted text clipped - 29 lines]
> technique. There is a brief mention in the ADO Documentation - see the
> last paragraph of the Connection Object topic.

Reply to this Message

Bob Barrows [MVP] - 28 Feb 2008 17:35 GMT

Ah-ha!
Well, don't undo everything! it's important to be cognizant of passing
the correct datatypes!

> Man, do I feel stupid this morning!
>
[quoted text clipped - 50 lines]
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

Signature

Reply to this Message

Drew - 28 Feb 2008 17:55 GMT

I still have those in there, I was talking about undoing the new table that
I built, new procedures, different pages... I will keep the CDate() and
even went as far as using CStr on my strings... there will be javascript
validation, and since this app will be run on an intranet, and we have
standard browsers and settings, I feel that will take care of the majority
of the problems.

Thanks,
Drew

> Ah-ha!
> Well, don't undo everything! it's important to be cognizant of passing
[quoted text clipped - 54 lines]
>>> header is my spam trap, so I don't check it very often. You will get
>>> a quicker response by posting to the newsgroup.

Reply to this Message

Brynn - 28 Feb 2008 16:20 GMT

If I am not mistaken ... the following line has one too many
parameters after it ... that being the rs. I count 7 in the stored
procedure I think ... and 8 in the ASP.

dim sql
sql = "InsertTrip "

sql = sql & "'" & Request.Form("TripDate") & "', "
sql = sql & "'" & Request.Form("StartTime") & "', "
sql = sql & "'" & Request.Form("EndTime") & "', "
sql = sql & Request.Form("Duration") & ", "
sql = sql & "'" & Request.Form("TripPlace") & "', "
sql = sql & "'" & Request.Form("TripPurpose") & "', "
sql = sql & "'" & Request.Form("ChoicesGiven") & "'"

rs = conn.execute sql;
varTripID = rs(0)

Reply to this Message

Bob Barrows [MVP] - 28 Feb 2008 17:37 GMT

> If I am not mistaken ... the following line has one too many
> parameters after it ... that being the rs. I count 7 in the stored
> procedure I think ... and 8 in the ASP.

Right. The "stored-procedure-as-connection-method" technique allows an
extra optional argument to pass a recordset object to receive results
returned from the execution of the procedure. So the first 7 values are
being passed to the stored procedure, and the results from the procedure
are being put into the recordset object in the eighth argument.

Signature

Reply to this Message

Brynn - 28 Feb 2008 16:26 GMT

Oh, I also just built the "sql" string ... wrapping the dates and
varchars and text in single quotes ... actually ... this adds changing
single quotes into 2 single quotes to not screw up the query.

dim sql
sql = "InsertTrip "

sql = sql & "'" & Request.Form("TripDate") & "', "
sql = sql & "'" & Request.Form("StartTime") & "', "
sql = sql & "'" & Request.Form("EndTime") & "', "
sql = sql & Request.Form("Duration") & ", "
sql = sql & "'" & replace(Request.Form("TripPlace"), "'", "''") & "',
"
sql = sql & "'" & replace(Request.Form("TripPurpose"), "'", "''") &
"', "
sql = sql & "'" & replace(Request.Form("ChoicesGiven"), "'", "''") &
"'"

rs = conn.execute sql;
varTripID = rs(0)

Reply to this Message

Bob Barrows [MVP] - 28 Feb 2008 17:24 GMT

> Oh, I also just built the "sql" string ... wrapping the dates and
> varchars and text in single quotes ... actually ... this adds changing
[quoted text clipped - 13 lines]
> sql = sql & "'" & replace(Request.Form("ChoicesGiven"), "'", "''") &
> "'"

That's what we want to avoid doing: it's an open invitation to hackers.

Signature

Reply to this Message

Drew - 28 Feb 2008 18:00 GMT

Brynn,

Thanks for your response... it ended up being a stupid mistake... not
creating the recordset. I have been working with Bob's
"stored-procedure-as-connection-method" technique for a few months now, and
it has made my code more readable, and there is far less code this way. I
just love this technique!

Thanks,
Drew

> Oh, I also just built the "sql" string ... wrapping the dates and
> varchars and text in single quotes ... actually ... this adds changing
[quoted text clipped - 16 lines]
> rs = conn.execute sql;
> varTripID = rs(0)

Reply to this Message

Webmaster Forum / ASP / Database Access / February 2008

Returning value using Stored Procedure and ASP