asp random image plus alt description

[fran7]

Hi, I have this nice code that returns a random image database record. It works great. What I am trying to do now is to be able to get the "alt" description for the image from another field. If I add another field to the query it returns two images. I was making the alt alt=""" & recordset(i) & """ but I know that must be wrong. well it works but returns the image name, ie images/friday.jpg. Anyone know how I can get it to return the image description from another field related to the image randomly returned. Thanks for any help.
Richard

 
<% 
'declare your variables
'declare SQL statement that will query your database
sql = "Select thumbnailurl FROM tblGreetPost Where CategoryID <> " & 60 & " and CategoryID <> " & 63 & " and CategoryID <> " & 61 & " and CategoryID <> " & 64 & " and CategoryID <> " & 66 & " and CategoryID <> " & 65 & " ORDER BY dateadded asc"
'create ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset") 
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & Server.MapPath("/fpdb/greetingcardpro.mdb") 
'Open the connection to the database
connection.Open(sConnString)
'Open the recordset object executing the SQL 
recordset.Open sql, connection, 3, 1
'count the number of records and hold this is the variable intTotalRecords
intTotalRecords = recordset.RecordCount
Randomize()
intRandomNumber = Int(intTotalRecords * Rnd)
'move to the random number returned
recordset.Move intRandomNumber
'open the table
'loop through the total number of fields
For i = 0 to recordset.Fields.Count - 1
'write out the field value
Response.write("<a href="""" title="" ""><img width=""100%"" class=""bord"" alt="" "" src=""" & recordset(i) & """/></a>")
Next
'close the recordset and connection objects and free up resources
recordset.Close
Set recordset=Nothing
connection.close
Set connection=Nothing
%>
 

[stepterr]

fran7, without digging in to deep something caught my eye. Look at one of the loops you are doing.

 
'loop through the total number of fields 
For i = 0 to recordset.Fields.Count - 1 
 'write out the field value 
 Response.write ("<a href="""" title="" ""><img width=""100%"" class=""bord"" alt="" "" src=""" & recordset (i) & """/></a>") 
Next 
 

It looks like you are looping through each field, but yet in your query you only select one field from the table. I would start there.

[jhardman]

Richard,

stepterr is right, you are currently only asking the db to give you one field of data. You will need to select two fields (something like this):

 
<% 
'declare your variables
'declare SQL statement that will query your database
sql = "Select thumbnailurl, thumbnaildescription FROM tblGreetPost Where CategoryID <> " & 60 & " and CategoryID <> " & 63 & " and CategoryID <> " & 61 & " and CategoryID <> " & 64 & " and CategoryID <> " & 66 & " and CategoryID <> " & 65 & " ORDER BY dateadded asc"
 

Jared

[fran7]

Thanks guys, Yes that was the problem but I eventually got it going like this.
Thanks Richard

<%
' ADO Constant. Dont change this
' Connection string and SQL statement
Dim sql , connStr
sql = "Select author,artist,tip,website,thumbnailurl,postcardid, download FROM tblGreeting Where CategoryID <> " & 60 & " and CategoryID <> " & 63 & " and CategoryID <> " & 61 & " and CategoryID <> " & 64 & " and CategoryID <> " & 66 & " and CategoryID <> " & 65 & " ORDER BY dateadded asc"
connStr = " Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/fpdb/data.mdb" )
' Opening database
Dim rs
Set rs = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, connStr, 3, , adCmdText
' Generating random number from total number of records
Dim intRnd
Randomize Timer
intRnd = (Int(RND * rs.RecordCount))
' Now moving the cursor to random record number
rs.Move intRnd
' Showing the random statement
Response.Write " <a target=""_blank"" href=""http://""><img width=""100%"" alt=""" & rs("download") & """ border=""0"" src=""" & rs("thumbnailurl") & """></a><br><br>"
' Closing the database
rs.Close
Set rs = Nothing
%>