CODEWITHSUNDEEP
c#datagridviewdatatable
Sunil Hirole
Sunil Hirole

Reputation: 239

Displaying rows from table into datagridview with matched column value

I have a list of string which contain names and a database table what i want to do is to display the rows from the table in datagridview. All rows which contain the name column value same as any of the list item will be displayed into datagrid view.I wrote the code for this using for loop but it is showing only last matched row in datagridview.

DBConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FacesDatabase.mdb";
DBConnection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = DBConnection;

for(i=0;i<MatchName.ToArray().Length;i++)
{
      string query = "Select FaceID,FaceName,RollNo,FaceImage from " + tableName + " where FaceName='" + MatchName[i].ToString() + "'";
      command.CommandText = query;
      OleDbDataAdapter da=new OleDbDataAdapter(command);
      DataTable dt=new DataTable();
      da.Fill(dt);
      dataGridView1.DataSource=dt;
}

DBConnection.Close();

Upvotes: 0

Views: 2402

Answers (2)

Vanest
Vanest

Reputation: 916

You are overwriting your gridview datasource. You can also do something like,

dataGridView1.DataSource = null;
for (i = 0; i < MatchName.ToArray().Length; i++)
{
    string query = "Select FaceID,FaceName,RollNo,FaceImage from " + tableName + " where FaceName='" + MatchName[i].ToString() + "'";
    command.CommandText = query;
    OleDbDataAdapter da = new OleDbDataAdapter(command);
    DataTable dt = new DataTable();
    da.Fill(dt);
    if (dataGridView1.DataSource != null)
    {
        DataTable dt2 = (DataTable)dataGridView1.DataSource;
        dt.Rows.Cast<DataRow>().ToList().ForEach(x => dt2.ImportRow(x));
        dt = dt2;
    }
    dataGridView1.DataSource = dt;
}

Upvotes: 0

Tsef
Tsef

Reputation: 1038

You can use the IN SQL key word:

DBConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FacesDatabase.mdb";
DBConnection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = DBConnection;
string query = "Select FaceID,FaceName,RollNo,FaceImage from " + tableName + " where FaceName IN ('"+ string.Join("','",MatchName.ToArray())+ "')";
command.CommandText = query;
OleDbDataAdapter da=new OleDbDataAdapter(command);
DataTable dt=new DataTable();
da.Fill(dt);
dataGridView1.DataSource=dt;
DBConnection.Close();

In your example what you're doing is querying the db for each "MatchName" (whatever that is) and for every resultset you're getting back you're assigning it as the datagrid's datasource. Therefore you're only seeing the last resultset since you're overwriting previous results.

Using the IN key word you're only hitting the db once and binding the grid to the data source once.

I also recommend using command parameters instead of building your query they way you are using string concatenation.

Upvotes: 1

Related Questions