Mobilemike Mobilemike - 3 months ago 11
C# Question

SqlCommand returns all records even though parameters are specified

I've got a simple ASP.NET web form with 5 text boxes and a submit button to filter a result set returned from a database.

Regardless of what is entered, the

DataAdapter
is filled with the complete contents of the table without any of the parameters included.

What's the problem with my code?

private void BindGridView()
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;

cmd.CommandText = "select Cust_SID, First_name, Last_name, Address1, Phone1, Email_addr from Customer where 1=1";

if (!String.IsNullOrEmpty(txtAddress.Text))
{
cmd.Parameters.AddWithValue("@Address1", SqlDbType.NVarChar).Value = "%" + txtAddress.Text + "%";
}

if (!String.IsNullOrEmpty(txtFirstName.Text))
{
cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text + "%");
}

if (!String.IsNullOrEmpty(txtLastName.Text))
{
cmd.Parameters.AddWithValue("@LastName", "%" + txtLastName.Text + "%");
}

if (!String.IsNullOrEmpty(txtEmail.Text))
{
cmd.Parameters.AddWithValue("@Email", "%" + txtEmail.Text + "%");
}

if (!String.IsNullOrEmpty(txtPhone.Text))
{
cmd.Parameters.AddWithValue("@Phone1", "%" + txtPhone.Text + "%");
}

conn.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dsCustomer = new DataSet();

da.Fill(dsCustomer, "Customer");

DataView dvCustomer = dsCustomer.Tables["Customer"].DefaultView;
dvCustomer.Sort = ViewState["SortExpression"].ToString();

gvCustomer.DataSource = dvCustomer;
gvCustomer.DataBind();
}
}

Answer

You have to actually include the parameters in your SQL:

cmd.CommandText = 
    "select Cust_SID, First_name, Last_name, Address1, Phone1, Email_addr " +
    "from Customer where 1=1";

if (!String.IsNullOrEmpty(txtAddress.Text))
{
    cmd.CommandText += " or Address1 like @Address1";
    cmd.Parameters.Add("@Address1", SqlDbType.NVarChar)
                  .Value = "%" + txtAddress.Text + "%";
}

if (!String.IsNullOrEmpty(txtFirstName.Text))
{
    cmd.CommandText += " or First_name like @FirstName";
    cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text + "%");
}

// and so on for each parameter