how to from select parameterized query in asp.net

Question

I want to write parametrized query for select statement. but it gives exception that "Must declare the variable '@'." how to declare this variable .

My code is given below:

SqlConnection con = null;
        SqlCommand cmd = null;
        try
        {
            //int @[MONTH_FOR], @[YEAR_FOR];
            con = new SqlConnection("Data Source=192.168.10.3;Initial Catalog=GPSTrainees;user id=gp;password=gp");
            con.Open();
            string select = @"SELECT [COMPONENT_NAME] ,[COMPONENT_AMOUNT] 
                            FROM [GoalPlanForTrainees].[gp].[TEAM_FUNDS_DETAILS] 
                            WHERE [MONTH_FOR] = @[MONTH_FOR] AND [YEAR_FOR] = @[YEAR_FOR]";
            cmd = new SqlCommand(select, con);
            cmd.Parameters.Add(new SqlParameter("@[MONTH_FOR]", Convert.ToInt32( TextBox1.Text.Trim())));
            cmd.Parameters.Add(new SqlParameter("@[YEAR_FOR]",Convert.ToInt32(TextBox2.Text.Trim())));
            DataSet ds = new DataSet();
            SqlDataAdapter adp = new SqlDataAdapter(select, con);
            adp.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (con != null)
            {
                con.Close();
            }
        }`enter code here`

Answer 1

Hi Shalni, Put the variable declaration in the sql statement. So move int @[MONTH_FOR], @[YEAR_FOR]; to @"int @[MONTH_FOR], @[YEAR_FOR]; SELECT [COMPONENT_NAME] ,[COMPONENT_AMOUNT] FROM [GoalPlanForTrainees].[gp].[TEAM_FUNDS_DETAILS] WHERE [MONTH_FOR] = @[MONTH_FOR] AND [YEAR_FOR] = @[YEAR_FOR]";

Cheers Tigger

Answer 2

While creating the parameters, please remove the "@" in front of the Parameter name. Something like this:

cmd.Parameters.Add(new SqlParameter("[MONTH_FOR]", Convert.ToInt32(TextBox1.Text.Trim()))); 

Hopefully this should help.

Answer 3

try this int MONTH_FOR=@monthFor command.Parameters.Add(MONTH_FOR, SqlDbType.int).Value = value