2 DataGrids with using same SQL Server table

[Cousy]

I have a windows form with two datagrid objects. The goal is to display
rows from the same SQL Server table, using different SELECT commands.

Here's a sample of the code:

Data Grid 1:
----------------------------------------------------------------------------------------------------------------------------------
strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate"
+ @" FROM sessions"
+ @" WHERE StartDate < GetDate()"
+ @" ORDER BY StartDate, ClassCode";

myCN = new System.Data.SqlClient.SqlConnection();
myCN.ConnectionString = frmMain.strConn;
myCN.Open();

myDA1 = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN);
myDS1 = new DataSet();
myDA1.Fill(myDS1, "sessions");

this.myDA1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "sessions",
new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ID", "ID"),
new System.Data.Common.DataColumnMapping("ClassID", "ClassID"),
new System.Data.Common.DataColumnMapping("ClassCode",
"ClassCode"),
new System.Data.Common.DataColumnMapping("StartDate",
"StartDate"),
new System.Data.Common.DataColumnMapping("EndDate",
"EndDate")})});

dataOrgs1.DataSource = myDS1;
dataOrgs1.DataMember = "sessions";
dataOrgs1.SetDataBinding(myDS1, "sessions");
----------------------------------------------------------------------------------------------------------------------------------

DataGrid 2:
----------------------------------------------------------------------------------------------------------------------------------
strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate"
+ @" FROM sessions"
+ @" WHERE Status = 'Open Reg'"
+ @" ORDER BY StartDate, ClassCode";

myCN = new System.Data.SqlClient.SqlConnection();
myCN.ConnectionString = frmMain.strConn;
myCN.Open();

myDA = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN);
myDS = new DataSet();
myDA.Fill(myDS, "sessions");

this.myDA.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "sessions",
new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ID", "ID"),
new System.Data.Common.DataColumnMapping("ClassID", "ClassID"),
new System.Data.Common.DataColumnMapping("ClassCode",
"ClassCode"),
new System.Data.Common.DataColumnMapping("StartDate",
"StartDate"),
new System.Data.Common.DataColumnMapping("EndDate",
"EndDate")})});

grdOrgs.DataSource = myDS;
grdOrgs.DataMember = "sessions";
grdOrgs.SetDataBinding(myDS, "sessions");
----------------------------------------------------------------------------------------------------------------------------------

If I comment out one, the active datagrid displays the right rows. If I
make both active, the first data grid displays the same rows as the
second datagrid. The only difference is the number of rows displayed.

Any ideas what I'm missing here?

 

[Cor Ligthert [MVP]]

Cousy,

Try first to understand the dataset model.

A dataset contains datatables and relations.

Therefore you would have to use one dataset with two datatables or to use
just two datatables. Not two datasets used as wrapper around your
datatables. That would make to find the solution of your problem already
much easier.

As well use with a datagrid: the datasource or the setdatabinding not both;
the first one will than be ignored.

As last try your problem without all those mappings, in my idea are you
mapping to the already existing mappings.

I hope this helps,

Cor

I have a windows form with two datagrid objects. The goal is to display
rows from the same SQL Server table, using different SELECT commands.

Here's a sample of the code:

Data Grid 1:
----------------------------------------------------------------------------------------------------------------------------------
strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate"
+ @" FROM sessions"
+ @" WHERE StartDate < GetDate()"
+ @" ORDER BY StartDate, ClassCode";

myCN = new System.Data.SqlClient.SqlConnection();
myCN.ConnectionString = frmMain.strConn;
myCN.Open();

myDA1 = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN);
myDS1 = new DataSet();
myDA1.Fill(myDS1, "sessions");

this.myDA1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "sessions",
new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ID", "ID"),
new System.Data.Common.DataColumnMapping("ClassID", "ClassID"),
new System.Data.Common.DataColumnMapping("ClassCode",
"ClassCode"),
new System.Data.Common.DataColumnMapping("StartDate",
"StartDate"),
new System.Data.Common.DataColumnMapping("EndDate",
"EndDate")})});

dataOrgs1.DataSource = myDS1;
dataOrgs1.DataMember = "sessions";
dataOrgs1.SetDataBinding(myDS1, "sessions");
----------------------------------------------------------------------------------------------------------------------------------

DataGrid 2:
----------------------------------------------------------------------------------------------------------------------------------
strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate"
+ @" FROM sessions"
+ @" WHERE Status = 'Open Reg'"
+ @" ORDER BY StartDate, ClassCode";

myCN = new System.Data.SqlClient.SqlConnection();
myCN.ConnectionString = frmMain.strConn;
myCN.Open();

myDA = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN);
myDS = new DataSet();
myDA.Fill(myDS, "sessions");

this.myDA.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "sessions",
new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ID", "ID"),
new System.Data.Common.DataColumnMapping("ClassID", "ClassID"),
new System.Data.Common.DataColumnMapping("ClassCode",
"ClassCode"),
new System.Data.Common.DataColumnMapping("StartDate",
"StartDate"),
new System.Data.Common.DataColumnMapping("EndDate",
"EndDate")})});

grdOrgs.DataSource = myDS;
grdOrgs.DataMember = "sessions";
grdOrgs.SetDataBinding(myDS, "sessions");
----------------------------------------------------------------------------------------------------------------------------------

If I comment out one, the active datagrid displays the right rows. If I
make both active, the first data grid displays the same rows as the
second datagrid. The only difference is the number of rows displayed.

Any ideas what I'm missing here?