works in QA but not in ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

works in QA but not in ASP

Author

Topic

Pat Shaw
Starting Member

14 Posts

Posted - 2006-09-23 : 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)

17689 Posts

Posted - 2006-09-23 : 06:09:26

[code]
Update b
SET Status = 'Accepted'
FROM Bookings b INNER JOIN dbo.CSVTable(@ID) i
ON b.BookingID = i.numberval
[/code]
CSVTable can be found in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

Pat Shaw
Starting Member

14 Posts

Posted - 2006-09-23 : 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)

17689 Posts

Posted - 2006-09-23 : 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.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-23 : 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

22864 Posts

Posted - 2006-09-23 : 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

22859 Posts

Posted - 2006-09-23 : 12:45:41

"I read the link and I'm not sure I understand the theory"

These may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-23 : 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 - 2006-09-23 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources