Bind Relation to DataGrid Problem

[ccshadow]

I have a datagrid that displays info from 2 tables - the chemicals table contains chemicalID (PK), description, etc. and dailyInventory contains inventoryID (PK), date, chemicalID (FK), etc. In my first pass at displaying this data I used a JOIN statement. When I finally got it to display the way I wanted, I ran into issues trying to update with the OLEDBAdapter. After some investigation I learned that Join statements should be avoided and instead use a DataRelation.

I made all the adjustments needed, but can't get the datagrid to bind to the relation so I can display the "description" column from the chemicals table with my data from the dailyInventory table. You'll note that I have commented out quite a few lines trying various options. While it isn't throwing any errors, I can only get the info from dailyInventory to display with no control over column headings, and no description from the chemicals table.

In addition to getting the "description" field to display with dailyInventory, I need to do a RowFilter based upon date. AND if there are no entries for a particular date then I to list only the chemical descriptions to simplify data entry as well. I'm fairly new to .NET so...

// chemicals table
this.oleAdpChemicals.SelectCommand = this.oleCmdChemicals;
			oleAdpChemicals.Fill(dsChemicalTracking, "chemicals");
dvChemicals = dsChemicalTracking.Tables["chemicals"].DefaultView;

// dailyInventory table and dataview
this.oleAdpDailyInv.SelectCommand = this.oleCmdDailyInv;
oleAdpDailyInv.Fill(dsChemicalTracking, "dailyInventory");		
dvDailyInv = dsChemicalTracking.Tables["dailyInventory"].DefaultView;
strFilter = "date='" + dtpDate.Value.ToShortDateString() + "'";
dvDailyInv.RowFilter = strFilter;

// add relations
DataColumn chemicalCol = new DataColumn();
DataColumn dailyInvCol = new DataColumn();
chemicalCol = dsChemicalTracking.Tables["chemicals"].Columns["chemicalID"];
dailyInvCol = dsChemicalTracking.Tables["dailyInventory"].Columns["chemicalID"];
DataRelation chemicalsToDailyInv  = new DataRelation("chemicalsToDailyInv", chemicalCol, dailyInvCol);
dsChemicalTracking.Relations.Add(chemicalsToDailyInv);

dgDailyInv.SetDataBinding(dsChemicalTracking, "chemicals.chemicalsToDailyInv");
//dgDailyInv.DataSource = dvDailyInv;
//dgDailyInv.DataMember = "dailyInventory";

// Declare DataGridTableStyle
//tsDailyInv.MappingName = dsChemicalTracking.Relations["chemicalsToDailyInv"].RelationName;
//tsDailyInv.MappingName = dsChemicalTracking.Tables["dailyInventory"].TableName;

tsDailyInv.AlternatingBackColor = Color.FromArgb(255, 153, 102);

			
tcDate.MappingName = "date";
tcDate.HeaderText = "Date";
tcDate.Width = 100;
tcDescription.MappingName = "chemicals.description";
tcDescription.HeaderText = "Chemical";
tcDescription.Width = 125;
tcAmtInStock.MappingName = "amtInStock";
tcAmtInStock.HeaderText = "Amt In Stock";
tcAmtInStock.Width = 100;
			
tsDailyInv.GridColumnStyles.Add(tcDate);
tsDailyInv.GridColumnStyles.Add(tcDescription);
tsDailyInv.GridColumnStyles.Add(tcAmtInStock);

// Add the DataGridTableStyle instance to the GridTableStylesCollection
dgDailyInv.TableStyles.Add(tsDailyInv);
dgDailyInv.Expand(-1);

// get chemical names for dailyInventory entry
//if (dvDailyInv.Count.Equals(0))
//{
//	strFilter = "discontinued = 0";
//	dvChemicals.RowFilter = strFilter;
				
//	foreach (DataRowView drChemicals in dvChemicals) 
//	{
					
//drDailyInv = dvDailyInv.AddNew();
//drDailyInv["date"] = dtpDate.Value.ToShortDateString();
//drDailyInv["description"] = drChemicals["description"];
//drDailyInv["amtInStock"] = 0;
//drDailyInv.EndEdit();
//	}
//}

 

[ccshadow]

Correction: I am unable to get any data to display in the grid via the relation.

 

[CrashDome]

If I am reading this correctly (and I might not be), you are trying to get data from two tabless to display in the datagrid at once.... which doesn't quite work the way you think it does with DataGrids.

DataGrids show datarows from one table at a time. If you specify a relation, you will see a little + next to the rows of the parent table and you can navigate through to the child table and see the child rows (one-to-many relationship).

If you are just trying to get the amount in inventory to display with your chemicals table, I would just programmatically add another column to your chemicals datatable and fill in the appropriate amount for each one. Don't worry about updates either. If your adapter is done correctly, it will only update the columns that are actually in the real table and will skip the "Amount" column.

Example:

dsChemicalTracking.Tables["chemicals"].Columns.Add("Amount", typeof(System.Int32));

//Cycle through each chemical datarow
foreach (DataRow chemRow in dsChemicalTracking.Tables["chemicals"])
{
  //Skips the idea of the DataRelation and Selects the appropriate child rows instead
  DataRow[] inventoryRows = dsChemicalTracking.Tables["dailyInventory"].Select("chemicalID = "+chemRow["chemicalID"].ToString());
  //Cycle through the child inventory rows
  foreach (DataRow inventoryRow in inventoryRows)
  {
    //Put amount in chemical rows new "Amount" column
    dr["Amount"] = inventoryRow["amtInStock"];
  }
}

You could do it vice-versa and put the description in the inventory Rows.

Now, this method is very crude but will get what you want at the expense of performance. If I were to do this, I would really use the JOIN statement in my Stored Procedure and then write an update command to only update the proper columns to the inventory table (or chemical.. whichever it is).

You probably didn't want to hear that as it may seem to just give you the run-around, but it's what I would personally do.

Perhaps there is a better way with relations... if so, I do not know of it (I am not the most proficient .NET programmer)

 

[ccshadow]

My explanation obviously wasn't the greatest, but you were able to surmise it pretty closely. I'm actually trying to get the chemical description from the chemical table to show up in the inventory grid instead of just the chemicalID that is stored in the inventory table.

I played around with the relation approach a bit more by adding a column to the inventory dataview and did get a little further. It now allows me to manipulate the column headers, but the chemical description column displays <null>, indicating that the relationship isn't really working. I read somewhere about creating an expression for this new column - just haven't had time to figure it out. And wondering if after I struggle to get it figured out that I'll have the same issue with the update since the chemical description would then need to be translated to chemicalID for any new rows.

Perhaps it's because I don't understand this yet and haven't found an impressive amount of info about it, but this seems like a lot more work than pre-ADO.NET using Join statements. I would think this is a fairly common task.

Thanks for the help, CrashDome.

 

[CrashDome]

Alwyas glad to help but read my post above again. Relations do not work the way you think they do. They are not substitutes for a JOIN statement. You are adding an extra column to a datatable and expecting the relation to "fill" the column just because there is a relation and the name is the same. This is not really going to happen. The real work behind the DataRelation is that it returns rows of the child table that meet the constraints of the relation and do not simple merge child and parent data together. Use the method I suggested to do this programmatically.

but...

Unless I had a full Business Logic layer and Data Layer, I would be using the JOIN statement.

 

[ccshadow]

Ohhhh. Now that I look back at yours and some of the postings I saw elsewhere I realize that I missed that distinction in relationships and using the DataRelation. Definitely back to the Join statements!

Thanks a bunch!!