| Author |
 Topic  |
|
|
Pat Shaw
Starting Member
14 Posts |
 Posted - 09/23/2006 : 05:49:28
|
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?
Pat.
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
|
|
Pat Shaw
Starting Member
14 Posts |
Posted - 09/23/2006 : 06:22:28
|
Thanks khtan, I read the link and I'm not sure I understand the theory. Is there no other (simpler) way of doing this? Also, I'm not sure if I will have access to UDF's through the web host.
Why does it work in QA but not in my asp page? |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 09/23/2006 : 06:36:27
|
quote:
@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.
your @ID must be string and column BookingID is an integer column. When @ID string only contain 1 ID, SQL Server will be able to implicitly convert from string to integer. When there is more than 1 ID like '10023567, 1000345, 90885543211', SQL Server will not be able to convert to integer.
quote:
it works fine and updates the correct rows, so why do I get the error when attempting it in an asp page?
quote:
Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN (10023567, 1000345, 90885543211)
The above is different from this
quote:
Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN (@ID)
when you replace @ID with the input string, it is actually as below. Which is different as what you have in QA.
quote:
Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN ('10023567, 1000345, 90885543211')
What you have in red is actually string which SQL Server is having problem converting it to integer.
KH
|
|
|
|
afrika
Flowing Fount of Yak Knowledge
Nigeria
2702 Posts |
Posted - 09/23/2006 : 07:43:19
|
Why not paste your ASP code here. Lets have a look at it.
You can use the isnumeric function in ASP and cint to check if the value is numeric and convert them appropriately
Afrika |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 09/23/2006 : 11:30:48
|
ISNUMERIC is not always reliable
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
You need to use one of these
Exec('Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN ('+@ID+')')
or
Update Bookings
SET Status = 'Accepted'
WHERE ',%'+@ID+'%,' like '%,'+cast(BookingID as varchar(20))+'%,'
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
afrika
Flowing Fount of Yak Knowledge
Nigeria
2702 Posts |
Posted - 09/23/2006 : 12:57:30
|
quote:
Originally posted by madhivanan
ISNUMERIC is not always reliable
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
You need to use one of these
Exec('Update Bookings
SET Status = 'Accepted'
WHERE BookingID IN ('+@ID+')')
or
Update Bookings
SET Status = 'Accepted'
WHERE ',%'+@ID+'%,' like '%,'+cast(BookingID as varchar(20))+'%,'
Madhivanan
Failing to plan is Planning to fail
Maddy, thanks for the info |
|
|
|
Pat Shaw
Starting Member
14 Posts |
Posted - 09/23/2006 : 14:10:44
|
Sorry guys, I've been out all day and just got back in. Thanks for all your replys, I am going to try them out now and will let you know how I go on.
Pat. |
|
|
| |
Topic |
|
works in QA but not in ASP
