|
| |
|
|
siask
Posts: 8
Joined: 4/21/2009
Status: offline
 |
IN - sql? - 7/1/2009 3:05:23
I'm struggling....
Have a field with many numbers listed.... ie: 4280, 3456, 3788.... and so on...
I was under the impression that a simple IN will pick that require number from list and display...
I've used this very simple custom code
SELECT* FROM CamoBasket
WHERE CN_No IN ('::CN_No::')
CN_no is the field name
CamoBasket the Table name....
I can't change from Text to number as some manufacturers may use text characters.
Now this does work if the required number is the only number in the field.... as soon as it notes other numbers listed (comma separated) it doesn't work!
Wha'd ya reckon?...
|
|
|
|
TexasWebDevelopers
Posts: 722
Joined: 2/22/2002
From: Dallas, TX
Status: offline
|
RE: IN - sql? - 7/1/2009 10:28:26
Is there an error message and if so what is it?
How are you handling the comma spearators and the single quotes?
This will work:
WHERE CN_No IN ('123','124','125','126')
but his, with the trailing comma after 126, will not:
WHERE CN_No IN ('123','124','125','126',)
and this will not:
WHERE CN_No IN ('123,124,125,126')
Note that all the variables have to have single quotes around them.
You might want to response.write(your_sql) to see exactly what your sql statement looks like.
We use a function to strip out the trailing comma after adding in the single quotes something like this:
<script language='jscript' runat='server'>
function commaKiller(x){ return x.replace(/,*$/,''); };
</script>
<%
str = str & "'" & Rs("CN_No") & "'," ' this bit adds single quotes and commas
newCN_No=commaKiller(str) ' this function call strips out the last comma in the array
%>
sql = "SELECT * FROM [table-name] WHERE CN_No IN (" & newCN_No & ");"
_____________________________
Follow us on TWITTER
| |
|
|
|
siask
Posts: 8
Joined: 4/21/2009
Status: offline
|
RE: IN - sql? - 7/1/2009 11:22:23
Thanks for the response TWD...
I'm not getting any error messages at the moment. It's just not finding the records that have more than one set of numbers in the field....
for example... if I select 4280 from a dropdown, i get all the records where 4280 is the only info in the CN_No field..... but i know one record has '4280','1111' within it....
I've been scratching my head (and other bits) for the last 2 days trying to figure it out....
I can't really use CONTAINS or LIKE as we have 820 as a CN_No as well.... but it must not be displayed due to data issues within the company.... only the records linked to 4280....
I have thought about a different field for each number, which would be easy..... but i know of one client that may have over 80 CN_no's to add.... so, comma separated list is the only way i can think to go?
This will only ever have one or two end users within our company, so its easy to instruct them to enter this data in a specific format... no real need to make it idiot-proof (famous last words!)
Ta muchly Si
< Message edited by siask -- 7/1/2009 11:36:56 >
| |
|
|
|
siask
Posts: 8
Joined: 4/21/2009
Status: offline
|
RE: IN - sql? - 7/1/2009 14:10:25
Yep....
'If from a form you could separate the string at the commas and update the data as separate fields "on the fly"... there are a lot of ways to do this.
The data is selected from a DRW dropdown on a separate page... i've then set it up to post to a final page that consists of 4 different DRW's, on of them being this problem one.... the other 3 all need the same info (CN_no)....
I'm a newbee, if you hadn't guessed... I think the problem may lay with the ('::CN_No::') ... as i understand.... if i select 4820 from the DRW dropdown (using this because it'll only show the CN_No's already in the DB and nothing else)....
Anyway, select 4820 and the 'on-the-fly- statement posted is bascially
SELECT * FROM CamoBasket WHERE CN_No IN (4820) ?.....
If this is right, i can see that the ' ' are missing.... I cant add them as the other 3 DRW won't work...
Sorry for being thick... and thanks for your time to answer...
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 