Contents
This tutorial walks you through the process of building a rating system in ASP.NET, Visual Basic .NET and SQL Server that allows your users to rate an article, and then see the ratings graphically displayed on the article.
If you publish articles on the Internet, you might want to capture user feedback to get an indication of the article's usefulness to your target user community. You'll need to control for bias to prevent multiple ratings from the same person, and you'll probably want to provide users with visual feedback on the overall rating of the article.
In this walkthrough, you'll learn how to develop and implement a basic content rating system in ASP.NET and SQL Server, using Visual Basic .NET as our programming language. You'll first learn how to setup the database table that you'll use to capture user ratings. Next, you'll learn how to build the graphical user interface components that display the article ratings. Lastly, you'll learn how to build the necessary logic, line by line. The rating system that you will study is the exact system in use by StephanBren.Com. The code in this system is modular: it looks for the unique ID of an article. Once you create it for one article, all you need to do is copy and paste it into each subsequent article, changing just the article ID variable. This is version 1.0 of this rating system. Version 2 will implement the rating system as a class module, for further code compactness and reusability. This article assumes familiarity with SQL Server, though the database concepts discussed here apply equally to Oracle as well.
You can also implement version 1 of this system using a Microsoft Access backend. However, there are certain security issues involving opening Access database files from within ASP.NET that are beyond the scope of this article and will be covered in a subsequent article.
Before you can begin developing, you first need to define the requirements that your project should meet. For this project, the requirements fall into three categories: data, interface, and business rules. The data requirements are defined by identifying what specific user data you want to capture and display. The interface requirements are defined by how you want to enable the user to enter data and how you then want to display back to the user the results of data entry. The business rule requirements are defined by what you want the rating system to do under different user scenarios. Let's get started by defining the data requirements.
The article can be uniquely identified by creating an identification system for your articles. For example, you may have noticed that each article at StephanBren.Com has a unique ID number. This number is stored as a variable in the server-side copy of the article's web page. The article ID system begins at 100001. Next, you need to define a numeric rating system - this site uses a five number range, from 1 to 5. An odd number range is used to allow neutral ratings, which for a 1-5 range would be 3. Lastly, data item needs to be captured from the user that uniquely identifies that user. You need this data item in order to screen and prevent multiple ratings from the same user. Attempting to uniquely identify anonymous users connecting over the Internet is no easy task, and no foolproof method exists, unless you want only registered users to be able to rate your articles - registered users having unique IDs, by definition. One item that is available to you is the user's IP address, which you can access through the HTTP request object. This data element is not foolproof either, since for telephone dial-up users, this IP changes each and every time the user dials into his ISP and establishes an Internet connection. You'll have to make a determination on how tightly you want to control and filter user ratings. For this site, only the user's IP address is captured.
Thus, this basic rating system is designed to capture the following data items:
ArticleID: A unique identification that allows you to properly identify the article being rated.
rating: A number corresponding to the rating (from 1 to 5) given to the article.
ip: a data item that (for the most part) uniquely identifies the user who rated the article.
User interface requirements are determined by how you want to capture and display ratings information. The easiest and the most common approach is to capture the rating information by using a series of radio buttons, with each radio button corresponding to a particular rating number. After choosing a specific rating, the user clicks a Submit button to post the response back to the web server for processing. Since we have determine a rating range of 5, each article must display a series of five radio buttons in a single group, along with a submit button - additionally, some indication will have to be provided to the user as to the direction of the rating scale, from poor to best, or from best to poor (read from left to right). To display the rating data, you have basically two options: using text, such as actual numbers, or using images. The text approach is the simplest, but least appealing. The visual approach is the most common. In either approach, you need to give users some sense of the relative proportion of positive to negative ratings.
For example, with a text-based approach, you might want to display the average rating of the article along with the total number of ratings of that article to date. For a visual approach, you could display a series of star graphics, with five star (corresponding to our rating range) being the highest rating and one star the lowest. Or, you could display a bar graph with the length of the bar graph proportional to the average rating.
This article rating system uses two bar images to represent the average rating, along with a text-based number to indicate the total number of ratings of the article to date. The two bar images provide a relative sense of the overall rating: one bar image represents positive ratings and the other represents negative ratings. The total length of the two bar images remains static - only the individual lengths change. This approach provides a quick and intuitive means of displaying ratings information to the user, and is the approach used by stephanbren.com. You may also want to provide additional information to the user, such as alerting the user that he may be the first one to rate the article, or that he has already rated this article in the past and thus is not able to rate the article again.
The last thing to consider with regard to the rating system is where to display the rating information. If you display it at the top of the article web page, you can guarantee that most users will see it immediately, and this makes it convenient for users to quickly use. By placing the rating display at the top of the article page, you provide users with quick, immediate information on article rating. If you place it at the end of the article web page, some users may miss it, and the rating system becomes a little less convenient to use. You may also decide to place your rating system somewhere in between the top and bottom of the article web page. Again, you'll need to keep in mind the browser heights and average monitor resolutions: these limitations may cause your rating display to not be visible when the user initially connects to your article. For stephanbren.com, all the rating displays are placed at the top of each article page. This placement ensures immediate visibility and usability of the rating tools by stephanbren.com users.
To build the business rule requirements, we need to carefully describe the process that we envision taking place when the user connects to the article and/or attempts to rate the article using our rating system. This can be done by building a rough use case of the rating system as follows:
- The user connects to the article.
- The user views the current article rating.
- If the article hasn't yet been rated, the user sees a message alerting him to this and perhaps encouraging him to be the first to rate this article; and the rating system displays the equivalent of a neutral or unrated article.
- If the article has already been rated, the user sees the rating system displaying the visual equivalent of whatever the average rating is to date.
- The user selects a specific rating and clicks Submit.
- The article rating is refreshed; the user remains at the same page (there is no referral to another page).
- If the user has rated this article before, the user sees a messaging alerting him to this; and he is unable to rate the article.
- If the user hasn't rated the article before, the user sees the display updated to include his rating.
This describes the process that the user experiences while interacting with the rating system. This use case provides the framework on which the actual code is built for the rating system.
This completes the process of defining the initial requirements that our rating system has to meet. Let's now perform the task of actually developing the rating system.
At this point, you have a pretty good basis on which to begin development. Having defined the requirements that the rating system has to meet, it remains to actually implement them. The first development task is to design the backend, or database, for the rating system. The backend for this rating system can be any data store that you choose. For Stephanbren.com uses a SQL Server backend, so our next task involves adding a new table to the existing stephanbren.com database. Once the database has been developed, the next task will be to identify the user interface components that we'll need to capture from and display to the user the article's rating information. Lastly, we'll perform the task of developing the necessary code to support the rating system.
Given the data requirements identified previously, you'll first need to create a table in SQL Server having the following suggested design:
The image shows the actual table design used by stephanbren.com to store rating information. It includes the three data elements that were defined previously, and it includes a primary key used to uniquely identify each and every rating entered into the table.
ratingID: the primary key uniquely identifying every rating. This number is generated automatically by the database, every time a new record is inserted into the table.
rating: the numeric rating, from 1 to 5, submitted to the rating system by the user.
ip: the IP address of the user, as provided by the HTTP request object to the rating system.
itemID: the unique ID of the article.
To simply retrieve and calculate the rating for a specific article when the user first connects, one SQL operation is performed. This operation counts how many records in the table have the same itemID as the ID of the article that the user is connected to:
SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating
WHERE Itemid= [the article's ID]
To determine whether the user has already rated a specific article, a single SQL operations is performed. This operation selects all records having the same itemID as the ID of the article the user is connected to and the same IP as the IP of the user:
SELECT COUNT(*) As RatingCount FROM tbl_Rating
WHERE Itemid= [the article's ID] AND ip = [the user's ip]
Lastly, to insert a new rating into the table, the following SQL INSERT operation is performed:
INSERT INTO tbl_rating (rating, ip, itemID)
VALUES ( [the user's rating], [the user's ip], [the article's ID]);
These three SQL statements are all the rating system needs to get the information for displaying the rating, to find out whether a user has already rated the article before, and to insert the user's rating.
The next task is to build the user interface for the rating system. As previously mentioned, the rating system will need five radio buttons, a command button to post the data to the web server, some images, and some labels for displaying text information. These interface items are provided by the ASP.NET radiobuttonlist, button, image, and label controls, respectively.
To build the user interface for the system, it's convenient to group all elements in a table, for maximum portability, like so:
| [visual rating display] / [label for rating count] |
| "poor" |
[list of radio buttons] |
"great!" |
[button] |
| [label for user feedback] |
Those items in "[]" represent ASP.NET web controls that need to be added to the table, and items in quotes "" represent simple text. Thus, the web controls are:
- two image controls,
imgRatingApproval and imgRatingBlank.
- two label controls,
lblRatingCount and lblRating.
- one radiobuttonlist control,
rblRating.
- and one button control,
btnRating.
The final interface will look similar to the following, at design time and at run time:
and the markup for the interface will look like this:
<table width="0%" border="0" class="basic11pt">
<tr>
<td colspan="4">Rating:
<asp:image ID="imgRatingApproval" runat="server"></asp:image>
<asp:image ID="imgRatingBlank" runat="server"></asp:image>
<asp:label ID="lblRatingCount" runat="server"></asp:label>
</td>
</tr>
<tr>
<td>poor</td>
<td><asp:radiobuttonlist RepeatLayout="Flow"
RepeatDirection="Horizontal"
ID="rblRating"
runat="server">
<asp:listitem Value="1">1</asp:listitem>
<asp:listitem Value="2">2</asp:listitem>
<asp:listitem Value="3" selected="True">3</asp:listitem>
<asp:listitem Value="4">4</asp:listitem>
<asp:listitem Value="5">5</asp:listitem>
</asp:radiobuttonlist></td>
<td>great</td>
<td><asp:button ID="btnRating"
OnClick="btnRating_Click"
runat="server"
Text="Rate It!" /></td>
</tr>
<tr>
<td colspan="4"><asp:label ID="lblRating"
runat="server"></asp:label></td>
</tr>
</table>
Tip: To make sure that your two rating images, imgRatingApproval and imgRatingBlank, are right next to each other when viewed in a browser, make sure that the ASP.NET markup for these two images are also adjacent. They are on separate lines above in order to make the page look nicer. In actuality, you'll want to place both the image controls on the same line, without any space in between.
You'll note that, as mentioned previously, all the interface elements are grouped within a table. This makes the markup repeatable and easily portable, using cut-and-paste methods. You'll use this same bit of markup for every article for which you want to implement your rating system.
This completes all the development for the user interface, let's move onto building the business logic.
The final development task is to build the implementation logic for the rating system. To build this implementation logic, let's first review the process outline developed previously, filling out the outline framework with some pseudo code:
- The user connects to the article.
- Build the SQL
SELECT statement.
- Connect to the database.
- Retrieve the rating sum and count for the article.
- Calculate the rating.
- Close the connection.
- The user views the current article rating.
- If the article hasn't yet been rated, the user sees a message alerting him to this and perhaps encouraging him to be the first to rate this article; and the rating system displays the equivalent of a neutral or unrated article.
- Set the image control width properties to the same width.
- Set
lblRatingCount.text to display " / 0".
- Set
lblRating.text to display "be the first to rate this article".
- Set other display properties appropriately.
- If the article has already been rated, the user sees the rating system displaying the visual equivalent of whatever the average rating is to date.
- Set
imgRatingApproval.width to the average rating x some multiplier factor (to increase the displayed width).
- Set
imgRatingBlank.width to the total width - imgRatingApproval.width.
- Set
lblRatingCount.text to the count.
- Set
lblRating.text to nothing
- The user selects a specific rating and clicks Submit.
- Build the SQL statement to determine whether the user has rated the article previously.
- Connect to the database.
- Retrieve the resulting record count.
- If the user hasn't rated the article before, build the SQL
INSERT statement and execute the statement.
- Close the existing connection.
- Open a new connection.
- Retrieve the rating sum and count for the article.
- Calculate the rating.
- Set
imgRatingApproval.width to the average rating x some multiplier factor (to increase displayed width).
- Set
imgRatingBlank.width to the total width - imgRatingApproval.width.
- Set
lblRatingCount.text to the count.
- Set
lblRating.text to nothing.
- Close the connection.
- The article rating is refreshed; the user remains at the same page (there is no referral to another page).
- If the user has rated this article before, the user sees a messaging alerting him to this; and he is unable to rate the article.
- Set
lblRating.text to "You have rated this article previously"
- Set other control properties appropriately.
- If the user hasn't rated the article before, the user sees the display updated to include his rating.
This outline is now ready for development. Now let's develop this outline into code.
Items 1 and 2 above are handled when the user first connects to the web page. You want this code to run whenever the user connects to the web page, whether from a referral, entering the URL directly into the browser, or even refreshing the page. To run the code whenever the users connect to the web page, driver the code using the article web page's Load event. Thus, insert the the following code into your web page:
<script runat="server">
Private intPageID as Long = <your article ID here>
Sub Page_Load(Src As Object, E As EventArgs)
End Sub
</script>
The intArticleID variable contains the unique ID assigned to this article. Once you build the rating system code, you can copy and paste it into each article that you publish, only changing the article ID for each web page. Now, you only want this code to run if the user is simply connecting to the page, i.e., the user is not attempting to rate the article and POST the results back, which is called a PostBack in ASP.NET. Thus, you want to check the IsPostback property of the ASP.NET page object before running the code. Thus, revise the code as follows:
<script runat="server">
Private intPageID as Long = <your article ID here>
Sub Page_Load(Src As Object, E As EventArgs)
If Not IsPostback Then
End If
End Sub
</script>
Now let's build the data connection logic. For global constants that you'll use over and over again, ASP.NET provides an extensible configuration system that allows you to define custom application configuration settings at deployment that you can easily change later. These configuration settings are conveniently stored in the application's root web.config file, located in the root folder. The configuration information is stored in standard XML, which you can edit using any text editor, as this file is no more than an ASCII text file. Once you define an application configuration setting that you want to use throughout your application (these settings are scoped as Public), you can access them from your application using the ConfigurationSettings.AppSettings property of the Configuration object, like so (in Visual Basic .NET):
System.Data.SqlClient.sqlConnection(_
System.Configuration.ConfigurationSettings.AppSettings("<your key name>"))
To create a application's settings in this manner, simply open any text editor, and then add the following text:
<!---->
<configuration>
<appSettings>
<add key="MyConnectionString" value="your SQL Server connection string "/>
</appSettings>
</configuration>
Save this file as web.config and place it in the root folder of your website. You now have a basically global constant defined for your website. Make sure to replace <your key name> with the name that you want to assign to your connection string.
Note: You'll need to obtain your actual connection string from your ISP. The typical connection string will include the following elements:
- data source
- initial catalog
- user ID
- password
- persist security info
- packet size
Putting all these elements into a string would result in something similar to the following:
value="data source=[the URL to your SQL Server database];
initial catalog=[your database name];user id=[your username];
password=[your password];persist security info=[set to True];
packet size=[default packet size is 4096, but use larger one
if your network supports it]"
Now, let's build the data access logic. We'll need Connection, Command, and DataReader objects. Since this walkthrough is using a Microsoft SQL Server backend, we'll use the sqlClient variations of these, or SqlConnection, SqlCommand, and SqlDataReader, respectively. These objects are located in the System.Data.SqlClient namespace.
Go back to your Page_Load event, and define your variables and implement your data logic as so:
<script runat="server">
Private intPageID As Long = [your article ID here]
Sub Page_Load(Src As Object, E As EventArgs)
Dim intApprovalWidth, intBlankWidth as Integer
Dim strQuery as string
Dim strCon As String
Dim conMyConnection as New System.Data.SqlClient.sqlConnection()
Dim cmdMyCommand as New System.Data.SqlClient.SqlCommand()
Dim dtrMyDataReader as System.Data.SqlClient.sqlDataReader
If Not IsPostback Then
strCon = System.Configuration.ConfigurationSettings.AppSettings(_
"MyConnectionString")
conMyConnection.ConnectionString = strCon
strQuery = "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
strQuery += "FROM tblMyRatings WHERE Itemid=" & intArticleID
conMyConnection.Open()
cmdMyCommand.Connection = conSb
cmdMyCommand.CommandType = System.Data.CommandType.Text
cmdMyCommand.CommandText = strQuery
dtrMyDataReader = cmdMyCommand.ExecuteReader()
dtrMyDataReader.Read()
[code for building rating display goes here]
dtrMyDataReader.Close()
conMyConnection.Close()
End If
End Sub
</script>
Note that the Read method of the DataReader is called once - you need to do this. When the DataReader object is initially populated with the resultset returned from the database, its cursor is initially set prior to the first record. Calling the Read method once moves the cursor to the first record. Notice too that the Close method of the connection object is not called until after all data has been read from the DataReader. This is due to the data access method being a connected access method. Were you to close the connection before retrieving data from the DataReader, you would lose all the data currently in the DataReader.
The next development task is to implement a decision structure for determining what to display: whether the article has or hasn't yet been rated. Our decision structure will check the RatingCount returned by the query: if it's zero, that means no ratings have yet been submitted. Otherwise ratings have been submitted. For the no rating case, set the images to be equal in width and also display an invitational message to users to be the first to rate the article. Otherwise, if the article has been rated, calculate the average rating, and then set the image properties appropriately.
<script runat="server">
Private intPageID As Long = [your article ID here]
Sub Page_Load(Src As Object, E As EventArgs)
Dim intApprovalWidth, intBlankWidth as Integer
Dim strQuery as string
Dim strCon As String
Dim conMyConnection as New System.Data.SqlClient.sqlConnection()
Dim cmdMyCommand as New System.Data.SqlClient.SqlCommand()
Dim dtrMyDataReader as System.Data.SqlClient.sqlDataReader
If Not IsPostback Then
strCon = System.Configuration.ConfigurationSettings.AppSettings(_
"MyConnectionString")
conMyConnection.ConnectionString = strCon
strQuery = "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
strQuery += "FROM tblMyRatings WHERE Itemid=" & intArticleID
conMyConnection.Open()
cmdMyCommand.Connection = conMyConnection
cmdMyCommand.CommandType = System.Data.CommandType.Text
cmdMyCommand.CommandText = strQuery
dtrMyDataReader = cmdMyCommand.ExecuteReader()
dtrMyDataReader.Read()
If dtrKb("RatingCount") = 0 Then
lblRatingCount.Text = " / " & dtrKb("RatingCount")
intApprovalWidth = dtrKb("RatingSum")/dtrKb("RatingCount")*15
intBlankWidth = 75 - intApprovalWidth
imgRatingApproval.Width = _
System.web.ui.webcontrols.unit.pixel(intApprovalWidth)
imgRatingBlank.Width = _
System.web.ui.webcontrols.unit.pixel(intBlankWidth)
Else
lblRatingCount.Text = "/0"
lblRating.Text = "Be the first to rate it!"
End If
dtrMyDataReader.Close()
conMyConnection.Close()
End If
End Sub
</script>
Note the factor of 15 used to increase the rating number. This is done to increase the width of the images. Otherwise, your images won't be any wider than 5 pixels, corresponding to the 1-5 range of the numbers returned by the rating system. The width of the Approval image is set to this product. The width of the blank image (or disapproval) is set to the total width of both the images minus the approval width. The total width of both the images is equal to the product of the maximum rating and the factor, or 75. This total length remains constant no matter what the ratings are. This way our rating system provides consistent information, a relative rating ratio, regarding the article. This completes all the development of Sections 1 and 2 of our code outline above. This code runs whenever a user initially connects to the page. It is not run if the user is posting back a rating to the web page. Let's now develop the business logic for allowing the user to post a new rating.
Previously, we defined the user interface, developing the markup necessary for building and displaying the UI. Recall that one of these UI elements is the button element:
<asp:button ID="btnRating" OnClick="btnRating_Click"
runat="server" Text="Rate It!" />
Within the script tags containing your Page_Load event handler, add the following procedure definition:
<script runat="server">
.
.
.
Sub btnRating_Click(Src As Object, E As EventArgs)
End Sub
</script>
Into this event handler, we'll need to repeat the data access logic that we developed previously. The button click event handler with three blocks of code:
- In the first block, the user's IP address is captured and then the rating table is queried to determine whether the user has rated this article previously.
- The second block is only executed if the user hasn't previously rated the article. In this block, the query for inserting a new rating is created and then executed against the database.
- The third and the final block essentially repeats the actions of the
Page_Load code, updating the display with the article rating information.
Let's start by declaring the variables used in this procedure:
<script runat="server">
.
.
.
Sub btnRating_Click(Src As Object, E As EventArgs)
Dim intApprovalWidth, intBlankWidth as Integer
Dim strQuery as string
Dim strCon As String
Dim conMyConnection as New System.Data.SqlClient.sqlConnection()
Dim cmdMyCommand as New System.Data.SqlClient.SqlCommand()
Dim dtrMyDataReader as System.Data.SqlClient.sqlDataReader
End Sub
</script>
Then build the code for the first block, which determines whether the user has rated the article previously. To do this, we'll need to declare and set a new object that helps us retrieve the user's IP address from the HTTP request object. We also can declare a few temporary storage variables:
<script runat="server">
.
.
.
Sub btnRating_Click(Src As Object, E As EventArgs)
Dim intApprovalWidth, intBlankWidth as Integer
Dim strSelectQuery, strInsertQuery as string
Dim strCon As String
Dim conMyConnection as New System.Data.SqlClient.sqlConnection()
Dim cmdMyCommand as New System.Data.SqlClient.SqlCommand()
Dim dtrMyDataReader as System.Data.SqlClient.sqlDataReader
Dim MyHttpAppObject As System.Web.HttpContext = _
System.Web.HttpContext.Current
Dim strRemoteAddress as String
Dim intSelectedRating, intCount As Integer
strRemoteAddress = Cstr(MyHttpApp.Request.UserHostAddress)
strSelectQuery = "SELECT COUNT(*) As RatingCount "
strSelectQuery += "FROM tbl_Rating WHERE Itemid=" & intArticleID "
strSelectQuery += " AND ip =
strCon = System.Configuration.ConfigurationSettings.AppSettings(_
"MyConnectionString")
conMyConnection.ConnectionString = strCon
conMyConnection.Open()
cmdMyCommand.Connection = conMyConnection
cmdMyCommand.CommandType = System.Data.CommandType.Text
cmdMyCommand.CommandText = strSelectQuery
intCount= cmdMyCommand.ExecuteScalar()
conMyConnection.Close()
If intCount = 0 Then
Else
End If
End Sub
</script>
This block is now able to uniquely identify the user (for the most part), then look up this user's IP in the database. If a match is found for the current article, then the user's rating is not inserted into the database. Otherwise, it is. Let's now insert the code for this decision structure:
<script runat="server">
.
.
.
Sub btnRating_Click(Src As Object, E As EventArgs)
Dim intApprovalWidth, intBlankWidth as Integer
Dim strSelectQuery, strInsertQuery as string
Dim strCon As String
Dim conMyConnection as New System.Data.SqlClient.sqlConnection()
Dim cmdMyCommand as New System.Data.SqlClient.SqlCommand()
Dim dtrMyDataReader as System.Data.SqlClient.sqlDataReader
Dim MyHttpAppObject As System.Web.HttpContext = _
System.Web.HttpContext.Current
Dim strRemoteAddress as String
Dim intSelectedRating, intCount As Integer
strRemoteAddress = Cstr(MyHttpApp.Request.UserHostAddress)
strSelectQuery = "SELECT COUNT(*) As RatingCount "
strSelectQuery += "FROM tbl_Rating WHERE Itemid=" & intArticleID "
strSelectQuery += " AND ip =
strCon = System.Configuration.ConfigurationSettings.AppSettings(_
"MyConnectionString")
conMyConnection.ConnectionString = strCon
conMyConnection.Open()
cmdMyCommand.Connection = conMyConnection
cmdMyCommand.CommandType = System.Data.CommandType.Text
cmdMyCommand.CommandText = strSelectQuery
intCount= cmdMyCommand.ExecuteScalar()
conMyConnection.Close()
If intCount = 0 Then
strInsertQuery = "INSERT INTO tbl_rating (rating, ip, itemID) "
strInsertQuery += "VALUES ("
strInsertQuery += intSelectedRating & ", '"
strInsertQuery += strRemoteAddress & "', "
strInsertQuery += intArticleID & "); "
cmdMyCommand.CommandText = strInsertQuery
conMyConnection.Open()
cmdMyCommand.ExecuteNonQuery()
conMyConnection.Close()
Else
lblRating.Text = "You've already rated this article"
End If
End Sub
</script>
With this code, we have given our rating system the ability to detect whether a user has rated the article previously. For the last block of code, we need to simply repeat the code that is in the Page_Load procedure, since all it is doing is essentially refreshing the display. This last code listing displays the entire code necessary for implementing the btnRating_Click procedure:
<script runat="server">
.
.
.
Sub btnRating_Click(Src As Object, E As EventArgs)
Dim intApprovalWidth, intBlankWidth as Integer
Dim strSelectQuery, strInsertQuery as string
Dim strCon As String
Dim conMyConnection as New System.Data.SqlClient.sqlConnection()
Dim cmdMyCommand as New System.Data.SqlClient.SqlCommand()
Dim dtrMyDataReader as System.Data.SqlClient.sqlDataReader
Dim MyHttpAppObject As System.Web.HttpContext = _
System.Web.HttpContext.Current
Dim strRemoteAddress as String
Dim intSelectedRating, intCount As Integer
strRemoteAddress = Cstr(MyHttpApp.Request.UserHostAddress)
strSelectQuery = "SELECT COUNT(*) As RatingCount "
strSelectQuery += "FROM tbl_Rating WHERE Itemid=" & intArticleID "
strSelectQuery += " AND ip =
strCon = System.Configuration.ConfigurationSettings.AppSettings(_
"MyConnectionString")
conMyConnection.ConnectionString = strCon
conMyConnection.Open()
cmdMyCommand.Connection = conMyConnection
cmdMyCommand.CommandType = System.Data.CommandType.Text
cmdMyCommand.CommandText = strSelectQuery
intCount= cmdMyCommand.ExecuteScalar()
conMyConnection.Close()
If intCount = 0 Then
strInsertQuery = "INSERT INTO tbl_rating (rating, ip, itemID)"
strInsertQuery += "VALUES ("
strInsertQuery += intSelectedRating & ", '"
strInsertQuery += strRemoteAddress & "', "
strInsertQuery += intArticleID & "); "
cmdMyCommand.CommandText = strInsertQuery
conMyConnection.Open()
cmdMyCommand.ExecuteNonQuery()
conMyConnection.Close()
Else
lblRating.Text = "You've already rated this article"
End If
strSelectQuery = _
"SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount"
strSelectQuery += "FROM tbl_Rating WHERE Itemid=" & intPageID
conMyConnection.Open()
cmdMyCommand.CommandText = strSelectQuery
dtrMyDataReader = cmdMyCommand.ExecuteReader()
dtrMyDataReader.Read()
lblRatingCount.Text = " / " & dtrKb("RatingCount")
intApprovalWidth = _
dtrKb("RatingSum")/dtrKb("RatingCount")*15
dtrMyDataReader.Close()
conMyConnection.Close()
intBlankWidth = 75 - intApprovalWidth
imgRatingApproval.Width = _
System.web.ui.webcontrols.unit.pixel(intApprovalWidth)
imgRatingBlank.Width = _
System.web.ui.webcontrols.unit.pixel(intBlankWidth)
End Sub
</script>
After implementing this code for the first time in your web page article, you'll need to perform testing to make sure that you have cleaned up typos and other grammar problems. The best way to implement this rating system is to do it in sections, and then test:
- First add the user interface markup, then save the article web page to your web server, and connect to it. You should see something like a rating interface similar to that shown earlier in this article. Get the interface nailed down, and then move onto the database.
- Second, create the table in SQL Server, as displayed in the figure above. This will be a simple task. You'll need this table later. Enter some test data into this table, such as a few ratings that contain the article ID, and IP and a rating.
- Third, add script tags to your web page, making sure that you give the page the ASPX file ending. In the script tags, add the page load event handler and all its code. Focus your testing on this block of code. Once you get this working, all the rest of your development efforts will proceed much more smoothly. This is because, if you can get the data access logic working for one block of code, the rest is cut'n paste, for the most part. If you get this block of code working, you have demonstrated successful linkage of your code with your interface and your database. Once you get the page load event code working right every time, move onto the rest of the code.
In this article, you have learned how to implement a simple rating system for an ASP.NET web page, using Visual Basic .NET and SQL Server. The rating system graphically displays the relative proportion of positive and negative ratings, and it displays the total number of ratings submitted about the article numerically. Users can rate the article only once, and some simple screen methods are implemented to prevent multiple ratings from the same user. The rating system can be completely implemented using your favorite HTML editor. However, you need to have access to Microsoft SQL Server.
- 03/20/04: created.
- 03/24/04: revised overall discussions; improved images.
- 04/17/04: minor reformatting.
- 07/15/04: Added table of contents; minor reformatting.
- 07/22/04: removed shell markup; migrated to dotnetnuke portal.
- 08/24/04: Migrated to DNN2.X.
Happy coding!
