SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 works in QA but not in ASP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pat Shaw
Starting Member

14 Posts

Posted - 09/23/2006 :  05:49:28  Show Profile  Reply with Quote
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
17642 Posts

Posted - 09/23/2006 :  06:09:26  Show Profile  Reply with Quote

Update b
SET    Status = 'Accepted'
FROM   Bookings b INNER JOIN dbo.CSVTable(@ID) i
ON     b.BookingID = i.numberval

CSVTable can be found in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable



KH

Go to Top of Page

Pat Shaw
Starting Member

14 Posts

Posted - 09/23/2006 :  06:22:28  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 09/23/2006 :  06:36:27  Show Profile  Reply with Quote
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

Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 09/23/2006 :  07:43:19  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/23/2006 :  11:30:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/23/2006 :  12:45:41  Show Profile  Reply with Quote
"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
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 09/23/2006 :  12:57:30  Show Profile  Reply with Quote
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
Go to Top of Page

Pat Shaw
Starting Member

14 Posts

Posted - 09/23/2006 :  14:10:44  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000