I am currently creating a voting system whereby existing users can vote on whether other entries are valid or not (upvote/Downvote) thru a sql dataset table.
Each user who is registered gets a customer number and the user needs a customer number to cast a vote. When the user casts a vote their customer number is recorded in the entry and the sql query updates the score to either add 1 to upvote or 1 to downvote. A unique constraint is applied to the voters ID on each entry voted to prevent a double vote and a user must have a customer number to vote.
My Code below attempts to do this however it always seems to end up with the message box saying "error, you cannot vote until you provide a valid customer number in the Textbox". All help is appreciated, thanks a lot!
protected void searchTheDB()
{
string s = "SELECT compName As 'Company/Organization Name', btcAddr As 'Bitcoin Address', Premium_User as 'Premium User'," +
"upvote as 'Upvotes',downvote As 'Downvotes' FROM clientDataTable WHERE compName LIKE '%" + searchBox.Text + "%'";
try
{
SqlConnection forSearch = new SqlConnection(connectionString);
SqlDataAdapter search = new SqlDataAdapter(s, forSearch);
DataSet dB = new DataSet();
search.Fill(dB);
searchGridView.DataSource = dB;
searchGridView.DataBind();
searchBox.Text = String.Empty;
}
catch (SqlException exp)
{
throw new InvalidOperationException("Sorry, the website is experiencing difficulties, please try again, error: ", exp);
}
}
protected void searchButton_Click(object sender, EventArgs e)
{
custVoteTextBox.Text = String.Empty;
searchTheDB();
generalLabel.Text = "results displayed below, if nothing is displayed your search returned no results";
}
protected void canUserVote()
{
submitCustNumButton_Click(new object(), new EventArgs());
string query = "INSERT INTO dbo.ClientDataTable (custNum) Values (@custNum)";
try
{
SqlConnection checkCustNum = new SqlConnection(connectionString);
SqlCommand isCustNumbValid = new SqlCommand(query, checkCustNum);
isCustNumbValid.Parameters.AddWithValue("@custNum", custNumber);
checkCustNum.Open();
isCustNumbValid.ExecuteNonQuery();
checkCustNum.Close();
}
catch (SqlException e)
{
if (e.Number == 2627) //checks if customer number is registered by activating unique constraint
{
canVote = true;
}
else //else user is not eligable to vote
{
canVote = false;
MessageBox.Show("invalid customer number, you cannot vote" + e);
}
}
}
protected void searchGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
canUserVote();//calls this method to check if user is eligable to vote with the given custNum in the textbox
if (canVote == true && custVoteTextBox.Text.Length == 8)
{
try
{
SqlConnection voteDb = new SqlConnection(connectionString);
{
switch (e.CommandName)
{
case "Upvote":
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow selectedRow = searchGridView.Rows[index];
string upvoteUpdateQuery = "UPDATE dbo.clientDataTable SET upvote = @upvote WHERE compName = @compName";
SqlCommand Upvote = new SqlCommand(upvoteUpdateQuery, voteDb);
Upvote.Parameters.AddWithValue("@upvote", "@upvote" + 1);
Upvote.Parameters.AddWithValue("@compName", selectedRow.DataItem.Equals("@compName"));
string insertQuery = "INSERT INTO dbo.clientDataTable (voted) Values(@voted) WHERE compName = @compName";
SqlCommand insertVoterDetailsUpvote = new SqlCommand(insertQuery, voteDb); //inserts voter information into specific entries table
insertVoterDetailsUpvote.Parameters.AddWithValue("@voted", custNumber);
insertVoterDetailsUpvote.Parameters.AddWithValue("@compName", selectedRow.DataItem.Equals("compName"));
voteDb.Open();
Upvote.ExecuteNonQuery();
voteDb.Close();
break;
case "Downvote":
int downvoteindex = Convert.ToInt32(e.CommandArgument);
GridViewRow downvoteSelectedRow = searchGridView.Rows[downvoteindex];
string downvoteUpdateQuery = "UPDATE dbo.clientDataTable SET downvote = @downvote WHERE compName = @compName";
SqlCommand Downvote = new SqlCommand(downvoteUpdateQuery, voteDb);
Downvote.Parameters.AddWithValue("@downvote", "@downvote" + 1);
Downvote.Parameters.AddWithValue("@compName", downvoteSelectedRow.DataItem.Equals("@compName"));
string downvoteInsertQuery = "UPDATE clientDataTable SET downvote = downvote + 1 WHERE compName = @compName";
SqlCommand insertVoterDetailsDownvote = new SqlCommand(downvoteInsertQuery, voteDb); //inserts voter information into specific entries table
insertVoterDetailsDownvote.Parameters.AddWithValue("@voted", custNumber);
insertVoterDetailsDownvote.Parameters.AddWithValue("@compName", downvoteSelectedRow.DataItem.Equals("@compName"));
voteDb.Open();
Downvote.ExecuteNonQuery();
voteDb.Close();
break;
}
}
}
catch (SqlException exp)
{
if (exp.Number == 2627)
{
MessageBox.Show("Sorry, you have already voted");
}
else
{
throw new InvalidOperationException("Sorry, the website is experiencing difficulties, please try again, error: ", exp);
}
}
}
else
{
MessageBox.Show("error, invalid customer number in the Textbox");
}
}
protected void submitCustNumButton_Click(object sender, EventArgs e)
{
int custNo = int.Parse(custVoteTextBox.Text);
this.custNumber = custNo;
}
