Datagrid data-bind

Will anyone show me how to bind multiple tables into one datagid? I have looked at the documentation on datagrid binding but I'm not quite sure if I got it right because it's not working. Here's what I want to do. I want to populate a datagrid with some data from my mdb file and if a user click on the plus sign on the datagrid cell, the datagrid shows another table or columns that's related to that cell column. I'm not sure how to go about accomplishing this. Any pointer will be greatly appreciated!

ljCharlie

Use datarelations. I put something when I get home.

Thank you very much for your suggestion. I'll check on the use of datarelation.

ljCharlie

By the way, here' s what I have. I have used the wizard to create my DataAdapter and gerneate my dataset. I have two dataAdapter, DAcomputer and DAProfile, and two DataSet, DScomputer and DSProfile. The parent table is in the DSProfile and the child table is in the DScomputer. The problem now is how do I display both of them in one datagrid and navigate to the child table.

I'll check back later. Again, thanks!

ljCharlie

Ok , I created little demo for you , Check it out . It should help you throughly . It's VS.NET 2003 proj and database MS Access XP .

Thanks! I'll look at the demo.

ljCharlie

Pirate, I couldn't open your demo file. Will you use .zip instead of .rar? I receive an error of Bad Header when I open your .rar file.

ljCharlie

Winzip can unpack it also :confused: . Anyways , it's here zipped .

Questions, Pirate. If I don't have the same amount of records or coresponding records in the two tables, I can't use the datarelation, correct? The reason I ask is because I receive an error "This constraint can not be enable as not all values have corresponding parent values. " If this is true then here's my situation see if you can help me. In the datagrid it has seven columns. The first two columns are Serial# and ComputerName . When a user click the cell in these first two columns, I want a drop down list, popup window, or anything that will let the user choose a serial # and computer name from another table. In this way the user don't have to remember all the serial number and computer name of all the computers. At first, I thought of using a popup windows form but I couldn't figure out how to double a datagrid cell. Then I thought of using datarelation but because of above stated problem. What do you think is the best way?

Many thanks for your time and help.

ljCharlie

Well ...I believe you don't need the same amount of data as you said in both parent and child tables but you do need the relation between them which is basis in linking them with datarelation obj . It's better not to move to the next part till you fix this problem . I dunno what your database looks like , but if you can zip it up here , we can try to fix what's wrong .

Here's my mdb file. I want to use the table Program as parent and UserProfile as child. The Program table is going to be edit and update by user in the datagrid.

Here's the code I used:

Quote:

---

'This handles the relationship between the two columns
Dim datarelation As DataRelation
Dim dc1 As DataColumn
Dim dc2 As DataColumn

'It's not important but gives your code more better way to compare strings between tables

DsProgram.CaseSensitive = True
'Now , we will fill the first table and add it to the dataset
DAProgram.TableMappings.Add("myTable", "Program")
DAProgram.Fill(DsProgram)

'As we did in the previous step , here for the Second table
ProfileDataAdapter.TableMappings.Add("myTable", "UserProfile")
ProfileDataAdapter.Fill(DsProfile)

dc1 = DsProgram.Tables("Program").Columns("PSerial")
dc2 = DsProgram.Tables("UserProfile").Columns("DPSerial")

'Here we combined two datacolumns to the relations obj
datarelation = New DataRelation("Tab1andTab2", dc1, dc2)
DsProgram.Relations.Add(datarelation)

'Simple one , bind the dataset after all operation to the Datagrid
dgComputer.DataSource = DsProgram.DefaultViewManager
'Show the first table in the grid because it's the primary table
dgComputer.DataMember = "PSerial"

---

And here's the error:

Quote:

---

An unhandled exception of type ‘System.NullReferenceException’ occurred in Computers.exe

Additional information: Object reference not set to an instance of an object.

---

This error occured on this line:

dc2 = DsProgram.Tables("UserProfile").Columns("DPSerial")

dgComputer is my datagrid
dsProgram is my Program table dataset
ProfileDataAdapter as my dataAdapter is for UserProfile table
DAProgram is my dataAdapater is for my Program table

ljCharlie

How come you are using datarelation but you don't have any relation between your tables ?? You need to reconsider your database structure .

Try this version . I linked them , it might work .

First, how did you do it? Second, when I click on the +sign in Access, I don't see the second table's contents shown. Why is that?

ljCharlie

Quote:

---

Originally posted by Chong
First, how did you do it?

---

