The parameterized query expects parameter which was not supplied

Question

I am trying to execute the code attached and I ended up with an error.I am making an ajax call and posting data to the controller method and I am selecting values from the sql database based on the values received.Please help

Controller code:-

public ActionResult notCk_Pk(NewOne n)
        {

            List<Counting> l = new List<Counting>();
            String FirstName = n.FirstName;
            String LastName = n.LastName;
            int Salary = n.Salary;
            String Gender = n.Gender;

                string query = "select count(FirstName),count(LastName),Salary,count(Email) from tblEm where FirstName=@FirstName";
                string ConnectionString = ConfigurationManager.ConnectionStrings["EmployeeContext"].ConnectionString;
                SqlDataAdapter da;
                DataSet ds = new DataSet();
                using (SqlConnection connection = new SqlConnection("data source=.; database=Srivatsava; integrated security=SSPI"))
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = n.FirstName;
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
                da = new SqlDataAdapter(query, ConnectionString);
                da.Fill(ds);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    l.Add(new Counting() { FirstNamecount = Convert.ToInt32(dr[0]), LastNamecount = Convert.ToInt32(dr[1]), Salary = Convert.ToInt32(dr[2]), Gendercount = Convert.ToInt32(dr[3]) });
                }



            var todoListsResults = l.Select(
                  a => new
                  {

                      a.FirstNamecount,
                      a.LastNamecount,
                      a.Salary,
                      a.Gendercount

                  });

            var jsonData = new
            {
                //   total = totalPages,
                // page,
                //records = totalRecords,
                rows = todoListsResults
            };
            return Json(jsonData, JsonRequestBehavior.AllowGet);




        }

js:-

$.ajax({
                url: '/TodoList/notCk_Pk',
                data:{'FirstName':FirstName,'LastName':LastName,'Salary':Salary,'Gender':Gender},
                type: "post",
                dataType: 'json',
                success: function (response) {
                    var rows = response.rows;
                    $.each(rows, function () {
                        alert(this.FirstNamecount);
                    });
                }
            })

Put a breakpoint and see whether your action method is getting the proper object with all properties. — Shyju
Data received correctly may be I have some issue with the syntax? — rao

shadow shadow · Accepted Answer · 2015-12-27T08:09:59

I believe the problem is here:

using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = n.FirstName;
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }

Try this:

using (SqlCommand cmd = new SqlCommand(query, connection))
                {    
                    cmd.Parameters.AddWithValue("@FirstName", n.FirstName);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }

For further information check SqlCommand.Parameters Property

The parameterized query expects parameter which was not supplied

Controller code:-

js:-

1 Answers