CODEWITHSUNDEEP
c#countlabel
user3716314
user3716314

Reputation: 9

The code of Show sum of user into label not working

I have two dropdownlists one of the country (worlddrdolist) and the another is of states (staatddl) and the button (NumOfUsrBtn) where if the user since click on it then the label will show the total sum of users depending on a country selected from (worlddrdolist) First whatever if the user select the state or not also if the user select the state then it will show the users depending of the country , the below code need update and fix some of part as i tried my best to fix but i coundnt, and i am receiving error message:

"Must declare the scalar variable "@cou"." also "Must declare the scalar variable "@sta"."

 protected void NumOfUsrBtn_Click(object sender, EventArgs e)
    {

        using (var conn = new SqlConnection(sc))
        {
            string UsrNumSQL = "SELECT COUNT (UsrID)FROM UserInfo WHERE 1=1 AND Country = @cou AND State=@sta";

            using (var cmd = new SqlCommand(UsrNumSQL, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 10000;
                cmd.Parameters.AddWithValue("@cou", worlddrdolist.SelectedValue);
                cmd.Parameters.AddWithValue("@sta", staatddl.SelectedValue);


                string condition = "";

                if (worlddrdolist.SelectedValue != "")
                {
                    condition += " and Country='" + worlddrdolist.SelectedValue + "'";
                }
                if (staatddl.SelectedValue != "")
                {
                    condition += " and State='" + staatddl.SelectedValue + "'";
                }
                cmd.Connection.Open();

                NumbOfUsersLbl.Text = cmd.ExecuteScalar().ToString();
            }

        }
    }

Upvotes: 0

Views: 34

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98810

This is not how parameterized queries works.

You need add your parameter value as a parameter in your command, not an additional condition.

cmd.Parameters.AddwithValue("@cou", worlddrdolist.SelectedValue);
cmd.Parameters.AddwithValue("@sta", staatddl.SelectedValue);

But I strongly suspect you just wanna add additional conditions with AND... at first place, you don't need to use parameter in your command at all. Looks like your conditions is not known at compile time, instead you wanna build them in runtime.

In such a case, just delete unnecessary parts in your query since you wanna add them based on your condition like;

string UsrNumSQL = "SELECT COUNT (UsrID)FROM UserInfo WHERE 1=1";
...
...
if (worlddrdolist.SelectedValue != "")
{
    condition += " and Country='" + worlddrdolist.SelectedValue + "'";
}
if (staatddl.SelectedValue != "")
{
    condition += " and State='" + staatddl.SelectedValue + "'";
}
cmd.Connection.Open();
NumbOfUsersLbl.Text = cmd.ExecuteScalar().ToString();

Upvotes: 1

Related Questions