CODEWITHSUNDEEP
c#sqlwinforms
tryingtotryhard
tryingtotryhard

Reputation: 134

SQL LIKE search with multiple text boxes?

enter image description here

Here is the code for my button.

private void btnStudentLookup_Click(object sender, EventArgs e)
    {
        string strConnect = "Server=DESKTOP- 
        2Q73COU\\SQLEXPRESS;Database=LoginApp;Trusted_Connection=True;";
        SqlConnection conn = new SqlConnection(strConnect);
        conn.Open();
        using (SqlConnection studentLookup = new SqlConnection(strConnect))
        {
            SqlCommand command =
                new SqlCommand("SELECT * FROM Main_Information WHERE [First Name] like '%" + txtFirstName.Text + "%';", studentLookup);

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            dgvAdvisor.DataSource = dt;
        }
    }

Everything above works. It filters out rows based on First Name. However, I'd like to filter out more than that so I add this to the line:

"SELECT * FROM Main_Information WHERE [First Name] like '%" + txtFirstName.Text + "%' OR [Last Name] like '%" + txtLastName.Text + "%';", studentLookup

Now it just doesn't do anything. No errors,no exceptions, nothing. Any advice?

Upvotes: 0

Views: 1125

Answers (2)

Fabio
Fabio

Reputation: 32445

When test your application write in the txtFirstName textbox:

'; DELETE FROM Main_Information; --

And press "Submit" button.

if everything works then great, if not then use SqlParameters for building query with dynamic values

using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
    var query = @"
        SELECT * FROM Main_Information
        WHERE [First Name] LIKE @firstName AND [Last Name] LIKE @lastName";
    var parameters = new [] 
    {
        new SqlParameter
        {
            ParameterName = "@firstName",
            SqlDbType = SqlDbType.VarChar,
            Value = $"%{txtFirstName.Text}%"
        },
        new SqlParameter
        {
            ParameterName = "@lastName",
            SqlDbType = SqlDbType.VarChar,
            Value = $"%{txtLastName.Text}%"
        }
    }   

    command.CommandText = query;
    command.Parameters.AddRange(parameters);

    connection.Open();

    var adapter = new SqlDataAdapter(command);
    var data = new DataTable();
    adapter.Fill(data);
    dgvAdvisor.DataSource = data;
}

Upvotes: 0

ssbsuresh
ssbsuresh

Reputation: 16

Change the query as below:

SELECT * FROM Main_Information WHERE [First Name] like '%" + txtFirstName.Text + "%' AND [Last Name] like '%" + txtLastName.Text + "%';", studentLookup

Use AND operator instead of OR.

Upvotes: 0

Related Questions