SQL query gets random image, but browser always loads same one

[Haydnw]

Hi,

I have the code below as code-behind for a page which displays two images.
My problem is with the second bit of code, commented as " 'Portfolio image
section". Basically, the SQL query gets details of a random image from an
access database. I know this works because when I run the query in access,
it picks a different image each time (or close enough). However, when I view
the .aspx page that this code-behind is for, it always picks the same image.
Can anyone please identify why this might be? I've tried deleting browser
cache etc but still no luck. Apologies for dumping huge lots of code in this
message, but I'm guessing you'll need to see it

Thanks very much,
Haydn


Code-behind:
=========


Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.Oledb
Imports System.Configuration

Public Class GetFrontPageInfo : Inherits Page

Public objConn As OledbConnection

'Declare labels for holding information re Newest album
Public lblstrNewestArtist As Label
Public dtmDate As DateTime
Public lblstrNewestDate As Label
Public lnkNewestAlbum As Hyperlink
Public imgNewestImage AS Image

'Declare labels for holding information re Portfolio image
Public lblstrPortfolioArtist As Label
Public lblstrPortfolioDate As Label
Public imgPortfolioImage AS Image

Public Sub Page_Load(Sender As Object, E As EventArgs)

'Newest album section
'====================

'SQL statement
Dim strSQL as String
strSQL= "SELECT tblEvents.strDirectory, tblEvents.strArtist,
tblEvents.strVenue, tblEvents.strTown, tblEvents.dtmDate FROM tblEvents
INNER JOIN sqyMaxDirOnly ON tblEvents.strDirectory =
sqyMaxDirOnly.strNewestDir;"

'Connection object
objConn = New
OledbConnection(ConfigurationSettings.AppSettings("strConnString"))

'Command object
Dim objNewestCommand As New OledbCommand(strSQL, objConn)

'Data Reader
Dim objNewestDataReader as OledbDataReader

'Open connection and execute command on data reader
objConn.Open()
objNewestDataReader = objNewestCommand.ExecuteReader()

'Set label text to values pulled from database
'Use 'Do While Loop' so that null values are allowed
'(I know there aren't any null values but it gives an error otherwise!)
Do While objNewestDataReader.Read()=True
imgNewestImage.ImageUrl = "images/newest/" &
objNewestDataReader("strDirectory") & ".jpg"
lnkNewestAlbum.NavigateUrl = "html/album.aspx?d=" &
objNewestDataReader("strDirectory")
lblstrNewestArtist.text = objNewestDataReader("strArtist")
'Need to use DateTime object to obtain correct formatting, ie
month and year only
dtmDate = objNewestDataReader("dtmDate")
lblstrPortfolioDate.text = dtmDate.ToString("MMMM yyyy")
Loop

'Close data reader
objNewestDataReader.Close()


'Portfolio image section
'=======================

'SQL statement
strSQL= "SELECT tblEvents.strDirectory, tblEvents.strArtist,
tblEvents.strVenue, tblEvents.strTown, tblEvents.dtmDate,
tblEvents.strArtistWebsite, sqyRandPortImage.strFilename FROM tblEvents
INNER JOIN sqyRandPortImage ON tblEvents.strDirectory =
sqyRandPortImage.strDirectory;"

'Command object
Dim objPortCommand As New OledbCommand(strSQL, objConn)

'Data Reader
Dim objPortDataReader as OledbDataReader

'Execute command on data reader - connection is already open
objPortDataReader = objPortCommand.ExecuteReader()

'Set label text to values pulled from database
'Use 'Do While Loop' so that null values are allowed
'(I know there aren't any null values but it gives an error otherwise!)
Do While objPortDataReader.Read()=True
lblstrPortfolioArtist.text = objPortDataReader("strArtist")
'Need to use DateTime object to obtain correct formatting,
ie month and year only
dtmDate = objPortDataReader("dtmDate")
lblstrNewestDate.text = dtmDate.ToString("MMMM yyyy")
'Set image path
imgPortfolioImage.ImageUrl = "images/albums/" &
objPortDataReader("strDirectory") & "/" & objPortDataReader("strFilename") &
".jpg"
Loop

'Close data reader
objPortDataReader.Close()

'Close connection
objConn.Close()


End Sub


End Class

 

[Peter Morris]

Hi

Why are you writing the DB image to disk? You should have an ASPX page
which returns the image data and sets the content type to image/jpg


--
Pete
====
Audio compression components, DIB graphics controls, FastStrings
http://www.droopyeyes.com

Read or write articles on just about anything
http://www.HowToDoThings.com

 

[Bart]

Forgive me, I couldn't be bothered to read all of your code, however
something that I use that may help. I place a random number in a querystring
in the image URL like this:

Dim randomNumber As New Random
....
imgNewestImage.ImageUrl = "images/newest/" &
objNewestDataReader("strDirectory") & ".jpg?rnd=" & randomNumber.Next(10000)

