Introduction
I have been getting lots of requests from my relatives for pictures of my new son, Jake. Naturally,
as a Web developer I thought to create a Web site for the baby and put all of the photos of the
little guy on there. Further, I thought, wouldn't it be cool if Jake's admirers could vote for their
favorite photo and at the end of every month a shiny print of that month's winner would show up in
their mailbox. That's when I decided to build utility that I could use on the site for tracking each
photo's rating. I began by planning how the utility would function.
Planning
Before I begin any programming task I always take some time to think about the problem and develop an
outline of steps. The first step to this utility was to examine to desired functionality and I
did so by writing out a wish list.
Allow users of the site to rate each photo.
Photo ratings will be on a scale of one to five.
Display graphical representation of how well each photo was doing.
Generate feedback (In case my grandmother wants to give me photographic pointers).
Design
Next I decided I needed to design the picture rating form, and ended up designing the form shown to
the right. This form is built using the following code:
The form consists of a RadioButtonList (MyRadioButtonList), a multiline textbox and a
linkbutton that fires the Submit_Rating event handler (Sub) shown below. I have also included a
RequiredFieldValidator control that is used to ensure the user selects one of the radio buttons in the
list. (For more information on the RequiredFieldValidator and the other ASP.NET validation controls
be sure to read: Form Validation with
ASP.NET - It Doesn't Get Any Easier.) I need to do this because my database table does not allow
nulls in the rating field.
Now I need to insert all the values from the form into a database. I chose to do this via a stored
procedure, sp_Muggle_PhotoRatingInsert. The first thing I need to do is
set up my database connection and SQLCommand object. I use a DSN (Data Source Name)
stored in the appSettings portion of my web.config to connect to my database.
(For some sample code of adding a record to a database see this
article. For more information on web.config files check out:
Format of ASP.NET
Configuration Files.) The below event handler is fired when the linkbutton is clicked.
Sub Submit_Rating(sender As Object, e As EventArgs)
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("MyDSN"))
'The SqlCommand receives two parameters. The first is the name of my stored procedure.
'And the second is the name of my database connection from above.
Dim myCommand As New SqlCommand("sp_Muggle_PhotoRatingInsert", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Next I need to add the input parameters for the stored procedure. This is done by declaring a
variable as type SqlParameter. You must also provide the input parameter name that
the stored procedure is expecting(such as @Photo_ID) the type (in this case
SqlDbType.Int) and the size of the variable. Then the value of the parameter is set to
the desired value. Finally, the parameter is added to the SqlCommand using its
Parameters.Add method passing it the name of the new parameter. Examine the following
code:
Dim parameterPhoto_ID As New SqlParameter("@Photo_ID", SqlDbType.Int, 4)
'Here Photo_ID is a public variable set by the calling page
parameterPhoto_ID.Value = Photo_ID
myCommand.Parameters.Add(parameterPhoto_ID)
One of the neat things about radiobuttonlists is the ability to grab the selected item's value
directly by using the radiobuttonlist's SelectedItem.Value property. To set up that
parameter and set its value to the selected radio button, I use the following code:
Dim parameterRating As New SqlParameter("@Rating", SqlDbType.Float, 8)
parameterRating.Value = MyRadioButtonList.SelectedItem.Value
myCommand.Parameters.Add(parameterRating)
The rest of the parameters for the stored procedure are pretty straight forward. I just declare a
variable of type SqlParameter passing it the required parameters as stated above. Then
I set the parameter's value. Finally, I add the parameter to the SqlCommand object. In
addition to the @PhotoID and @Rating parameters, my stored procedure also
has @Comments and @Remote_Address parameters, which specify the voter's
comments (if any) and the voter's IP address, respectively.
Dim parameterComments As New SqlParameter("@Comments", SqlDbType.VarChar, 255)
parameterComments.Value = Comments.Text 'The contents of the multiline textbox
myCommand.Parameters.Add(parameterComments)
Dim parameterRemote_Address As New SqlParameter("@Remote_Address", SqlDbType.VarChar, 50)
parameterRemote_Address.Value = Request.Servervariables("REMOTE_HOST")
myCommand.Parameters.Add(parameterRemote_Address)
Now, in order to execute my stored procedure with all of its parameters, I need to open my database
connection, execute my SqlCommand object, and close the connection. This is accomplished
with the following few lines of code (note that this also marks the end of the event handler):
myConnection.Open()
'I use the ExecuteNonQuery method because the stored procedure does
'not return any rows from the database.
myCommand.ExecuteNonQuery()
'Close the database connection
myConnection.Close()
End Sub
Okay, so what do I have so far? I have a form that allows users to rate each photo on a scale of one
to five and also allows them to provide feedback regarding each of the photos,
but I still don't have a graphical representation of the photo's rating.
Introduction
