Hi Thuyai,
Check this example. Now please take its reference and implement in your code.
I have made use of the following tables with the schema as follows.
SQL
CREATE TABLE PollQuestions
(
PollId INT PRIMARY KEY,
Question VARCHAR(100)
)
GO
INSERT INTO PollQuestions VALUES(1,'What is your favorite color?')
GO
CREATE TABLE PollOptions
(
PollId INT,
PollOptionId INT,
PoleOption VARCHAR(50),
CONSTRAINT pk_PollOptionId PRIMARY KEY (PollId,PollOptionId),
CONSTRAINT fk_PollId FOREIGN KEY (PollId) REFERENCES PollQuestions(PollId)
)
GO
INSERT INTO PollOptions VALUES(1,1,'Red')
INSERT INTO PollOptions VALUES(1,2,'Green')
INSERT INTO PollOptions VALUES(1,3,'Yellow')
GO
CREATE TABLE PollAnswers
(
PollAnswerId INT PRIMARY KEY IDENTITY,
PollId INT,
PollAnswers INT,
CONSTRAINT fk_PollAnswerId FOREIGN KEY (PollId) REFERENCES PollQuestions(PollId)
)
GO
CREATE PROCEDURE InsertPollAnswers
@PollId INT,
@PollAnswer INT
AS
BEGIN
INSERT INTO PollAnswers VALUES(@PollId,@PollAnswer)
END
GO
CREATE PROCEDURE PollCount
@PollId INT
AS
BEGIN
SELECT PollOptions.PoleOption,COUNT(PollAnswers.PollAnswerId) 'Polls Count'
FROM PollAnswers
INNER JOIN PollOptions ON PollOptions.PollID = PollAnswers.PollID
AND PollOptions.PollOptionId = PollAnswers.PollAnswers
WHERE PollAnswers.PollID = @PollId
GROUP BY PollAnswers,PollOptions.PoleOption
END
HTML
<div>
<asp:Label ID="lblPollQuestion" runat="server" />
<asp:RadioButtonList ID="rblPollOptions" runat="server" OnSelectedIndexChanged="OnOptionChanged"
AutoPostBack="true">
</asp:RadioButtonList>
<asp:Repeater runat="server" ID="rptPollCount">
<HeaderTemplate>
<table>
<thead>
<th>
Vote for
</th>
<th>
Vote Count
</th>
</thead>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label ID="lblOptions" Text='<%# Eval("PoleOption") %>' runat="server" />
</td>
<td>
<asp:Label ID="lblCount" Text='<%# Eval("Polls Count") %>' runat="server" />
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</div>
Namespaces
C#
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
VB.Net
Imports System.Data.SqlClient
Imports System.Data
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
lblPollQuestion.Text = PopulatePollQuestions();
PopulatePollOptions();
rptPollCount.DataSource = PopulatePollCount(1);
rptPollCount.DataBind();
}
}
protected void OnOptionChanged(object sender, EventArgs e)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("InsertPollAnswers", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PollId", 1);
cmd.Parameters.AddWithValue("@PollAnswer", rblPollOptions.SelectedValue);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}
private string PopulatePollQuestions()
{
object question = "";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT Question FROM PollQuestions WHERE PollId = 1", con))
{
con.Open();
question = cmd.ExecuteScalar();
con.Close();
}
}
return question.ToString();
}
private DataTable PopulatePollOptions()
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT PollId,PollOptionId,PoleOption FROM PollOptions WHERE PollId = 1", con))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
rblPollOptions.DataSource = dt;
rblPollOptions.DataTextField = "PoleOption";
rblPollOptions.DataValueField = "PollOptionId";
rblPollOptions.DataBind();
}
}
}
return dt;
}
private DataTable PopulatePollCount(int pollId)
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("PollCount", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PollId", pollId);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
return dt;
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
lblPollQuestion.Text = PopulatePollQuestions()
PopulatePollOptions()
rptPollCount.DataSource = PopulatePollCount(1)
rptPollCount.DataBind()
End If
End Sub
Protected Sub OnOptionChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("InsertPollAnswers", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PollId", 1)
cmd.Parameters.AddWithValue("@PollAnswer", rblPollOptions.SelectedValue)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Response.Redirect(Request.Url.AbsoluteUri)
End Sub
Private Function PopulatePollQuestions() As String
Dim question As Object = ""
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT Question FROM PollQuestions WHERE PollId = 1", con)
con.Open()
question = cmd.ExecuteScalar()
con.Close()
End Using
End Using
Return question.ToString()
End Function
Private Function PopulatePollOptions() As DataTable
Dim dt As DataTable = New DataTable()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT PollId,PollOptionId,PoleOption FROM PollOptions WHERE PollId = 1", con)
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
da.Fill(dt)
rblPollOptions.DataSource = dt
rblPollOptions.DataTextField = "PoleOption"
rblPollOptions.DataValueField = "PollOptionId"
rblPollOptions.DataBind()
End Using
End Using
End Using
Return dt
End Function
Private Function PopulatePollCount(ByVal pollId As Integer) As DataTable
Dim dt As DataTable = New DataTable()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("PollCount", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PollId", pollId)
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
da.Fill(dt)
End Using
End Using
End Using
Return dt
End Function
Screenshot
