In Part 1 we examined the code to get a picture's ranking into the
database. Now that we have a ranking from the user we'd like to display the picture's current ranking.
Before we can do this, though, we need to grab the picture's current ranking from the database. That's
what we'll examine how to do in this part!
The Image
To display the image I am going to use the src attribute of an <img>
tag set to an .aspx file that will generate the image and return it directly to the
browser. Alternately, I could create the image, save it to the server and set the
<img> tag's src attribute to that filename.
The only problem with this approach is that I'd end up with a lot of temporary image files on my Web server
that would need to be deleted every so often. (For more information on dynamic image creation
see this sample chapter
from ASP.NET: Tips, Tutorials, and Code.)
To specify the image to display we'll create a server-side img tag. Then, in the code
we can specify its Src property programmatically:
<!-- create a server-side image tag -->
<img id="RatingBar" runat="server" />
'In the server-side code, you can set the src property like so:
RatingBar.Src = "photo_rating_image.aspx?Photo_ID=" & Photo_ID
In the above code I concatenate Photo_ID to the filename and querystring and then set that to the
src attribute of my image control RatingBar.
Let's look at the code for the file photo_rating_image.aspx.
First I will import the namespaces used in this file, open a server-side script block, and begin the
Page_Load event handler:
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
<%@ Import Namespace = "System.Drawing" %>
<%@ Import Namespace = "System.Drawing.Imaging" %>
<Script runat="server">
Sub Page_Load( Sender As Object, e As EventArgs)
The file photo_rating_image.aspx will receive via the querystring the Photo_Id
it will need in order to get the specified photo's current rating and generate a graphical representation
of that rating. Therefore, in my Page_Load event handler I need to grab the querystring
value like so:
'Start by getting our Photo_ID from the querystring
Dim Photo_ID As Integer = Request.Querystring("Photo_ID")
In Part 1, when I needed to establish a database connection to write the user's vote into the database
table. Now that I wish to display the overall ranking, I need to create another database connection and
pull out the cumulative ranking. This is done in a similar fashion as before, only this time I am
calling the sp_Muggle_PhotoRatingOutput stored procedure.
'Set up a connection to my database as well as a
'SqlCommand Object for my stored procedure
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("MyDSN"))
Dim myCommand As New SqlCommand("sp_Muggle_PhotoRatingOutput", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
'Add the Photo_ID as a parameter to the stored procedure
Dim parameterPhoto_ID As New SqlParameter("@Photo_ID", SqlDbType.Int, 4)
parameterPhoto_ID.Value = Photo_ID
myCommand.Parameters.Add(parameterPhoto_ID)
This stored procedure differs from my previous example because it returns a value via an output
parameter. SqlCommmand parameters are by default input parameters. So, I need to specify
the parameter's direction as being output. Notice it is exactly as above except I cannot assign a
value to an output parameter. Also, I must add a line to let the SqlCommand object know
the parameter parameterRating is an output parameter. This is accomplished like so:
'Add the Rating as a parameter to the stored procedure
'and specify its direction as output
Dim parameterRating As New SqlParameter("@Rating", SqlDbType.Float, 8)
parameterRating.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRating)
I can execute the SqlCommand and retrieve the value of the output parameter by using the
following lines of code:
'Open the connection, execute the stored procedure and close the connection
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Dim Rating_Output as Double
Rating_Output = parameterRating.Value
Now I have a variable Rating_Output of type Double that contains the average ranking for
the photo specified by @PhotoID. (Note that this value will be between 1.0 and 5.0.) In
order to show a graphical representation of that Rating I will need to convert it to a whole number.
First I need to set up some constants for my image.
Because of the layout of my site I want to limit image to a width of 168 pixels and a height of 15
pixels. Therefore, I should set those up to be constants.
'Set up the constants for our image
Dim MaxImageLength As integer
Dim MaxImageHeight As integer
MaxImageHeight = 15 ' pixels
MaxImageLength = 168 ' pixels
If the largest length I would want the image is MaxImageLength or 168, then
(Rating_Output / 5) * MaxImageLength is an expression that I can use to ensure my
graphical representation of the rating will never exceed the MaxImageLength of 168 pixels.
Dim Rating as Integer
Rating = Cint(((Rating_Output) / 5) * MaxImageLength)
Now that we have the rating for the image we'll examine how we can dynamically create a JPEG showing
a graphical representation of the picture's ranking in Part 3.
