Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a text box that responds to a keypress event. When an letter is entered in the text box, it must display all ID's with that letter in them e.g.(%d%) in a gridview. It works, but it displays all data in the database without sorting based on the letter. Please help me out.
private void txtCollectorid_KeyPress(object sender, KeyPressEventArgs e)
        {
            
              dgvContactpersonsearch.Visible = true;
            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            string strConn = "Data Source=user-PC;Initial Catalog=MUCGPROJECT;User ID=sa;Password=mike";
            string strSQL = "select Collectorid AS 'Collector ID', Title, Surname, Middlename AS 'Middle Name ', Firstname, Gender, Dateofbirth AS 'Date of Birth' from tblcollectorsregistration where collectorid LIKE '" + "%" + txtCollectorid.Text + "%" + "' order by Surname";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strConn);
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataAdapter.Fill(table);
            bindingSource1.DataSource = table;
            // you can make it grid readonly.
            dgvContactpersonsearch.ReadOnly = true;
            // finally bind the data to the grid
            dgvContactpersonsearch.DataSource = bindingSource1;
        }
Posted
Updated 4-Apr-13 7:52am
v2
Comments
Richard C Bishop 4-Apr-13 13:57pm    
What do the "collectorid" entries look like in the table?

Try this code.

private void txtCollectorid_KeyPress(object sender, KeyPressEventArgs e)
        {
            
              dgvContactpersonsearch.Visible = true;
 SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   string strConn = "Data Source=user-PC;Initial Catalog=MUCGPROJECT;User ID=sa;Password=mike";
// Using this Query get the data from tblcollectorsregistration table
            string strSQL = "select Collectorid AS 'Collector ID', Title, Surname, Middlename AS 'Middle Name ', Firstname, Gender, Dateofbirth AS 'Date of Birth' from tblcollectorsregistration order by Surname";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strConn);
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataAdapter.Fill(table); //Table is filled with data
//Now use below code to filter
string expression ="[Collector ID] like '%" + txtCollectorid.Text + "%'";
//Make sure the ColumnName Collector ID is same as Displayed in the gridview.
DataRow[] filteredRows=table.Select(expression); //table is the Datatable
if (filteredRows.Any()) //If any rows are matching
{
table = filteredRows.CopyToDataTable();
}

 // you can make it grid readonly.
    dgvContactpersonsearch.ReadOnly = true;
// finally bind the data to the grid
     dgvContactpersonsearch.DataSource = table ;
}
 
Share this answer
 
v3
Comments
mikeoabban 8-Apr-13 12:51pm    
i get error mesage "The name dtfilter does not exist in the current context" here
dtfilter = filteredRows.CopyToDataTable();
dgvContactpersonsearch.DataSource = dtfilter ;
Prathap Gangireddy 9-Apr-13 1:23am    
The error message says it all.I have edited the answer.Try now.
mikeoabban 10-Apr-13 7:20am    
Great it worked. thanks alot for the help.
when the form loads, the dgvContactpersonsearch gridview is hiden. i want it to be displayed on the form only when the first letter entered in the textbox muches the collectorid in the datatable. i tried removing dgvContactpersonsearch.Visible = true; from line 1 placed it here

if (filteredRows.Any()) //If any rows are matching
{
table = filteredRows.CopyToDataTable();
dgvContactpersonsearch.visible = true ;
}

but it still get displayed on the form when any key is pressed. what should i do thanks
mikeoabban 10-Apr-13 7:54am    
i just got the answer. only code the codes from Keypress event to TextChanged event
thanks
Hi,

I think you are already displaying data in the gridview when loading the form.Why bother a call to the database again.

Here is an example where you can use the datatable which is used as datasource to the Gridview.

HTML
<pre lang="c#">
string expression ="[Collector ID] like '%" + txtCollectorid.Text + "%'";
//Make sure the ColumnName Collector ID is same as Displayed in the gridview.
DataRow[] filteredRows=table.Select(expression); //table is the Datatable
if (filteredRows.Any()) //If any rows are matching
{
dtfilter = filteredRows.CopyToDataTable();
}

by doing so you can avoid call to database.
 
Share this answer
 
v3
Comments
mikeoabban 4-Apr-13 15:16pm    
data load into the grid only when you type something in the text box
Prathap Gangireddy 4-Apr-13 15:18pm    
Not a problem.Load all the data into a Datatable using a query then follow my code.Always avoid the number of calls to database.
mikeoabban 5-Apr-13 14:37pm    
thanks alot for the help, but am a learner in c#. i have tried using the codes but it keeps
given errors. can you please help me out with the full code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900