| Author |
 Topic Search  Topic Options
|
zaboss 
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Retrieving more records from 1 field
Posted: 14 October 2003 at 6:54am |
I have a field (TestID) in which I store the ID of the quizes a user is allowed to take. It could be 1 records, could be several, coma delimited (like 1, 2, 39, 103).
How could I pull them from db one by one? as I need to pass them to a query string like in examiner.asp?quizID=1?
|
|
|
 |
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2003 at 8:19am |
|
I would query the whole field and split the values into an array. will be easier to work with that way.
|
|
|
|
zaboss
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2003 at 8:26am |
Kind of knew that, but I do not know how to write it ! I have tryed
<%
dim i, j
i=1
Set rs= MyConn.Execute("Select TestID from Login Where UserName = '" & Session("UserName") &"'")
While i < len(rs("TestID"))
j=instr(i, ",", rs("TestID"))
response.write("<p><a href = examiner.asp?quizID=" & mid(rs("TestID"), i, j-i-1) & ">" & "Test " & i & "</a></p>")
i=j+1
Wend
rs.close
set rs = nothing%></p>
But it triggers the error : Invalid procedure call or argument: 'mid'
|
|
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2003 at 12:24pm |
dim i,counter, testids
Set rs= MyConn.Execute("Select TestID from Login Where UserName = '" & Session("UserName") &"'")
testids = Split(rs("TestID"),",")
i = ubound(testids)
For counter = 0 to i
Response.Write "TestID= " & testid(counter)
NEXT
|
This should work, have not tested it, just give it a shot...
|
|
|
|
zaboss
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2003 at 3:33pm |
Thanks Michael, that worked. 
|
|
|
|
Flamewave
Senior Member
Joined: 19 June 2002
Location: United States
Status: Offline
Points: 376
|
Post Options
Thanks(0)
Quote Reply
Posted: 15 October 2003 at 11:42am |
|
Seems like an awful lot of overhead, if you are intrested in improving the performance of this, I would create another table that has a column for the user id and a colum for the test id, and then enter in a single record for each test id that the user has access to, you can then use a select statement to find out if the user has access to that test, and skip all of the array splitting and comparison stuff asp side.
|
|
- Flamewave
They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.
|
|
zaboss
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
Post Options
Thanks(0)
Quote Reply
Posted: 15 October 2003 at 12:38pm |
|
Creating another table would fill the db with thousands of useless records. I think the performance would be much more affected while looping through thousands records than spliting one record into several others.
|
|
|
|
Flamewave
Senior Member
Joined: 19 June 2002
Location: United States
Status: Offline
Points: 376
|
Post Options
Thanks(0)
Quote Reply
Posted: 16 October 2003 at 1:28am |
|
Depends on what database you are using. If you are using SQL, then you can use stored procs and views to do the select statements, and its about 10x faster (well, maybe thats an exaguration) then looping through it ASP side, excpecially (damn I need to learn how to spell one of these days) if you have thousdands of recordsets. If you are using Access, then there probally wouldn't be much if any of a difference in speed between the two, although you can still use the "querries" section of Access to create an equivialnt of a view in SQL server, that can greatly speed up the performance. If its something other then Access or SQL, I don't know enugh about them to voice my opinion on performance with them.
|
|
- Flamewave
They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.
|
|
