Going Crazy with this!

[Merlin]

Hi Again Group,

Sorry to trouble you all again but i`ve been looking at this problem for 2
weeks now and just don`t seem to be able to grasp how to create a
relationship in VB.NET with ADO.NET.

I have the following:

Private Sub frmMainMenu_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
odaArcirisUsers.Fill(dsArcirisUsers)
Dim relArcirisUsers As New DataRelation("dsArcirisUsers",
dsArcirisUsers.Tables(0).Columns("SitesID"),
dsArcirisUsers.Tables(1).Columns("Sites Name"))
dsArcirisUsers.Relations.Add(relArcirisUsers)
Catch Ex As Exception
Console.WriteLine("Error: " & Ex.Message)
MsgBox("Error: " & Ex.Message)
End Try
dgdArcirisUsers.Expand(-1)
dgdArcirisUsers.NavigateTo(0, "ArcirisUsers")
End Sub

I have 2 Tables: 1 is My ArcirisUsers Table witch has the following Feilds:

ArcirisUserID (PrimaryKey)
POSID
USERNAME
BOMID
SITEID

Table 2 is My Sites IT which has:

SitesID(PrimaryKey)
SiteName
Company
Telephone

In my project I want to be able to update the feild on my ArcirisUsers Table
via Text Box`s. However I would like to have my SITEID on my Arciris Table
lookup the SITENAME in my Sites Table via a ComboBox, as it is doing in
access by a look up.

How do I do this im so confused, i`ve looked everywhere and seem to get
confused all the more, do i need 2 Dataadapters, and 2 Datasets of do i have
it all in the same Dataset?

Please Could someone help me?

Many Thanks
Regards
Merlin

 

[Norman Yuan]

See comment in line.

Hi Again Group,

Sorry to trouble you all again but i`ve been looking at this problem for 2
weeks now and just don`t seem to be able to grasp how to create a
relationship in VB.NET with ADO.NET.

I have the following:

Private Sub frmMainMenu_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
odaArcirisUsers.Fill(dsArcirisUsers)
Dim relArcirisUsers As New DataRelation("dsArcirisUsers",
dsArcirisUsers.Tables(0).Columns("SitesID"),
dsArcirisUsers.Tables(1).Columns("Sites Name"))
dsArcirisUsers.Relations.Add(relArcirisUsers)

Based on your description about the two tables, it seems that Table 2 is
the parent table, while Table 1 is the child table. That is, each Site
record in Table 2 may have one or many user records. So, the relation
should be other way around, opposite to what you defined. Also, how could
you create a relation between two different type of column: SiteName (I
suppose it is Text column) and SiteID (I suppose it is most likely,
AutoNumber column)? The relation shoud be

Dim rel=New
DataRelation("SitesToUsers",dsArcirisUsers.Tables([SiteTable]).Columns("Site
ID"),dsArcirisUsers.Tables([SiteUserTable]).Columns("SiteID"))

Catch Ex As Exception
Console.WriteLine("Error: " & Ex.Message)
MsgBox("Error: " & Ex.Message)
End Try
dgdArcirisUsers.Expand(-1)
dgdArcirisUsers.NavigateTo(0, "ArcirisUsers")
End Sub

I have 2 Tables: 1 is My ArcirisUsers Table witch has the following Feilds:

ArcirisUserID (PrimaryKey)
POSID
USERNAME
BOMID
SITEID

Table 2 is My Sites IT which has:

SitesID(PrimaryKey)
SiteName
Company
Telephone

In my project I want to be able to update the feild on my ArcirisUsers Table
via Text Box`s. However I would like to have my SITEID on my Arciris Table
lookup the SITENAME in my Sites Table via a ComboBox, as it is doing in
access by a look up.

How do I do this im so confused, i`ve looked everywhere and seem to get
confused all the more, do i need 2 Dataadapters, and 2 Datasets of do i have
it all in the same Dataset?

It is not clear what is confuse you here.
You can bind Site table to a ComboBox, so add/edit SiteUser record, user can
choose availabe site, while you can get the site's ID from the comboBox
easily:

ComboBox1.DataSourse=dsArcirisUsers.Tables(SiteTable);
ComboBox1.DisplayMember="SiteName"
ComboBox1.ValueMember="SiteID"

Now, user see a Site list in ComboBox, while you can get SiteID of use
selected Site:

Dim intID=CType(ComboBox.SelectedValue,Int32)