Try that for each image URL.

David

 

[Guest]

Hi Pete,

Forgive me, but I don't follow. I don't write the image to disk (do I?) -
this page is for viewing images. I merely get the directory and filename of
an image from the database, concatenate them and use that to set the ImageUrl
property of an <asp:image>. If you could expand on your previous post I would
be very grateful.

Thanks,
Haydn

 

[Haydnw]

Hi all,

Figured out what makes this go wrong - it's the objConn.Close() on the very
last line. However, if closing the connection here breaks my page, where is
the best place to close it? Or it is just closed automatically after the
page loads? I don't want loads of open connections to the database, do I?!

Cheers,
H

 

[Guest]

Do you still need help?

Hi,

I have the code below as code-behind for a page which displays two images.
My problem is with the second bit of code, commented as " 'Portfolio image
section". Basically, the SQL query gets details of a random image from an
access database. I know this works because when I run the query in access,
it picks a different image each time (or close enough). However, when I view
the .aspx page that this code-behind is for, it always picks the same image.
Can anyone please identify why this might be? I've tried deleting browser
cache etc but still no luck. Apologies for dumping huge lots of code in this
message, but I'm guessing you'll need to see it

Thanks very much,
Haydn


Code-behind:
=========


Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.Oledb
Imports System.Configuration

Public Class GetFrontPageInfo : Inherits Page

Public objConn As OledbConnection

'Declare labels for holding information re Newest album
Public lblstrNewestArtist As Label
Public dtmDate As DateTime
Public lblstrNewestDate As Label
Public lnkNewestAlbum As Hyperlink
Public imgNewestImage AS Image

'Declare labels for holding information re Portfolio image
Public lblstrPortfolioArtist As Label
Public lblstrPortfolioDate As Label
Public imgPortfolioImage AS Image

Public Sub Page_Load(Sender As Object, E As EventArgs)

'Newest album section
'====================

'SQL statement
Dim strSQL as String
strSQL= "SELECT tblEvents.strDirectory, tblEvents.strArtist,
tblEvents.strVenue, tblEvents.strTown, tblEvents.dtmDate FROM tblEvents
INNER JOIN sqyMaxDirOnly ON tblEvents.strDirectory =
sqyMaxDirOnly.strNewestDir;"

'Connection object
objConn = New
OledbConnection(ConfigurationSettings.AppSettings("strConnString"))

'Command object
Dim objNewestCommand As New OledbCommand(strSQL, objConn)

'Data Reader
Dim objNewestDataReader as OledbDataReader

'Open connection and execute command on data reader
objConn.Open()
objNewestDataReader = objNewestCommand.ExecuteReader()

'Set label text to values pulled from database
'Use 'Do While Loop' so that null values are allowed
'(I know there aren't any null values but it gives an error otherwise!)
Do While objNewestDataReader.Read()=True
imgNewestImage.ImageUrl = "images/newest/" &
objNewestDataReader("strDirectory") & ".jpg"
lnkNewestAlbum.NavigateUrl = "html/album.aspx?d=" &
objNewestDataReader("strDirectory")
lblstrNewestArtist.text = objNewestDataReader("strArtist")
'Need to use DateTime object to obtain correct formatting, ie
month and year only
dtmDate = objNewestDataReader("dtmDate")
lblstrPortfolioDate.text = dtmDate.ToString("MMMM yyyy")
Loop

'Close data reader
objNewestDataReader.Close()


'Portfolio image section
'=======================

'SQL statement
strSQL= "SELECT tblEvents.strDirectory, tblEvents.strArtist,
tblEvents.strVenue, tblEvents.strTown, tblEvents.dtmDate,
tblEvents.strArtistWebsite, sqyRandPortImage.strFilename FROM tblEvents
INNER JOIN sqyRandPortImage ON tblEvents.strDirectory =
sqyRandPortImage.strDirectory;"

'Command object
Dim objPortCommand As New OledbCommand(strSQL, objConn)

'Data Reader
Dim objPortDataReader as OledbDataReader

'Execute command on data reader - connection is already open
objPortDataReader = objPortCommand.ExecuteReader()

'Set label text to values pulled from database
'Use 'Do While Loop' so that null values are allowed
'(I know there aren't any null values but it gives an error otherwise!)
Do While objPortDataReader.Read()=True
lblstrPortfolioArtist.text = objPortDataReader("strArtist")
'Need to use DateTime object to obtain correct formatting,
ie month and year only
dtmDate = objPortDataReader("dtmDate")
lblstrNewestDate.text = dtmDate.ToString("MMMM yyyy")
'Set image path
imgPortfolioImage.ImageUrl = "images/albums/" &
objPortDataReader("strDirectory") & "/" & objPortDataReader("strFilename") &
".jpg"
Loop

'Close data reader
objPortDataReader.Close()

'Close connection
objConn.Close()


End Sub


End Class