Works in Query Analyser but not in asp

Report · Sep 23, 2006

I have an asp page that passes between 1 and 20 ID's in a string to another
asp page where an Update is triggered. The string looks like:
10023567, 1000345, 90885543211
or it could be just one ID like:
27736458

My update stored procedure is:

Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN (@ID)

@iD is the string mentioned above. If there is only 1 ID in the string then
the Update is performed but if there is more than 1 ID I get "cannot convert
Varchar to Int" error.

If I take the string contents and use them in Query Analyser eg.

Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN (10023567, 1000345, 90885543211)

it works fine and updates the correct rows, so why do I get the error when
attempting it in an asp page?

Report · Sep 25, 2006

In situations like these, I use a Response.write() statement to output the
SQL String to the page so I can see any possible errors.

Have you tried that?

Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
SatNav stuff: http://robgt.com/satnav

Report · Sep 25, 2006

In a stored procedure, @ID is a parameter, not plain text that gets added to
your query. Of course copy/pasting the list into QA would work because that
IS plain text getting added to the query. If you'd defined a parameter in
QA, it would also have failed.

DECLARE @ID INT
SET @ID = '1, 2, 3, 4' --this will fail

You can write the update for a single ID, then execute in a loop
Set up your command object
For Each ID in IDList
Command.Parameters("@ID").Value = ID
Command.Execute ,,128
Next

Or you could use one of the array/list techniques here:
http://www.sommarskog.se/arrays-in-sql.html

"SIMON" <simon@home.com> wrote in message
news:ef301q$8il$1@forums.macromedia.com...
>I have an asp page that passes between 1 and 20 ID's in a string to another
>asp page where an Update is triggered. The string looks like:
> 10023567, 1000345, 90885543211
> or it could be just one ID like:
> 27736458
>
> My update stored procedure is:
>
> Update Bookings
> SET Status = 'Accepted'
> WHERE BookingID IN (@ID)
>
> @ID is the string mentioned above. If there is only 1 ID in the string
> then the Update is performed but if there is more than 1 ID I get "cannot
> convert Varchar to Int" error.
>
> If I take the string contents and use them in Query Analyser eg.
>
> Update Bookings
> SET Status = 'Accepted'
> WHERE BookingID IN (10023567, 1000345, 90885543211)
>
> it works fine and updates the correct rows, so why do I get the error when
> attempting it in an asp page?
>