| Author |
 Topic  |
|
|
lancepr
New Member
73 Posts |
 Posted - 05 March 2002 : 17:48:08
|
I am trying to select items out of my DB based on a querystring
in my DB I have an item like this
Name Description Category
test test 1,2,3,4
test2 test2 1,3,4
test3 test3 3,4
now I want to select all records that category = 3
Select name, description
from tbl_test
WHERE "&querystring&" IN (category)
here is my error
Syntax error converting the varchar value '26, 9, 15, 27 ' to a column of data type int.
How can I store/convert these CSV values in a type int?
Lance
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 05 March 2002 : 18:00:43
|
quote:
Syntax error converting the varchar value '26, 9, 15, 27 ' to a column of data type int.
Where do these values come from, QueryString?
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
 |
|
|
lancepr
New Member
73 Posts |
Posted - 05 March 2002 : 18:14:30
|
No they are the actual values in My DB, sorry for the confusion
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 05 March 2002 : 18:18:26
|
Is that ONE table you have in your database? If it is, I would suggest using two tables:
Table 1 fields: ID, Name, Description
Table 2 fields: Table1ID, Category
Then your example would look like:
ID Name Description
1 test test
2 test2 test2
3 test3 test3
Table1ID Category
1 1
1 2
1 3
1 4
2 1
2 3
2 4
3 3
3 4
It would be much easier to do what you want to do.
Nikkol |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 05 March 2002 : 18:42:05
|
You could, in theory, store the values like you are and split them up into an array.
catagoriesArr = split(rs("Category"), " ,")
Then you would have to loop through the array looking for the value 3.
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource
Edited by - Nathan on 05 March 2002 18:42:26 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 05 March 2002 : 18:42:58
|
lancepr
I would strongly recommend you to follow Nikkol's advice. The current structure of your DB violates the most elementary rule of database design, which is that each record (row) should have a single value in each column.
If you follow the structure recomended by Nikkol, you'll see that each Category has a single column value for each row. This means her recommended design follows this very basic DB design rule. Not following it will mean big difficulties to do whatever it is that you may want to do with your data (if you ever manage to do it at all).
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 05 March 2002 : 18:58:33
|
quote:
Not following it will mean big difficulties to do whatever it is that you may want to do with your data (if you ever manage to do it at all).
I think what ruirib is saying here is that say for example, you decide that category 3 no longer exists. You would have to do some pretty fancy footwork with your database to remove all of the 3s. If instead you have a separate table for category designation, you just perform a DELETE where the category is 3. Or better yet, you establish a cascading-delete integrity rule and just delete category 3 from yet another table (Category table) and voila all instances of category 3 magically disappear. 
Nikkol |
|
|
| |
Topic |
|
Topic Locked
