CODEWITHSUNDEEP
c#sql-server
Mobilemike
Mobilemike

Reputation: 37

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();
    }
}

Upvotes: 0

Views: 145

Answers (2)

Blorgbeard
Blorgbeard

Reputation: 103437

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 0=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

Upvotes: 1

Stef Geysels
Stef Geysels

Reputation: 1047

A SQL-string is sometimes a bit confusing.

The first part SELECT is executed at the end of the request to the database.

The first thing that the database adapter does is enter the table (FROM tableName).

Second thing is the comparison of the WHERE statements. Here you can choose which rows the database need to return. E.g. WHERE column1="someValue". You'll get al the rows where column1 is "someValue".

Third are the group by and the order by statements.

And finnaly, from the internal list of rows that the database holds for that request (after the WHERE), you can SELECT which columns you want. If you want all the colums you simply put * , otherwise you give onze of more column names of even "MAX(columName)" which give you the highest number in that column (only for numeric columns).

You can code your SQL strings in two ways:

First way is hardcoding the WHERE statements. This is acceptable if the parameter is not a complex type like DateTime:

"SELECT * FROM tableName WHERE column1=" + variable;

Second way is using parameters. This way is better because the SqlCommand object will convert your parameters to the right DbType:

"SELECT * FROM tableName WHERE column1=@paramerter1";
var t = com.CreateParameter();
t.ParameterName = "@parameter1";
t.Value = variable;
com.Parameters.Add(t);

A good program to test your sql-strings is "SqlTryOut". A Google search will do.

Upvotes: 0

Related Questions