Click here to Skip to main content
15,896,410 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to validate my webform. If count is 2 for the particular month and employee, a jquery message will show. But I am getting input string not in correct format.

I am new to asp.net. Can you please guide me how can i write this query correctly?

C#
string query = "select sum(count) from WFHCO_COUNT where MONTH= " + DropDownList3.SelectedValue.ToString()+" and  EMPNO=EMPNO";
           int counts = Convert.ToInt32(query);
           if ( counts >= 2)
           {
               JQUERYDisplay.ShowAlertMessage("Oops.. You have already made two requests for this month. Try again Next Month");
               clear();
           }
Posted

There are several mistakes:
C#
string query = "select sum(count) from WFHCO_COUNT where MONTH= " + DropDownList3.SelectedValue.ToString()+" and  EMPNO=EMPNO";

is bad; this leaves your code opened to SQL injection attacks. You should validate that the value selected is a valid one, and construct your query from there. This way:
C#
int month;
if (!int.TryParse(DropDownList3.SelectedValue, out month)) {
   // Selected value is not a valid integer
   return;
}
string query = "select sum(count) from WFHCO_COUNT where MONTH=@month and  EMPNO=@empno";
using (SqlConnection connection = /* Build your connection here */)
using (SqlCommand cmd = new SqlCommand(query, connection)) {
   cmd.Parameters.AddWithValue("@month", month);
   cmd.Parameters.AddWithValue("@empno", EMPNO);
   int count = cmd.ExecuteScalar();
}

As you can see:
- I validated that the selected value is a valid integer (we could also validate that it is a valid month), in case user just typed a hand-crafted value in the combo.
- I used SqlConnection and SqlCommand objects.
- I used a parameterized query, and qualified said parameters before executing.
- I execute the query and get a result from it, whereas you just tried to get the result by converting your query to an integer. 'Convert' class is not a magic wand, and in fact it is rarely needed nor useful.
- I used using blocks so that disposable objects are effectively disposed when they are not needed anymore.
 
Share this answer
 
Comments
Member 9017207 7-Dec-15 12:49pm    
OK I will try this.

And after int count = cmd.ExecuteScalar(); I can show the display message right?
phil.o 7-Dec-15 12:54pm    
Why couldn't you?
Um...
C#
string query = "select sum(count) from WFHCO_COUNT where MONTH= " + DropDownList3.SelectedValue.ToString()+" and  EMPNO=EMPNO";
           int counts = Convert.ToInt32(query);
And what number do you expect a string that starts with "select" to generate?
Words are not numbers, and strings do not automatically query databases.
Try:
C#
int counts;
using (SqlConnection con = new SqlConnection(@"strConnect"))
     {
     con.Open();
     using (SqlCommand com = new SqlCommand("select sum(count) from WFHCO_COUNT where MONTH= " + DropDownList3.SelectedValue.ToString()+" and  EMPNO=EMPNO", con))
         {
         counts = (int) com.ExecuteScalar();
         }
     }

But...you should use a parameterised query, and the second part of your WHERE clause is irrelevant!
 
Share this answer
 
Comments
Member 9017207 7-Dec-15 12:38pm    
OK I will try this.

And after counts = (int) com.ExecuteScalar(); I can show the display message right?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900