Invalid Column name when implementing search button

Question

I'm working with C# and SQL Sever 2008, when I try to create a command for searching a record I got exception that said "Invalid Column name"

this is my code :

void cari()
        {
            koneksi.Open();
            DataTable dt = new DataTable();
            SqlDataAdapter SDA = new SqlDataAdapter("SELECT * FROM jadwalkuliah where Subject = "+ textBox1.Text, koneksi);
            SDA.Fill(dt);
            koneksi.Close();

            dataGridView1.DataSource = dt;
        }`

the search command should be work as search engine, can anyone help me?

Answer 1

Well the immediate problem is that your WHERE clause will look something like:

where Subject = Foo

which is trying to compare the value of the Subject column with the value of the Foo column.

The hacky way of fixing this is to put quotes round the value. The better solution is to use parameterized SQL:

string sql = "SELECT * FROM jadwalkuliah where Subject = @Subject";
using (SqlConnection connection = new SqlConnection(...))
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connection))
{
    connection.Open();
    adapter.SelectCommand.Parameters.Add("@Subject", SqlDbType.VarChar)
                                    .Value = textBox1.Text;
    adapter.Fill(dt);
}

Additionally, note that you shouldn't be performing database accesses from a GUI thread. It's not clear whether this is a web app (in which case it's okay) or WPF/WinForms (in which case it's not).

Note that that will still try to make an exact match. For a "wildcard" match you'll need to change it to something like:

SELECT * FROM jadwalkuliah where Subject LIKE @Subject

... and add the parameter with something like "%" + textBox1.Text + "%". (You'll need to then think about escaping within that value, but that's another matter...)