How can I effectively select one unique record from a DB quickly. The current extension takes too long to load the record. I am using the extension posted on the Extensions Board of Marcormedia.?
Thanks

does your table have a unique identifying field such as ID?

Doc
Rangewalk Digital Studios

Yes I have a unique record identifier in my access DB.
The load times are too long.
Thanks

I wrote this quickly, but tested and works...

<%
' ::::: DEFINE VARIABLES
Dim rs1, rs2, TotRecs, RndRecNum

' ::::: OPEN RS TO GET TOTAL RECORDS

Set rs1 = Server.CreateObject("ADODB.Recordset"
rs1.ActiveConnection = MM_CONN_STRING
rs1.Source = "SELECT COUNT(*) AS Total FROM dbo.Users"
rs1.CursorType = 0
rs1.CursorLocation = 2
rs1.LockType = 1
rs1.Open

TotRecs = rs1.Fields("Total".Value

' ::::: CLEAN UP

RS1.Close
Set rs1 = Nothing

' ::::ISPLAY TOTAL NUMBER OF RECS IN TABLE

Response.Write TotRecs & "<br><br> "

' ::::: INITIALIZE RANDOMIZATION

Randomize()

' ::::: SET VARIABLE = TO A RANDOM INTEGER WITH THE TOTAL NUMBER OF RECORDS IN TABLE AS UPPER LIMIT

RndRecNum = INT(TotRecs * Rnd())

' ::::: DISPLAY OUR RANDOM NUMBER

Response.Write RndRecNum

' ::::: OPEN RS WITH RANDOM NUMBER AS THE UNIQUE RECORD IDENTIFIER

Set rs2 = Server.CreateObject("ADODB.Recordset"
rs2.ActiveConnection = MM_CONN_STRING
rs2.Source = "SELECT * FROM dbo.Users WHERE USERID='" & RndRecNum & "'"
rs2.CursorType = 0
rs2.CursorLocation = 2
rs2.LockType = 1
rs2.Open

' ::::: DISPLAY SOME DATA FROM THE RETURNED RS

Response.Write "<BR><BR>" & rs2.Fields.Item("UserName".value
%>

This assumes that your unique record identifier is the same as the record number...you can play around with the logic to get the data to match up...also, there is no error checking incase a NULL RS is returned, you can build that as well.

If you need further help let me know.

Hope this helps...

Doc
Rangewalk Digital Studios

Thanks very much. I will try it out.
A very big Mahalo.

hey...im also doing a quiz page..and i could display some random records which is not duplicated when i use repeat region......


i'v got already the download dmx extension to random records. the thing is.. records are duplicated.

Hi

To generate random records from SQL, try this fast code:

SELECT TOP 1 columnName
FROM tableName
ORDER BY NEWID()

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>