Binding SqlDataReader to GridView

[Horscht i]

Hi guys, this is the code I have problems with. It is supposed to become a little search function when finished. private void InitSearch() { DropDownList_SearchWhat.Items.Add("[ProjectID]"); [...] } protected void PerformSearch(object sender, EventArgs e) { //Start Sql connection using connection string from web.config SqlConnection SqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["String"].ToString()); // Define Search command SqlCommand Search = new SqlCommand(); Search.Connection = SqlConn; SqlDataReader Reader = null; Search.Parameters.Add(new SqlParameter("@SearchValue", Txt_SearchValue.Text.Trim())); Search.CommandText = "SELECT [ProjectID], [ProjectTitle], [ProjectStatus], [Orderer] FROM [DevelopmentOrder] WHERE ('"+DropDownList_SearchWhat.Text.Trim()+"'=@SearchValue)"; try { SqlConn.Open(); Reader = Search.ExecuteReader(); GVOverview.DataSource = Reader; GVOverview.DataBind(); Reader.Close(); SqlConn.Close(); } catch (Exception ex) { Message.Text = ex.Message; SqlConn.Close(); } The code works fine without any error message, but nothing is displayed in the GridView "GVOverview". I think the problem is the SELECT command: With the DropDownList the user can choose, what he wants to search for (Project ID, Type, Title). The search string can be typed into the TextBox "Txt_SearchValue". The DropDownList items are equal to the column names of the table, so i use the DropDownList.Text attribute to build the SELECT command. I also tried using an additional SqlParameter: "SELECT [ProjectID], [ProjectTitle], [ProjectStatus], [Orderer] FROM [DevelopmentOrder] WHERE (@SearchWhat = @SearchValue)"; In this case the @SearchWhat parameter text depends on the selected item in the DropDownList, e.g. if (DropDownList.SelectedIndex == 0) { Search.Parameters.Add(new SqlParameter("@SearchWhat", "[ProjectID]")); } The result is the same: Nothing is displayed in the GridView. Only way it displays something is a hard coded column name in the WHERE statement (e.g. WHERE [ProjectID]=@SelectValue). Has anyone an idea how to fix this problem? Thanks in advance PS: I guess there exist several better ways to programm a search func

[albCode]

Did you set to true auto generate fields of gridview or use boundfields. Proud to be Albanian

[Horscht i]

I am using BoundFields in the GridView. I`ll try with auto generated fields...maybe it works. -- modified at 2:35 Tuesday 14th March, 2006 No, BoundFields were not the problem. As I said it works fine, if I hard code a value like [...] WHERE ([ProjectID] LIKE @SearchValue)" For [ProjectID] I want to have the user selection of the DropDownList.

[albCode]

i suppose should be like this (if user have to select ProjectID from dropdownlist) [...] WHERE ([" + dropdownlist.SlectedValue + " ] LIKE @SearchValue)" //SlectedValue of ur dropdownlist should be ur projectid _____________________ Proud to be Albanian _____________________

[Horscht i]

Now I am one step closer. It works, but i reveive an error message "Invalid column name [Text Of DropDownList]". But at least the query was send to db. Thanks albcode!

[albCode]

dude fill datavaluefield of dropdownlist with IDProduct column and datatextfield by e.g Description. like this dropdownlist1.datasourceid="sqldatasource"; dropdownlist1.datatextfield="Descrption"; dropdownlist1.datavaluefield="ProductID";//here is ur solution example selectedvalue of dropdownlist is 3 [...] WHERE ([" + dropdownlist.SlectedValue + " ] LIKE @SearchValue)" //formula [...] WHERE (['" + 3 + "' ] LIKE @SearchValue)" //result

[Horscht i]

I think you did not get my intention... With the DropDownList the user can select, what he wants to search for: If he wants to search for a project title, than he chooses "ProjectTitle" from the List. (The items of the list are exactly the column names). Then the user enters the string he wants to search for in a beneath TextBox. @SearchValue parameter will be TextBox.Text Thus, we have as SELECT command: "[...] WHERE (['"+DropDownList.SelectedValue+"'] LIKE '%@SearchValue%')" just as you described. Or to get rid of the parameter "[...] WHERE (['"+DropDownList.SelectedValue+"'] LIKE '%'"+TextBox.Text"'%')" ['"+DropDownList.SelectedValue+"'] produces the error message 'Invalid column name "ProjectTitle"', but ProjectTitle is exactly the column name. That is what i do not understand. If i hard code the column name, e.g. "[...] WHERE ([ProjectTitle] LIKE '%'"+TextBox.Text+"'%')" the code works. DropDownList.SelectedItem and DropDownList.Text also produce the same error message. The problem is, that the selected item of the list is not properly inserted into the SELECT command and i don`t know why.:sigh:

[albCode]

Dude value of dropdownlist should contain value of column ProjectTitle and u slove ur problem _____________________ Proud to be Albanian _____________________