dotnet windows forms databinding: Bind Datagrid to Relation via Dataview -- dotnet windows forms databinding

RE: Bind Datagrid to Relation via Dataview

JT
5/11/2006 8:36:03 PM

dotnet windows forms databinding:

How about revisiting your join idea as follows:

SELECT
C.ChemID,
C.ChemDescription,
DI.InventoryDate,
DI.AmtInStock,
C.Discontinued --not sure where you're storing this?

FROM dbo.tblChemicals C LEFT OUTER JOIN
dbo.tblInventory DI ON
C.ChemID = DI.ChemID

WHERE (C.Discontinued = 0)

This would give you a dataTable with actual values for every
non-discontinued row in your Inventory table, plus the ChemID and Description
from the Chemicals table where Inventory is null. These last rows you could
use to allow your users to do inserts by just inserting a value for the
Inventory amount and date. The dataAdapter's Insert command would then look
like:

INSERT dbo.tblInventory(
ChemID,
InventoryDate,
AmtInStock
)
VALUES(
@ChemID,
@InventoryDate,
@AmtInStock
)

--
John

[quoted text, click to view]

Bind Datagrid to Relation via Dataview

ccshine via DotNetMonster.com
5/11/2006 10:08:37 PM

After searching throughout this and other sites trying variations on my code
to no avail, I confess. I was the second gunman on the grassy knoll! And my
eyes are about to bug out of my head trying to find a solution to this issue
so any help would be greatly appreciated.

I have a datagrid that displays info from 2 tables. The setup is quite
simple - chemicals contains the PK of chemicalID which is related to the
dailyInventory table FK chemicalID. 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 soul-
searching and prescription meds I learned that Join statements should be
abandoned and instead use a datarelation. Oh boy! Sounds like a piece of
cake!

I made all the adjustments needed - created OleDBAdapters for all 4 tables,
populated the dataset and created the relation. Now for the fun part.
Binding the datagrid 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 a few lines trying different options
based on info from various boards, but I can not get it to work. While it
isn't throwing any errors, I can only get the info from dailyInventory to
display. Oh yeah, I can also make nothing display, but I don't find this
extremely helpful.

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 grab the chemical table and loop
through it to add the description to the datagrid to simplify data entry as
well. However all I really care about at this point is getting that damn
description in there. I'm fairly new to .NET so if the mistake is painfully
obvious....

string strFilter;

// 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;

// production table
this.oleAdpProduction.SelectCommand = this.oleCmdProduction;
oleAdpProduction.Fill(dsChemicalTracking, "production");

// receiving table
this.oleAdpReceiving.SelectCommand = this.oleCmdReceiving;
oleAdpReceiving.Fill(dsChemicalTracking, "chemicalsReceived");

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

dgDailyInv.DataSource = dvDailyInv;
dgDailyInv.DataMember = "dailyInventory";

//dgDailyInv.SetDataBinding(dsChemicalTracking, "chemicals.
chemicalsToDailyInv");

// Declare DataGridTableStyle
// tsDailyInv.MappingName = dsChemicalTracking.Relations
["chemicalsToDailyInv"].RelationName;
//tsDailyInv.MappingName = dsChemicalTracking.Tables["dailyInventory"].
TableName;
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();
// }
//}

--
Message posted via DotNetMonster.com

RE: Bind Datagrid to Relation via Dataview

ccshine via DotNetMonster.com
5/12/2006 4:54:12 PM

Well that gives me a clue as to how the insert command for the data adapter
would need to be coded, but I've seen a lot of suggestions to not use Join
statements in leiu of dataRelations so I went that route. Which is the
preferred method?? Is there a performance or functionality difference?

Also, when I went with the Join statement I had difficulty getting the grid
to pull chemical descriptions from the parent table no matter how I
constructed the Join. I did manage to force that into submission, but not
the most elegant solution as I ended up get the descriptions from another
dataset I used elsewhere in the app. Of course, that was before I learned
you should only use one dataset too.

[quoted text, click to view]

--
Message posted via DotNetMonster.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the dotnet windows forms databinding group.