Display random returned string from Access dB
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>
Re: Display random returned string from Access dB
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>