Simple one , Open your database , go to tools menu click relationships , then you will see all the tables , choose the ones you need to make relation with . Then , drag the column from left table that relate to the one on the right table (must have the same data type ) . A popup window appears asking you if you wish to add the relation , press Yes or ok . You will see a line between the two columns . That's how to do relations in a database .

Quote:

---

Second, when I click on the +sign in Access, I don't see the second table's contents shown. Why is that?

---

Relation , yes again relationship . If you have a number that exists in the [Program] table , field [PSerial] and this number also exists in the [UserProfile] table , field [DPSerial] , then you can they will show in the parent table [program] . Try this :

Add '1' to the column [PSerial] in [program] table and complete the blank field if you like .

go to table [UserProfile] and add also '1' in column [DPSerial] and complete empty spaces . Save the changes . Use this database you should see the child records from within the parent in the datagrid . Let me know if you are stuck .

I see! Maybe this method is not what I want in my situation then. The reason is, I want table UserProfile to be used as lookup table for information to be inserted into table Program. Your explaination made me understand that by using relationship, the value in both field of those two tables have to be matched to be able to display the child table and visversa. And what I want to do is for a user to click the datagrid cell of table Program cell [Pserial] and the table, UserProfile, will showup with all its information for the user to select the proper serial# and then click OK or close that table and the first two fields of the selected row of that table, UserProfile, will be inserted into the Program table's first two fields. So I guess now my question would be, how do I catch a double click inside a datagrid's cell so I can lanch another windows form that will display the userProfile table for insertion? I know there is a double click event for datagrid but not for datagrid cell. How do I launch something when I double click the datagrid cell?

Thanks for your time and help.

ljCharlie

Forgive me to say you are wrong . You can always count on relations to normalize your data . Man , I used to say : relations are bull****' . I admit it , I was wrong too. So , if were in a situation you feel you can't use relation in you database design , then probably the problem is you not with relation . Ok , it's your way to decide . In any case , would this work instead of double clicking DataGrid cell ? It works well and nicely for me .

VB Code:

Private Sub DataGrid1_CurrentCellChanged(ByVal sender As 
Object, ByVal e As System.EventArgs) Handles 
DataGrid1.CurrentCellChanged
 
        'Here , you can catch the selected cell , instead of doubleclick 
        'event . Get cell info and do your stuff .
        MessageBox.Show("Column is " & DataGrid1.CurrentCell.ColumnNumber _
        & ", Row number is " & DataGrid1.CurrentCell.RowNumber _
        & ", Value is " & DataGrid1.Item(DataGrid1.CurrentCell))
    End Sub

Thanks for the info. Will you explain, then, how you would go about using relation in my situation? With the little knowledge I have about relation, I feel that I can't use relation in my design. This is just so I understand and can appreciate relation.

ljCharlie

OK , You probably need to explain more about your database and what info you are going to store in ??

Okay. One table, Program, will be storing information that's regarding any program changes in a computer. This table include the following columns.

PSerial
Name
Version
Time
Date
What
Addtn_Info

The other table is UserProfile with the following columns:

DPSerial
ComputerName
User
IP_Address
Speed
Memory
HD1
HD2
HDType
OS

Now, the table UserProfile is already filled with information about computers we have. On the other hand, table Program is not. This table, Program, will be used for any changes in our computers' software. For example, if a service pack version 4 is update to, let's say computer name gw2k11 with serial# 0001, then this table, Program, is the table that I will use to store this information. The problem is, I want to be able to click the + sign of the datagrid cell [PSerial] and [Name] and the table UserProfile will show up as a child table or as a popup where I can select the [DPSerial] and [ComputerName] that will ultimately be inserted into the cells of [PSerial] and [Name] of table Program. And then I will hit the update button of the form to update the table of Program.

Let me know if I'm still unclear.

ljCharlie

Looks like the same database you uploaded , doesn't it ? If so , I can make some changes on it then .

Yes!

ljCharlie

I dunno if this would solve your problem but you need to find common factor between the two tables . In sense , some value that exists in both tables . I put [PSerial] field as your primary key and foriegn key in the [UserProfile] table (I dunno if this makes sense in your design but I dunno what these fields stand for) . Check this database now .

Thanks! How do I hide certain columns of the table in Access so when I open the tabe, that colun does not show.

ljCharlie

If you mean the column doesn't show up in access , then I dunno or probably you can't do it there .

Many thanks for the help!

ljCharlie