Search SQL Server database with multiple text box values with search button

Question

I can only get entries back from the database if I put a value in the id text field but when I put anything in the other text fields I get no results back when I know for sure that they are in there.

Here is my code:

protected void SearchButton_Click(object sender, EventArgs e)
{
    String commandString = "SELECT * FROM [Swim] WHERE (([First Name] LIKE '%' + @First_Name + '%') AND ([Last Name] LIKE '%' + @Last_Name + '%') AND ([Phone] LIKE '%' + @Phone + '%') AND ([id] = @id))";

    //SELECT * FROM [Swim] WHERE ([Phone] LIKE '%' + @Phone + '%')
    SqlCommand command = new SqlCommand(commandString, conn);

    command.Parameters.Add("@First_Name", SqlDbType.NVarChar).Value = FirstNameTextBox.Text;
    command.Parameters.Add("@Last_Name", SqlDbType.NVarChar).Value = LastNameTextBox.Text;
    command.Parameters.Add("@id", SqlDbType.NVarChar).Value = IdTextBox.Text;
    command.Parameters.Add("@phone", SqlDbType.NVarChar).Value = PhoneTextBox.Text;

    conn.Open();
    command.ExecuteNonQuery();

    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = command;

    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();

    conn.Close();
}

Any suggestions would help?

First of all, I'd suggest you read up on SQL injection attacks and how to use parametrized queries to avoid those! — marc_s
– marc_s, Commented Jan 24, 2014 at 6:16

Timothy Walters · Accepted Answer · 2014-01-23 23:51:56Z

Seriously consider using something like Entity Framework instead of munging SQL text inside your code.

I am taking a guess here that you want to allow a query on one-or-more of the fields.

To satisfy your requirement using Entity Framework and querying using LINQ you would do the following:

var query = db.Swims;

// ID overrides all others, since it is unique no point adding more filters unless
// you want to not return the row if the other filters don't match?
if (IdTextBox.Text.Length > 0)
{
    int id = Convert.ToInt32(IdTextBox.Text);
    query = query.Where(s => s.Id == id);
}
else
{
    if (FirstNameTextBox.Text.Length > 0)
    {
        query = query.Where(s => s.FirstName.Contains(FirstNameTextBox.Text));
    }
    if (LastNameTextBox.Text.Length > 0)
    {
        query = query.Where(s => s.LastName.Contains(LastNameTextBox.Text));
    }
    if (PhoneTextBox.Text.Length > 0)
    {
        query = query.Where(s => s.Phone.Contains(PhoneTextBox.Text));
    }
}

GridView1.DataSource = query.ToList();

The above will handle all 8 or so combinations of queries without a problem.

I've made a few assumptions here not explicitly stated in your question, let me know if I got any wrong and I'll update the answer accordingly.

Fermin Pitol · Accepted Answer · 2014-01-23 23:37:25Z

0

try with this:

SELECT * FROM Swim WHERE (First_Name LIKE '%' + @First_Name + '%' AND Last_Name LIKE '%' + @Last_Name + '%' and Phone LIKE '%' + @Phone + '%') OR (id = @id));

answered Jan 23, 2014 at 23:37

Fermin Pitol

4866 silver badges12 bronze badges

Comments

Nick H. · Accepted Answer · 2014-01-24 01:58:40Z

0

First, I would recommend putting (if possible) this into sql stored procedures. That's more of a personal preference since I'm more on the database side. When we write code between the application layer and the database layer, it's so much easier to refactor a stored proc if you must and easier (to me) to store/retrieve data between the two.

If you're interested, I could post an example of how I would do this using c# & a stored proc.

As far as your issue.. shouldn't this: command.ExecuteNonQuery(); be this: command.ExecuteQuery ?

I use command.ExecuteNonQuery to send updates/information to the database layer and command.ExecuteQuery to pull information to the application layer.

answered Jan 24, 2014 at 1:58

Nick H.

1,61614 silver badges20 bronze badges

Collectives™ on Stack Overflow

Search SQL Server database with multiple text box values with search button

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related