DataGridView search returns no rows

I am writing an application that has a SqlServer database behind it. When the user selects which table they want to see the gridview is shown and a textbox shows up that they can type in and search that specific gridview. Yet whenever I type anything into the textbox the gridview returns no rows, it doesn't seem to matter what I type into the box. I have tried coding it many different ways but no to avail. any suggestions would be greatly appreciated. this is the code that is called when the user types into the textbox to search the gridview.

    

privatevoidtxtSearchKeyUp(objectsender, KeyEventArgse)

        {

            con.Open();

           

                SqlCommandsearchCMD = con.CreateCommand();

                searchCMD.CommandType =

                CommandType.Text;

                searchCMD.Parameters.Add(

                "Query", SqlDbType.Char).Value = '%'+txtSearch.Text+ '%';

          

// searchCMD.CommandText = "SELECT * FROM dbo.[Asset Table] WHERE ([Asset ID] = N'N12855')";


            searchCMD.CommandText =

"SELECT assetID, assetName, Description, assetStorageLocation, assetStorageSubLocation, assetType,[bookValue(Estimated)] FROM dbo.assetTable WHERE assetID LIKE 'Query'";

            searchCMD.ExecuteNonQuery();

           

DataTablesearchData = newDataTable();

        

SqlDataAdaptersearchDT = newSqlDataAdapter(searchCMD);

            searchDT.Fill(searchData);

            dataAssetTable.DataSource = searchDT;

           

            con.Close();

        }

August 27th, 2015 10:36am

Hi,

I just noticed that there are improper declaration of parameter and the data source declaration to DGV.

1. From a parameter: searchCMD.Parameters.Add("Query"SqlDbType.Char).Value = '%'+txtSearch.Text+ '%';, just change it to searchCMD.Parameters.Add("@query", SqlDbType.Char).Value = txtSearch.Text;. Make sure that the wild card (%) is in a query section not in a parameter declaration. 

2. From the where clause of your query: WHERE assetID LIKE 'Query', just change it to: WHERE assetID LIKE '%' + @query + '%'. 

3. And lastly the DGV datasource. Your declaration of the datasource is not a datatable, you set it as a Datadapter searchDT. dataAssetTable.DataSource = searchDT;, change it to: dataAssetTable.DataSource = searchData;.

So by modifying the code:

//con = new SqlConnection(@"Your connection string...");
            using (con)
            {
                con.Open();
                SqlCommand searchCMD = con.CreateCommand();
                searchCMD.CommandType = CommandType.Text;
                searchCMD.Parameters.Add("@query", SqlDbType.Char).Value = txtSearch.Text;
                // searchCMD.CommandText = "SELECT * FROM dbo.[Asset Table] WHERE ([Asset ID] = N'N12855')";

                searchCMD.CommandText = "SELECT assetID, assetName, Description, assetStorageLocation, assetStorageSubLocation, " +
                    "assetType,[bookValue(Estimated)] FROM dbo.assetTable WHERE assetID LIKE '%' + @query + '%'";

                //searchCMD.CommandText = "Select * From temp Where Tree Like '%' + @query + '%'";

                searchCMD.ExecuteNonQuery();
                DataTable searchData = new DataTable();
                SqlDataAdapter searchDT = new SqlDataAdapter(searchCMD);
                searchDT.Fill(searchData);
                dataAssetTable.DataSource = searchData;
            }
Hope it helps.

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 2:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics