array problem

[ltreur]

Hello, I'm having a problem with an array in my website.

I have a database with two table ( produkten, indicatie)
in table produkten is a field named indicatie. this field contains an array. all these array need to be connected to indicatiID in table indicatie.

normaly I use

<%
Dim Rsprodukt
Dim Rsprodukt_cmd
Dim Rsprodukt_numRows
 
Set Rsprodukt_cmd = Server.CreateObject ("ADODB.Command")
Rsprodukt_cmd.ActiveConnection = MM_Conavo_STRING
Rsprodukt_cmd.CommandText = "SELECT * FROM produkten, indicatie where indicatieID like indicatie" 
Rsprodukt_cmd.Prepared = true
 
Set Rsprodukt = Rsprodukt_cmd.Execute
Rsprodukt_numRows = 0
%>
 

but because produkten.indicatie is an array, this would not work..

can anyone help me to fix this problem?

[jhardman]

I'm having trouble visualizing what are in each of these columns. Could you please give an example of the contents of these fields? Specifically, what is in the field "produkten.indicatie"?

[ltreur]

This is the content of the fields
produkten
indicatie
38,3,33
3,33
37,41,11,26
2
14
20
3,13,37,39

indicatie
indicatieID indicatienaam
1 gewrichten
2 pezen
3 bloedsomloop
4 darmstelsel
5 luchtwegen
6 ontwormen
7 insectenbestrijding
8 haargroei
9 hoeven
10 ligamenten
11 vermoeidheid
12 recupertatie
13 conditie
14 krampen
15 rodebloedlichamen
16 weerstand
17 Banden
18 lever
19 nieren
20 stress
21 onrust
22 vochtbalans
23 neusbloeding
24 bloedzuivering
25 huid
26 zomereczeem
27 mok
28 litteken
29 schuren
30 zwellingen
31 peesschede
32 darmen
33 brandwonden
34 schimmels
35 schaafwonden
36 vacht
37 dracht
38 Artrose
39 eetlust
41 Spieren

[jhardman]

hmm. So is the produkten.indicatie column a text field of some kind (varchar etc)? In order to do what you are trying to do you will need to cast your integer to text and then use the "like" keyword to search the array field to see if it contains that text. The general form of a query containing "like" looks like this: "WHERE indicatie LIKE '%32%'", but even if you can get that to work (I've never tried it with a casting expression in the value you are searching), that's not a very good solution.

You may not like this, but I think the best solution would be to organize your db differently. it appears that you have a many to many relationship between indicatie and produkten (each produkten can correspond to multiple indicatie and the reverse is also true) in which case I would suggest adding a third table that relates the two - maybe titled produktenIndicatieMembers with fields "produkten" and "indicatie" and a row for every relationship. Does this make sense?

Jared

[ltreur]

Yes, it makes sense.
The problem is that I have an update page for produkten. And with an array I could update the produkten.indicatie at the same time with an multiple selection list. In case of a thirt table it is a lot more complexed. Maybe it is possible but I don't see how at this time.

But thank you for your time.