Tutorial RSS
 
Navigator: Home - Advanced - Creating a Poll with AJAX, SQL and LINQ in VB.NET

Creating a Poll with AJAX, SQL and LINQ in VB.NET

This tutorial will show you how to create an online voting system which will take advantage of AJAX and LINQ to allow users to quickly and easily vote for their favorite Political candidate, and be shown who is currently the front-runner. VB.NET

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!


Looking for the C# Version? Click here!

Looking for more .NET Database Tutorials? Click Here!

Polling and voting systems can be very useful when wanting to survey for data. Visitors tend not to mind giving their opinion on a quick poll, as it is quick and there is no need to reveal any personal information.
In this tutorial, we will be looking at how we can create a poll with a SQL database, LINQ and AJAX.

The first thing we need to do is create the database. We will have a poll on peoples' political views- Obama or McCain. We will create a SQL database with one table and two columns - id and vote. People will be able to submit their votes via a form with a radiobutton list, so once we have our database set up, we can begin on the ASPX form:

<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Literal ID="litResults" runat="server" visible="false"/><br />
Who is your favorite Candidate?<br />
<asp:RadioButtonList ID="radVote" runat="server">
<asp:ListItem>Obama</asp:ListItem>
<asp:ListItem>McCain</asp:ListItem>
</asp:RadioButtonList>
<asp:Button ID="butVote" runat="server" Text="Vote"
onclick="butVote_Click" /><br />
<asp:Label ID="lblStatus" runat="server" /><br />
<asp:Button ID="butResults" runat="server" Text="Show Results"
onclick="butResults_Click" />
</ContentTemplate>
</asp:UpdatePanel>
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="PollDataContext" EnableInsert="True" TableName="tblPolls">
</asp:LinqDataSource>
</form>


Here, you'll notice we have a LinqDataSource. We added this to communicate with our LINQ to SQL Classes, which we add by right-clicking our project in Solution Explorer and choosing Add New Item, LINQ to SQL Classes. We drag our table from Server Explorer to the design view, and then save. VS will create the class and methods for us.
We have a Radio Button List on our page, and two buttons, as well as a label and a literal control. The literal will be used to display the results of the poll, and the label will be used for any validation and errors. The two buttons will be to submit the vote and to read current results.

Notice also that our buttons have been assigned methods on the click events. The first, looks something like this:

Protected Sub butVote_Click(ByVal sender As Object, ByVal e As EventArgs)
If radVote.SelectedItem Is Nothing Then
lblStatus.Text = "Please vote."
Else
countVote(radVote.SelectedItem.ToString())
End If
End Sub


This simple method checks to see if an option has been selected. If it has, we call another method to add the vote to the database:

Protected Sub countVote(ByVal theVote As String)
Try
Dim conn As String = ConfigurationManager.ConnectionStrings("PollConnectionString").ToString()
Dim context As New PollDataContext(conn)

Dim pt As New tblPoll()
pt.vote = theVote
context.tblPolls.InsertOnSubmit(pt)
context.SubmitChanges()

lblStatus.Text = "Thank you for your vote."
readData()
Catch
lblStatus.Text = "Sorry, unable to process request. Please try again."
End Try
End Sub

Try Server Intellect for Windows Server Hosting. Quality and Quantity!


Here, we use a Try...Catch to provide some error prevention. We use LINQ to add the selected option to the database. Finally, we create a method that will read the results from the database:

Protected Sub readData()
Dim conn As String = ConfigurationManager.ConnectionStrings("PollConnectionString").ToString()
Dim context As New PollDataContext(conn)

Dim pt As New tblPoll()

Dim votes = From vote In context.tblPolls _
Select vote

Dim mCount As Integer
Dim oCount As Integer
mCount = 0
oCount = 0

For Each vote In votes
If vote.vote = "McCain" Then
mCount += 1
ElseIf vote.vote = "Obama" Then
oCount += 1
End If
Next vote

Dim theTotal As Double
theTotal = mCount + oCount
Dim mPercent As Double
Dim oPercent As Double
mPercent = (mCount / theTotal) * 100
oPercent = (oCount / theTotal) * 100

litResults.Visible = True
litResults.Text = "Obama: " & oCount & " votes (" & oPercent & "%).<br />"
litResults.Text = litResults.Text & "McCain: " & mCount & " votes (" & mPercent & "%).<br /><br />"
End Sub


Again we use LINQ to make a selection of all the data from the database, then we loop through it and count how many votes there are for each candidate. We also make a simple calculation to work out and display the percentage of votes for each one. We also code the other button, simply calling the readXML method:
Protected Sub butResults_Click(ByVal sender As Object, ByVal e As EventArgs)
readData()
End Sub


The entire code-behind will look like this:
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub butVote_Click(ByVal sender As Object, ByVal e As EventArgs)
If radVote.SelectedItem Is Nothing Then
lblStatus.Text = "Please vote."
Else
countVote(radVote.SelectedItem.ToString())
End If
End Sub

Protected Sub countVote(ByVal theVote As String)
Try
Dim conn As String = ConfigurationManager.ConnectionStrings("PollConnectionString").ToString()
Dim context As New PollDataContext(conn)

Dim pt As New tblPoll()
pt.vote = theVote
context.tblPolls.InsertOnSubmit(pt)
context.SubmitChanges()

lblStatus.Text = "Thank you for your vote."
readData()
Catch
lblStatus.Text = "Sorry, unable to process request. Please try again."
End Try
End Sub

Protected Sub readData()
Dim conn As String = ConfigurationManager.ConnectionStrings("PollConnectionString").ToString()
Dim context As New PollDataContext(conn)

Dim pt As New tblPoll()

Dim votes = From vote In context.tblPolls _
Select vote

Dim mCount As Integer
Dim oCount As Integer
mCount = 0
oCount = 0

For Each vote In votes
If vote.vote = "McCain" Then
mCount += 1
ElseIf vote.vote = "Obama" Then
oCount += 1
End If
Next vote

Dim theTotal As Double
theTotal = mCount + oCount
Dim mPercent As Double
Dim oPercent As Double
mPercent = (mCount / theTotal) * 100
oPercent = (oCount / theTotal) * 100

litResults.Visible = True
litResults.Text = "Obama: " & oCount & " votes (" & oPercent & "%).<br />"
litResults.Text = litResults.Text & "McCain: " & mCount & " votes (" & mPercent & "%).<br /><br />"
End Sub

Protected Sub butResults_Click(ByVal sender As Object, ByVal e As EventArgs)
readData()
End Sub
End Class




Looking for the C# Version? Click here!

Looking for more .NET Database Tutorials? Click Here!

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.


Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!

411asp.net123aspxDotNetFreaksServer Intellect