counting votes from sql and displaying it using asp.net

Question

1.00/5 (1 vote)

See more:

i am making an android app for mobile voting. to support that i have made some .aspx pages. now i am stuck at a point where i have to make a page in which vote count along with the contender name and the party name should be shown. i have a table RegisterContender in sql database which consists of the ContenderName and PartyName. i also have a table castVote which consists of the ContenderName for which the voter has voted. Now what i want to do is to count the number of ContenderName(s) for which the voters have voted and display it in the form:- Name PartyName TotalVotes

hep me asap.. thank you

String _view = String.Format("select count(*) as numVotes,ContenderName from castVote where ContenderName IS NOT NULL group by ContenderName order by numVotes desc");

SqlCommand cmd = new SqlCommand(_view, con);
SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{

String _ans = String.Format(dr["numvotes"].ToString()+" "+dr["ContenderName"].ToString());

Response.Write(_ans);
}

table structure

create table RegisterContender
(ContenderId int identity(1,1) Primary Key Not Null,
Name varchar(100),
PartyName varchar(100),
History varchar(1000),
Future_Proposals varchar(500),
Region varchar(150)
)

create table castVote
(VoterLogin varchar(100),ContenderName varchar(100))

the relationship between these two is that the ContenderName from castVote is same as the Name in the RegisterContender. PartyName is also present in the RegisterContender.

Posted 15-Jul-13 8:53am

Jonty Kapoor

Updated 15-Jul-13 20:42pm

v2

Add a Solution

Comments

OriginalGriff 15-Jul-13 15:02pm

And? What have you tried? Where are you stuck?

Jonty Kapoor 16-Jul-13 2:42am

I have updated my details you can see them.

ZurdoDev 15-Jul-13 15:14pm

Where are you stuck exactly?

Jonty Kapoor 16-Jul-13 2:43am

I have updated my details you can see them.

Reza Alipour Fard 15-Jul-13 15:16pm

You must show your database structure for that 2 tables. I think you need only a group by and count vote. Please present your tables structure.

Jonty Kapoor 16-Jul-13 2:43am

I have updated my details you can see them.

Reza Alipour Fard 16-Jul-13 5:56am

I can't understand your value in your table. I think it's better you show some row data as sample data.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Answer 1 · 2013-07-15T21:26:00

Solution 1

Try this code.

SQL

select count(*) as numVotes,cv.ContenderName,rc.PartyName
from castVote cv inner join RegisterContender rc on cv.ContenderName = rc.Name
where ContenderName IS NOT NULL
group by ContenderName order by numVotes desc

Posted 15-Jul-13 21:26pm

ArunRajendra

Comments

Jonty Kapoor 16-Jul-13 3:49am

it throws an exception

System.Data.SqlClient.SqlException (0x80131904): Column 'RegisterContender.PartyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ASP.checkresult_aspx.Page_Load(Object sender, EventArgs e) in c:\Users\Namit\Documents\Visual Studio 2010\WebSites\trail\CheckResult\CheckResult.aspx:line 25

Raja Sekhar S 16-Jul-13 4:39am

The Error is simple... check this link...
http://msdn.microsoft.com/en-us/library/ms177673.aspx..
U Can use Group by PartyName,ContenderName or if only one candidate exists for a Party.. u can use min(PartyName) and Group by ContendentName

Jonty Kapoor 16-Jul-13 7:19am

thanks it worked i used Group By ContenderName , PartyName... and it worked.. thanks

Raja Sekhar S 16-Jul-13 7:22am

You are Welcome...