Display random returned string from Access dB

**drizz** · Jan 5th, 2004, 02:01 PM

I have an Access dB that I am trying to use to highlight our Products on our intranet. I have created a simple page that can display either the first string in my query or all the strings returned. I am trying to make it so that every time the page is reloaded, it cycles to a new product w/ picture and title and description.

Do I need to create an array and use the randomize function in Vbscript? I am somewhat of a newbie so any help is appreciated. If what I have written doesn't make sense, just let me know and I'll try to clarify.

Here is the code:

<%

Dim adoCon 'Database Connection Variable
Dim strAdoConfig 'Holds the Database driver and the path and name of the database
Dim strCon 'Holds the Database driver and the path and name of the database
Dim strProgSql 'Holds the SQL Select query for the database

'Create database connection
'Create a connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Connection String for Access 2000/2002
strAdoConfig = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("./_db/programs.mdb")

'Set an active connection to the Connection object
adoCon.Open strAdoConfig

%>

<html>
<head>
<title>Test Programs</title>
</head>
<body>
<%
Dim rsPrograms 'Database recordset holding the items
Dim intProgItems 'Loop counter for displaying the items

'Create recorset object
Set rsPrograms = Server.CreateObject("ADODB.Recordset")

'Create Select Statement
strProgSql = "SELECT Prog_Main.name, Prog_Main.desc, Prog_Main.url, Prog_Pics.pics FROM Prog_Main LEFT JOIN Prog_Pics ON Prog_Main.id = Prog_Pics.prog_id "

'Query the database
rsPrograms.Open strProgSql, adoCon

'If there are no items to display then display a message saying so
If rsPrograms.EOF Then Response.Write("<span class=""text"">Sorry, There are no Programs to display</span>")

'Loop round to display each of the programs
For intProgItems = 1 to 10

'If there are no records then exit for loop
If rsPrograms.EOF Then Exit For

%>

<table><tr><td>
<% = rsPrograms("pics") %>
<% = rsPrograms("name") %>
<% = rsPrograms("desc") %>
<% = rsPrograms("url") %>
</td></tr></table><br>
<%
'Move to the next record in the recordset
rsPrograms.MoveNext
Next

'Reset server objects
rsPrograms.Close
Set rsPrograms = Nothing
Set strAdoConfig = Nothing
Set adoCon = Nothing
%>
</body>
</html>

**mendhak** · Jan 5th, 2004, 02:45 PM

VB Code:

<%
 
Dim adoCon              'Database Connection Variable
Dim strAdoConfig        'Holds the Database driver and the path and name of the database
Dim strCon              'Holds the Database driver and the path and name of the database
Dim strProgSql          'Holds the SQL Select query for the database
 
 
 
 
'Create database connection
'Create a connection object
  Set adoCon = Server.CreateObject("ADODB.Connection")
 
'Connection String for Access 2000/2002
  strAdoConfig = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("./_db/programs.mdb")
 
'Set an active connection to the Connection object
  adoCon.Open strAdoConfig
 
%>
 
<html>
<head>
  <title>Test Programs</title>
</head>
<body>
<%
  Dim rsPrograms        'Database recordset holding the items
  Dim intProgItems  'Loop counter for displaying the items
 
'Create recorset object
  Set rsPrograms = Server.CreateObject("ADODB.Recordset")
 
'Create Select Statement
  strProgSql = "SELECT Prog_Main.name, Prog_Main.desc, Prog_Main.url, Prog_Pics.pics  FROM Prog_Main LEFT JOIN Prog_Pics ON Prog_Main.id = Prog_Pics.prog_id   "
 
'Query the database
  rsPrograms.Open strProgSql, adoCon
 
 
 
'If there are no items to display then display a message saying so
  If rsPrograms.EOF Then Response.Write("<span class=""text"">Sorry, There are no Programs to display</span>")
 
 
 
'If there are no records then exit for loop
  If rsPrograms.EOF Then Exit For
 
 
Dim randomnumber
Dim numberofprograms
 
numberofprograms = rsPrograms.RecordCount
randomnumber =  Int((numberofprograms - 1 + 1) * Rnd + 1)
 
rsPrograms.Move randonnumber, 1
 
%>
 
<table><tr><td>
<% = rsPrograms("pics") %>
<% = rsPrograms("name") %>
<% = rsPrograms("desc") %>
<% = rsPrograms("url") %>
</td></tr></table><br>
<%
 
'Reset server objects
  rsPrograms.Close
  Set rsPrograms = Nothing
  Set strAdoConfig = Nothing
  Set adoCon = Nothing
%>
</body>
</html>

**mendhak** · Jan 5th, 2004, 02:46 PM

It's pretty inefficient, but I've modified your code

**drizz** · Jan 5th, 2004, 02:55 PM

Mendhak, thank you!!! I just need somewhere to start. Can you suggest another way of doing this. I was wondering if there way though another select statement...

Anyway, thanks for the quick response...

**mendhak** · Jan 5th, 2004, 02:59 PM

First do a SELECT COUNT()... on the table.

Get that number, and then perform the random number thing on that number, and *then* use it in the original SQL statement like:

strSQL = "SELECT Prog_Main.name, Prog_Main.desc, Prog_Main.url, Prog_Pics.pics FROM Prog_Main LEFT JOIN Prog_Pics ON Prog_Main.id = Prog_Pics.prog_id WHERE Prog_Main.id = " & therandomnumber

Thread: Display random returned string from Access dB

Thread Tools

Display

Display random returned string from Access dB

Re: Display random returned string from Access dB

Posting Permissions