Here is the code I use in the World recipe random record. I've been looking all over the net on how to display random record using access db on ASP .NET, but found most for SQL server like NEWID(), doesn't work on Access. This could be easily done in ASP 3.0. So I do some research on it, and came with this code.
Note: This does not use code behind, it's inline. Written entirely in notepad. I have VS .NET but decide not to use it.
LIVE DEMO: http://www.myasp-net.com
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Oledb" %>
<script runat="server">
'Pulls a Random number for selecting a random recipe
Sub RandomRecipeNumber()
'It connects to database
strSQL = "SELECT ID FROM Recipes"
Dim objDataReader as OledbDataReader
'Call Open database - connect to the database
DBconnect()
objConnection.Open()
objDataReader = objCommand.ExecuteReader()
'Counts how many records are in the database
Dim iRecordNumber = 0
do while objDataReader.Read()=True
iRecordNumber += 1
loop
objDataReader.Close()
objConnection.Close()
'Here's where random number is generated
Randomize()
do
iRandomRecipe = (Int(RND() * iRecordNumber))
loop until iRandomRecipe <> 0
End Sub
'Pulls aand dsiplay random recipe records
Sub RandomRecipe()
strSQL = "SELECT ID,CAT_ID,Category,Name,Author,Date,HITS,RATING,NO_RATES, (RATING/NO_RATES) AS Rates FROM Recipes"
Dim objDataReader as OledbDataReader
'Call Open database - connect to the database
DBconnect()
objConnection.Open()
objDataReader = objCommand.ExecuteReader()
Dim i = 0
'Go until a random position
do while i<>iRandomRecipe
objDataReader.Read()
i += 1
loop
Dim strRanRating as Double
'Display recipe
lblRating2.Text = "Rating:"
lblRancategory.text = "Category:"
lblranhitsdis.text = "Hits:"
lblranhits.text = objDataReader("Hits")
strRanRating = FormatNumber(objDataReader("Rates"), 1, -2, -2, -2)
lblranrating.Text = "(" & strRanRating & ")"
strRatingimg = FormatNumber(objDataReader("Rates"), 1, -2, -2, -2)
LinkRanName.NavigateUrl = "recipedetail.aspx?id=" & objDataReader("ID")
LinkRanName.Text = objDataReader("Name")
LinkRanName.ToolTip = "View" & " - " & objDataReader("Name") & " - " & "recipe"
LinkRanCat.NavigateUrl = "category.aspx?catid=" & objDataReader("CAT_ID")
LinkRanCat.Text = objDataReader("Category")
LinkRanCat.ToolTip = "Go to" & " - " & objDataReader("Category") & " - " & "&category"
objDataReader.Close()
objConnection.Close()
End Sub
'Database connection string - Open database
Sub DBconnect()
objConnection = New OledbConnection(strConnection)
objCommand = New OledbCommand(strSQL, objConnection)
End Sub
'Declare public so it will accessible in all subs
Public strDBLocation = DB_Path()
Public strConnection as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBLocation
Public objConnection
Public objCommand
Public strSQL as string
Public strRatingimg as Integer
Public iRandomRecipe as integer
'Get the database server Map Path - This is where you change the database name and path
Function DB_Path()
if instr(Context.Request.ServerVariables("PATH_TRANSLATED"),"Recipes") then
DB_Path = System.Web.HttpContext.Current.Server.MapPath("MyRecipes.mdb")
else
DB_Path = System.Web.HttpContext.Current.Server.MapPath("/mydb/MyRecipes.mdb")
end if
End Function
</script>On the page_load call the random subs
'Call Random Recipe
RandomRecipeNumber()
RandomRecipe()
LIVE DEMO: http://www.myasp-net.com
Have fun!