Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL
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 4-Apr-13 8:49am
Edited 4-Apr-13 8:52am
v2
Comments
richcb at 4-Apr-13 13:57pm
   
What do the "collectorid" entries look like in the table?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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 ;
}
  Permalink  
v3
Comments
mikeoabban at 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 at 9-Apr-13 1:23am
   
The error message says it all.I have edited the answer.Try now.
mikeoabban at 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 at 10-Apr-13 7:54am
   
i just got the answer. only code the codes from Keypress event to TextChanged event
thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
 
<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.
  Permalink  
v3
Comments
mikeoabban at 4-Apr-13 15:16pm
   
data load into the grid only when you type something in the text box
Prathap Gangireddy at 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 at 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)

  Print Answers RSS
0 Maciej Los 310
1 OriginalGriff 285
2 Afzaal Ahmad Zeeshan 200
3 Sergey Alexandrovich Kryukov 195
4 BillWoodruff 180
0 OriginalGriff 6,499
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,193
3 Manas Bhardwaj 4,657
4 Maciej Los 4,120


Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 9 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100