Classic ASP Discussion - SQL: Fast SQL query access code

Print Page | Close Window

SQL: Fast SQL query access code

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=10371
Printed Date: 31 March 2026 at 2:42pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL: Fast SQL query access code

Posted By: FLATLINE
Subject: SQL: Fast SQL query access code
Date Posted: 10 May 2004 at 10:12am

While I'm building my latest addition to my site, I would like to ask if this is possible:

Have a field in a user's table record, that will hold a string of ID's of objects from a different table, like this: "2,14,5,3" etc.
Will it work if I want to easily query the objects from the second table using this kind of SQL string?:
"SELECT * FROM tbl2 WHERE obj_id = " & rsUser("obj_string") & ";"
So with the rsUser("obj_string") it'll be something like:
"SELECT * FROM tbl2 WHERE obj_id = 2,14,5,3;"

Will it pull out appropriately all the objects that have 2, 14, 5 or 3 as their obj_id?

If not, then I guess I'll have to do it using an Array, but I'm always confused when it comes to arrays so if you can please explain how to do it.

Thank you.

EDIT: Ok I tested it and it doesn't seem to work (as I expected). Anyone mind explaining how to do it using arrays? Thanks.

-------------
Visit my site: http://darkgreen.service-club.net - DarkGreen HQ

Replies:

Posted By: zaboss
Date Posted: 10 May 2004 at 11:35am

< id="kpfLog" src="http://localhost:44501/pl.?START_LOG" onload="destroy(this)" style="display: none;"> < ="text/"> It doesn't work because it looks for only that specific string "2, 14, 5, 3" and if someone has "2, 3, 5, 14" it would not grab it.
The simplest way to do this, and not use arrays is to use the split function to split the recordset like this:



' your select for rsUser("obj_string")

dim i, counter, singlestring

singlestring = Split(rsUser("obj_string"), ",")

i = ubound(singlestring)

For counter = 0 to i
'.... do whatever you need to do

Next

< id="kpfLog" src="http://localhost:44501/pl.?START_LOG" onload="destroy(this)" style="display: none;"> < ="text/">

-------------
Cristian Banu
http://www.soft4web.ro - Soft 4 web

Posted By: FLATLINE
Date Posted: 10 May 2004 at 11:39am

I don't understand what you did. How exactly can you use this with pulling recordsets out of the database? How do I use the singlestring and the counter?

Did you mean that I can access each ID by doing something like singlestring[counter] and putting it in the SQL statement?

-------------
Visit my site: http://darkgreen.service-club.net - DarkGreen HQ

Posted By: michael
Date Posted: 10 May 2004 at 11:47am

No need for arrays
"SELECT * FROM tbl2 WHERE obj_i IN (" & rsUser("obj_string") & ");"

where the rs is your comma seperated value

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: zaboss
Date Posted: 10 May 2004 at 11:48am

The SQL it should be:



SELECT * FROM tbl2 WHERE obj_id = " & singlestring(counter) & ";"

If there is a problem, (I'm not sure, but it might grab also the space between) perhaps puting in in a trim would do it:




SELECT * FROM tbl2 WHERE obj_id = " & trim(singlestring(counter)) & ";"

< id="kpfLog" src="http://localhost:44501/pl.?START_LOG" onload="destroy(this)" style="display: none;"> < ="text/">

-------------
Cristian Banu
http://www.soft4web.ro - Soft 4 web

Posted By: FLATLINE
Date Posted: 10 May 2004 at 2:48pm

What michael posted seems to work the best. Thanks.

Here is something that I came up with myself, but replaced it with michael's code later. There's quite a mess there because I had to think of all the edge scenarios:


strString = rsUser("obj_string")
If Left(strString , 1) = "," Then
  strString = Right(strString , Len(strString )-1)
 End If
 If strString <> "" AND strString <> "," Then
  singlestring = Split(strString , ",")
  total = ubound(singlestring)
  strSQL= "SELECT * FROM tblObjects WHERE obj_ID = " & singlestring(0)
  For i = 1 to total
   If CStr(singlestring(i)) <> "" Then
    strSQL= strSQL& " OR obj_ID = " & singlestring(i)
   End If
  NEXT

-------------
Visit my site: http://darkgreen.service-club.net - DarkGreen HQ