Trying to separate numbers with commas! 3

[Rexolio]

Sorry about the subject...didn't know a better way to describe it!

I have a database with 2 tables. One table contains different categories. The second table contains registered members info.

I have 45 categories and didn't want 45 fields in my members table. SO, in the member's table I have a field called "CategoryIDs". When someone has mulitple categories, they're listed in the field like " 4, 6, 10, 15,". I have the last number added with a comma behind it as well so that I'd have some type of separator.

What I need to do on my ASP is to have the members info displayed including the actual names of the categories.

I tried this, but does not work (I know I'm horrible at this stuff - I'm sure its a butchered way to do this!)...

<%
CatID = CategoryIDs

'BEGIN LISTING CATEGORIES
CatID = Replace(CatID,&quot; &quot;,&quot;&quot
stID = LCase(Trim(CatID))
aryID = Split(stID,&quot;,&quot
For i = 0 To UBound(aryID)

sID = aryID(i)

SQL=&quot;SELECT * FROM tblCategories WHERE CategoryID = &quot; & sID
Set List=cnSupport.execute(SQL)
do while not List.eof

Response.write List(&quot;Category&quot & &quot;, &quot;

List.movenext
loop
List.close

'FIND NEXT CATEGORY
Next

%>

which results in the following error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
list.asp, line 178

Line 178 is this line from above:
SQL=&quot;SELECT * FROM tblCategories WHERE CategoryID = &quot; & sID

can anyone straighten me out?

 

[JuanitaC]

Is it looping one too many times? If you have a comma after your last value (2,3,4,) then your array will contain 4 elements instead of 3 when you use split. Try this and see if it helps:

For i = 0 To UBound(aryID)-1

 

[Rexolio]

YOU ROCK!!!!!!!!!! THAT APPEARS TO HAVE WORKED!!!!

Many Thanks!!!

 

[RushiShroff]

Ok your application is working that is not the final solution.ASP Optimization is something to be known.

As you grow as a ASP developer, you should keep the habit of quality programming.And the way you are coding is not the right way.
You should tyr to concatenate the SQL statement and should execute it as only once.As many times you will fire conn.execute more it will consume resources on the server,also it will make application slower.

If you are interested,I can show you how to concatenate SQL strings. Tell me.

Rushi Shroff

 

[Rexolio]

Rushi,

that's why I come to this site...to learn!!! So yes, I'd love for you to show me how to concatenate the SQL statement!

Thanks,
Rex

 

[RushiShroff]

dim SQL
SQL=&quot;&quot;
SQL=&quot;SELECT * FROM tblCategories WHERE 1<>1&quot;
For i = 0 To UBound(aryID)-1
SQL=SQL&&quot; OR CategoryID=&quot;& aryID(i) &&quot;&quot;
next

So ultimately SQL string will be generated.
And then Execute it !!!!!

Rushi Shroff

 

[sifitz]

Whatever the code problems you have are, I think the code problems could have been avoided by setting the database up differently. You have a many to many relationship between members and catagories (any member can have a number of categories and and any category can belong to a number of members).

The way that this can be modelled quite elegantly in a database is to have a &quot;many to many&quot; table in between the members and categories tables. The fields are just member id and category id (and you should really have a primary key for this table as well). You simply use an INNER JOIN and the relationship is modelled. No need for string parsing.

Hope this is of help in the future.

Si Fitz

 

[Repec]

I would think that the easiest way to perform this is to do away with the loop and use the following to produce the recordset of categories.


&quot;SELECT * FROM tblCategories WHERE CategoryID IN( &quot; & stID & &quot&quot;

Eric Repec
eric@ericrepec.com

http://www.ericrepec.com

If its not broke try it make it faster.....

 

[Rexolio]

Okay, thanks guys! Going to try to set up the many to many relationship, as that obviously makes much more sense. because I have to get the site going on what I have to date, I will utilize JuanitaC and RushiShroff's suggestions.

Also, thanks Sifitz for not talking down to me in your suggestion. I asked this question in another forum and got drilled for setting up my database inappropriately. Its one thing to know you need help, ask for it, and receive a helpful reply. Its another to know you need help, ask for it, and then get called an idiot (more or less.) Discourages one from asking for more help. So I thank you very much. I know I don't know much and have a long way to go. I appreciate your help.

All of you have been a huge help! Thanks so much!

 

[sifitz]

Rexolio,

Thanks for the comment. Nice to know I am appreciated. Maybe you could send a reference to my boss ;-)

 

[B827]

I like this forum because you can ask the dumbest question and get a series of sensible answers and suggestions. Nobody knows it all and there's always a better way of doing something. Sandy

 

[RushiShroff]

Dear sifitz ,
I appreciate your suggestion of having only 2 field in the table and add cate like

Mem Id Categories
1 a
1 b
2 c
3 b
3 c
........

This way.
Now tell me if you want to find only those members having categories b, c and d (ALL Of them)then How will fire a query ??

Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[sakilgor]

SELECT * FROM members m WHERE
EXISTS (SELECT 0 FROM mems_cats mc
WHERE m.mem_id = mc.mem_id AND
mc.category = 'b') AND
EXISTS (SELECT 0 FROM mems_cats mc
WHERE m.mem_id = mc.mem_id AND
mc.category = 'c') AND
EXISTS (SELECT 0 FROM mems_cats mc
WHERE m.mem_id = mc.mem_id AND
mc.category = 'b')

 

[RushiShroff]

It could be OK
I tell you..

select Mem_Id from tblnm where Categories in ('b','c','d')
and count(*)=3 group by Mem_Id

Cheers
Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[sakilgor]

Excellent! Much cleaner than mine. One minor correction to yours... In SqlServer at least, you cannot have the Count(*) in the Where clause. It would need to be this...

SELECT Mem_Id FROM tblnm WHERE Categories in ('b','c','d')
GROUP BY Mem_Id HAVING count(*)=3

Sorry Rexolio, we've gotten off into a SQL discussion here!

 

[Rexolio]

no kidding!!! I'm soaking it all in though!