DataAdapter Sql Query with parameters - c#

Question

Code below is working properly and view all matches by search in column.

string sql = "SELECT car, model, year FROM store WHERE" + column + "LIKE " + search + "'";

Now adding parameters in query. Not working. It doesn't display search in column. Only display all rows in column, if search column of column ( 1 = 1)

    public int SearchCar(MainStore searchCars)
    {
            string connection = @"Data Source=(LocalDB)";
            SqlConnection con = new SqlConnection(connection);
            string sql = "SELECT car, model, year FROM store WHERE @column like @search '";
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);

            sdt.SelectCommand.Parameters.AddWithValue("@column", "%" + searchCars.GetCombo());
            sdt.SelectCommand.Parameters.AddWithValue("@search", "%" + searchCars.GetSearch());

            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = data;
     }

What could possible be the answer to get it search within specific column?

can you post end - end code from connection opening to connection closing? or explain what is "search", "sdt"? — Jeyara
– Jeyara, Commented May 2, 2016 at 0:06
You can't do that: stackoverflow.com/questions/3330343/… You can use a 'hybrid' method of your two samples. — Steve Wellens
– Steve Wellens, Commented May 2, 2016 at 0:55

Ash · Accepted Answer · 2016-05-02 02:10:33Z

1

Change it as follows so as to not parameterize the column name:

public int SearchCar(MainStore searchCars)
    {
            string connection = @"Data Source=(LocalDB)";
            SqlConnection con = new SqlConnection(connection);
            string sql = string.Format("SELECT car, model, year FROM store WHERE {0} like @search", search.GetCombo());
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);

           // sdt.SelectCommand.Parameters.AddWithValue("@column", "%" + search.GetCombo());
            sdt.SelectCommand.Parameters.AddWithValue("@search", "%" + search.GetSearch());

            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = data;
     }

Also, you've got an extra quote at the end of your query: like @search '";

edited May 2, 2016 at 2:10

answered May 2, 2016 at 1:33

Ash

6,0635 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Global Over a year ago

I discovered a new issue. When the combobox is not selected, then a catch error appears. Incorrect syntax near the keyword 'like'

Ash Over a year ago

You're going to have to handle the case where it is not selected. I'm guessing search.GetCombo() returns null if it isn't selected. For example, you could try throwing an alert in case nothing is selected. var columnName = search.GetCombo(); if (columnName == null) { \\Show validation message return; }

suulisin · Accepted Answer · 2016-05-01 23:48:48Z

0

Please take a look at this

 private static void Select() {

       string cmdStr = "SELECT FirstName, LastName, Telephone FROM Person WHERE FirstName = @FirstName";

        using (SqlConnection connection = new SqlConnection(ConnectionString))

        using (SqlCommand command = new SqlCommand(cmdStr, connection)) {

            command.Parameters.AddWithValue("@FirstName", "John");

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read()) {

                string output = "First Name: {0} \t Last Name: {1} \t Phone: {2}";

                Console.WriteLine(output, reader["FirstName"], reader["LastName"], reader["Telephone"]);

            }

        }

    }

answered May 1, 2016 at 23:48

suulisin

1,4351 gold badge10 silver badges17 bronze badges

2 Comments

Global Over a year ago

That's not correct at all. I'm using SqlDataAdapter and combobox.

suulisin Over a year ago

Sorry i did not understand you question

Collectives™ on Stack Overflow

DataAdapter Sql Query with parameters - c#

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related