searching data in a database

Question

Hi,

Here is the aspx.cs file for my web application:

protected void Button1_Click(object sender, EventArgs e) {

    SqlDataReader myDataReader = null;
    string connectionString = "Data Source=[my source];Initial Catalog=[catalog name];Integrated Security=True";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {

        SqlCommand returnResults = new SqlCommand("SELECT " + categoryName + " FROM Teacher WHERE " + categoryName + " LIKE '%" + searchText + "%'", connection);

        connection.Open();
      myDataReader = returnResults.ExecuteReader(CommandBehavior.CloseConnection);

        while (myDataReader.Read())
        {
            Console.Write(myDataReader.GetInt32(0) + "\t");
            Console.Write(myDataReader.GetString(2) + " " + myDataReader.GetString(1) + "\t");
            Console.Write(myDataReader.GetString(3) + "\t");
            if (myDataReader.IsDBNull(4))
                Console.Write("N/A\n");
            else
                Console.Write(myDataReader.GetInt32(4) + "\n");
        }


        myDataReader.Close();
        connection.Close();

    }


}

   protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{

    categoryName = DropDownList1.SelectedItem.Value;

}
protected void SearchBox_TextChanged(object sender, EventArgs e)
{

    searchText = SearchBox.Text;
}

My database has a table with around 24 columns. The DropDownList I have created has an option to select each of these column names. There is a SearchBox underneath where the user can enter a keyword to search.

I want to save the DropDownList selection as "categoryName," and I want to save the SearchBox input as "searchText". When I run the application, I get this error:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FROM'.

Source Error: Line 48: myDataReader=returnResults.ExecuteReader(CommandBehavior.CloseConnection);

I'm not sure how to progress from here, so any help is appreciated. If you need more info please ask.

Answer 1

How about this:

SqlCommand returnResults = new SqlCommand("SELECT categoryName FROM Teacher WHERE categoryName LIKE '%" + searchText + "%'", connection);

This is assuming categoryName is the column you want in return, and also the one you want searched. Your original query was doing a few things wrong: using a variable instead of the column name, and the LIKE value wasn't quoted.

If the column to be searched is dynamic, and selected by the drop down box, and the value is stored in the variable categoryName:

SqlCommand returnResults = new SqlCommand("SELECT " + categoryName + " FROM Teacher WHERE " + categoryName + " LIKE '%" + searchText + "%'", connection);

Answer 2

Looks like you may be missing blank spaces

"SELECT" + categoryName + "FROM Teacher WHERE" + searchText + " LIKE " + "%" + searchText + "%", connection);

is going to return something like

SELECTmycolumnnameFROM Teacher WHEREmycolumn LIKE %john%

I think you should do this

"SELECT " + categoryName + " FROM Teacher WHERE " + searchText + " LIKE """ + "%" + searchText + "%""", connection);

to get

SELECT mycolumnname FROM Teacher WHERE mycolumn LIKE "%john%"