Bind Data Grid to BindingNavigator

[CoachBarker]

Good Morning all

I am rying to write an applicaton in the 2.0 style, using data sources, Table Adapters and BindingNavigators. I have a form that loads and all my combo boxes load with the appropriate values when I navigate through the records. I would like the data grid to show the records based on the Dog ID, but can not figure out how to bind the data grid. In the old way I would call a method to fill the data grid based on the Dog ID passed in.



Also in the owner table, Fname and LName are stored separately and to display them both I would concatenate the string, the Display member will only allow me to add one source for the name.

Thanks
CoachBarker

 

[cjard]

OK, first off.. I know youre new so you'll get some things not quite right, but you should endeavour to use the correct terminology..

That's a DataGridView, not a DataGrid. DG exists, but you arent using it, so please make sure to accurately talk about what youre using..

You dont bind anything to a BindingNavigator; a BN is merely a component that moves the .Position of a BindingSource. A BS is a source of data, and is usually based on an underlying list or table because it has no storage capabilities of its own, it simply filters, orders, allows per-row viewing or the underlying data and maintains relationships.


Now, what you have here is a master/detail or parent/child relationship. Read the DW2 link in my sig, section on Creating a Simple Data App; that app features a relationship. There is another section called "Displaying Related Data" - read that too. It preps you for what comes next:

Expand the data sources tree and looks at your tables. Expand the parent table and look for the node representing the child table. Note that the child table apears in datasources twice. If you drag the main entry out, you get a grid that is bound directly to the child data. If you drag the nested entry out, you get a grid that is bound to the relation exposed by the parent bindingsource.
Note that in NO CASE does this mean that selecting a parent record will cuase the related chld records to be pulled down from the database. You must do this yourself! While it may seem like there is no benefit to establishing a relation if youre only going to have to fill the child data yourself based on selection of a parent, it is intended for use in situations where you want to download e.g. 10 parents and 100 children all at once, and then have .NET maintain the display of data for you. When you select 1 parent, .NET would show only the 10 related children to that parent. This can avoid multiple databsae fill calls at the expense of one larger fill at the start. Also In situations where a lookup table is being used or shown, or child records relate repeatedly to several columns in a parent, this approach makes sense.

After reading the tuts youmust decide:

Are you going to Fill only the child data for this particular parent as and when it is needed,
or are you going to fill child datas for several parents and have .NET swap them for you

In the former case, you have 2 bind sources bound to 2 data tables, 2 tableadapters and fill the child as a response in changing selection in the parent.
In the latter case you have 2 bind sources, the parent being bound to the parent table and thechild being bound to the datarelation exposed by the parent bindsource, you fill parent first, then children ahead of time using a loop something like:

parentDT.BeginLoadData() 'parentDT
parentTA.Fill(parentDT)
childDT.BeginLoadData
For Each ro as ParentRow in parentDT
  childTA.FillByParentID(childDT, ro.ID)
Next ro
childDT.EndLoadData()
parentDT.EndLoadData()

DT=datatable, TA=tableadapter

Finally, you ask how to establisha relation when the parent is FName, LName and child is Name?
Make another column on the parent, brand new column, NOT in the database, ONLY in the datatable (yep, DT dont have to reflect database) and set its .Expression to "[FName] + ' ' + [LName]" and establish the relaiton using that.

 

[cjard]

pps; having tables in a database that are related, but having them related on a calculated column is.. er.. dumbassed. Sorry. You'd actually be better off making an id column in the parent and a ParentID, ChildID column pair in the child.. Or reworking your tables so the child ID columns are FName LName, not Name. If you want Name, you can select it as a concat at time of query

 

[CoachBarker]

Thank you for the explanation, it clarified a few points. Unfortunately all my experience is in .net 1.1 and not .net 2.0, so it is a big jump for me. Also all my experience (until now) has been in using 3/4 tier architecture. I am used to creating all my forms and controls working out of the toolbox. All the things that 2.0 does automatically I can do myself in code. So the data binding and using data sources I will work on.

I think you misunderstood my question concerning the names and concatenating. In 1.1 if I wanted to concatenate the FName and the LName in a combo box I would do this :

Public Sub FillOwnerComboBox()
        ' error checking
        Try
            Dim ds As DataSet
            ' declare a new instance of the Owner class
            ds = clsEmployees.selectOwner 
            ' have all the Owners and their ID's now populate the combobox
            ' combo box and get the total number of records in the dataset
            Dim MaxRecords As Integer = ds.Tables(0).Rows.Count
            Dim i As Integer
            ' create a string to concatenate the first and last names
            Dim fullname As String
            cboFullName.BeginUpdate()
            For i = 0 To MaxRecords - 1
                ' populate the combo box with items from ds
                fullname = ds.Tables(0).Rows(i).Item(1) & " " & ds.Tables(0).Rows(i).Item(2)
                cboFullName.Items.Add(fullname)
            Next
            cboFullName.EndUpdate()
        Catch ex As Exception
            MsgBox("Error occurred in FillOwnerComboBox " & ex.Message)
        End Try
    End Sub

now if I want to do this in 2.0 using a data source I am totally lost. And I don't see where I have tables related on a calculated field. I have never done that. Here is my table layout so you can see what I mean.



Again, as I have said on the forums a few times, for people like me who need help and direction in doing things right, when we get stuck or can not rationalize how to do something, we come here and the experts such as yourself keep us going with your expert advice and examples.

Thanks once again
CoachBarker

ps I knew it was actually a DGV but in my haste called it a DG

 

[cjard]

now if I want to do this in 2.0 using a data source I am totally lost.

It's much simpler. You edit your DataTable in the designer to have a pseudo column. (nb: YOU DO NOT NEED TO ADD THAT COLUMN TO THE DATABASE!)

Go to DataSet designer
Right click the Owner datatable
Choose "Add Column"
Call it _FullName
Set its expression to [FName] + ' ' + 'LName']

Now, when the datatable is filled with data or the data is changed, that row will recalculate the value automatically.

Bind your combo up like (i'm guessing) this:

DataSource = dataset.Owner
DisplayMember = "_FullName"
ValueMember = "OwnerID"
SelectedValue bindings to Dog table, OwnerID column

Suppose you have ownerid=name of 1=John Smith, 2=Fred Jones
A dog row is on show with an ownerid of 1. The combo shows "John Smith"
You change the combo to Show "Fred Jones"
The DOG row is updated to have an owner ID of 2

Correct?

And I don't see where I have tables related on a calculated field. I have never done that. Here is my table layout so you can see what I mean.

OK, good.. Sorry I misunderstood you there. I thought Owner "John Smith" in the DOG table was the relation link to the columns "John" and "Smith" in the OWNER table

Again, as I have said on the forums a few times, for people like me who need help and direction in doing things right,

No problems

 

[cjard]

Note that a relation does NOT have to exist for this process above to work. The combo is responsible for reading 2 out of the dog table, looking up 2 in the vlauemember list, and showing "Fred Jones" from the displaymember list