Help with DataRelation and DataGrid

[SonicBoomAu]

Hi All,

Am unsure If I am doing this correctly.
I have a Access DB, I have 2 Tables (StaticAssets) & (IssuedAssets). Static Assets contains all the Assets Details (i.e AssetNo, Equip0, Ser0, Qty0, Type, Classification, SystemName, OS, etc) Issued Assets contains all the info from StaticAssets plus the Issued information (i.e. SigneeName, CourseName, CourseDates, SigneeWing, SignOutDate). Issued Asset contains only the Assets that are issued out.

What I am trying to do is list all the Static Assets in a datagrid, (No Problems There). If any of those assets are Issued then have a plus sign and display the issued info.

Heres my Code so far.

[VB]
Set strDatabaseName
strDatabaseName = "LocationofDB"

Dim cnAdoNetConnection As New OleDb.OleDbConnection
Dim cCommand As New OleDb.OleDbCommand
Dim daDataAdapter As New OleDb.OleDbDataAdapter
Dim ds As New DataSet

Dim strSqlQuery As String
Dim strSqlQuery1 As String
Dim strSqlQuery2 As String
Dim strSqlQuery3 As String

strSqlQuery = "AssetNo, Equip0, Ser0, Qty0, Type, Classification, SystemName, OS"
strSqlQuery1 = "SELECT " & strSqlQuery & " From StaticAssets"
strSqlQuery2 = "AssetNo, SigneeName, CourseName, CourseDates, SigneeWing, SignOutDate"
strSqlQuery3 = "SELECT " & strSqlQuery2 & " From IssuedAssets"

Try

Open the connection to the database
cnAdoNetConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strDatabaseName
cnAdoNetConnection.Open()

Retrieve all the Permanent Asset Information
daDataAdapter = New OleDb.OleDbDataAdapter(strSqlQuery1, cnAdoNetConnection)

daDataAdapter.Fill(ds, "StaticAssets")

cCommand.CommandText = strSqlQuery3
daDataAdapter.Fill(ds, "IssuedAssets")

Dim rel As New DataRelation("View Issued Assets", _
ds.Tables("StaticAssets").Columns("AssetNo"), _
ds.Tables("IssuedAssets").Columns("AssetNo"))
ds.Relations.Add(rel)


DataGrid1.DataSource = ds.Tables("StaticAssets")

Close the connection to the database
cnAdoNetConnection.Close()

Catch ex As Exception
MessageBox.Show("An error has occurred! Error: " & ex.Message, _
"DIntTC Asset Management", MessageBoxButtons.OK, MessageBoxIcon.Error)

Close the connection to the database
cnAdoNetConnection.Close()

End Try
[/VB]

The staticassets info displays in the daatagrid. But all the Assets have plus signs when only 3 are currently Issued. And when you navigate down it displays the same information not the information I require (strSqlQuery2).

Please Help.

 

[SonicBoomAu]

Happy Days

Never fear I have found the solution.

Link

Which has a great example of how to setup a Data Relationship between two tables. So I will also post there (Programmer = Pirate) code here for the next person.

[VB]
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Path to database
Dim dbpath As String = Application.StartupPath & "\mydb.mdb"

Connection obj to database
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & ";Jet OLEDBatabase Password=")

Open the Connetion
conn.Open()
Dataset that holds data in disconnected mode
Dim ds As New DataSet

Two commands for two tables (tab1 and tab2)
Dim cmd1 As OleDbCommand
Dim cmd2 As OleDbCommand

Two datapaters to fill the dataset from two tables
Dim adp1 As OleDbDataAdapter
Dim adp2 As OleDbDataAdapter

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

Its not important but gives your code more better way to
compare strings between tables
ds.CaseSensitive = True

First command for first table
cmd1 = New OleDbCommand
cmd1.Connection = conn
cmd1.CommandText = "SELECT * FROM Tab1"


Second command for Second table
cmd2 = New OleDbCommand
cmd2.Connection = conn
cmd2.CommandText = "SELECT * FROM Tab2"

Now , we will fill the first table and add it to the dataset
adp1 = New OleDbDataAdapter
adp1.SelectCommand = cmd1
adp1.TableMappings.Add("Table", "Tab1")
adp1.Fill(ds)


As we did in the previous step , here for the Second table
adp2 = New OleDbDataAdapter
adp2.SelectCommand = cmd2
adp2.TableMappings.Add("Table", "Tab2")
adp2.Fill(ds)



dc1 = ds.Tables("Tab1").Columns("ID")
dc2 = ds.Tables("Tab2").Columns("ID")

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

Simple one , bind the dataset after all operation to the
Datagrid
DataGrid1.DataSource = ds.DefaultViewManager
Show the first table in the grid because its the primary table
DataGrid1.DataMember = "tab1"

Thats all folks
Pirate

End Sub
[/VB]

 

[sunu_moon]

I have used your code but I have not get actual result.

Hello

I have used your code with database MS-SQL.I have used two tables CRM_EXCHANGE_USER_INFO (Primary table) and CRM_TRANSFERRED_OUTLOOK_DATA(Secondary Table) with column USER_EMAIL_ID as Common to both table.I have given my code below to you.
But when I run application It will only show error DataGrid with id DataGrid1 could not automatically generate any columns from the selected data source
Is anything of datagrid properties have to change.
Pls tell me detail.
Wait for your response as soon as possible.
Thanks

[VB]
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Put user code to initialize the page here
Dim sqlcon As New SqlConnection
sqlcon.ConnectionString = "data source=Earth1;initial catalog=crmdbrosepm;uid=sa;pwd=$rose"
sqlcon.Open()

Dim cmdString As String = "SELECT * FROM CRM_EXCHANGE_USER_INFO"
Dim dataAdapter As New SqlDataAdapter(cmdString, sqlcon)
Dim ds As New DataSet
dataAdapter.Fill(ds, "USER_INFO")

cmdString = "SELECT * FROM CRM_TRANSFERRED_OUTLOOK_DATA"
dataAdapter = New SqlDataAdapter(cmdString, sqlcon)
dataAdapter.Fill(ds, "OUTLOOK_DATA")

Dim relation As New DataRelation("TYPE_SUBTYPE", ds.Tables("USER_INFO").Columns("User_Email_ID"), ds.Tables("OUTLOOK_DATA").Columns("User_Email_ID"))
ds.Relations.Add(relation)


Dim dv As New DataView(ds.Tables("USER_INFO"))
DataGrid1.DataSource = dv
DataGrid1.DataBind()


Dataset that holds data in disconnected mode
Dim ds As New DataSet

Two commands for two tables (tab1 and tab2)
Dim cmd1 As SqlCommand
Dim cmd2 As SqlCommand

Two datapaters to fill the dataset from two tables
Dim adp1 As SqlDataAdapter
Dim adp2 As SqlDataAdapter

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

Its not important but gives your code more better way to
compare strings between tables
ds.CaseSensitive = True

First command for first table
cmd1 = New SqlCommand
cmd1.Connection = sqlcon
cmd1.CommandText = "SELECT * FROM CRM_EXCHANGE_USER_INFO"


Second command for Second table
cmd2 = New SqlCommand
cmd2.Connection = sqlcon
cmd2.CommandText = "SELECT * FROM CRM_TRANSFERRED_OUTLOOK_DATA"

Now , we will fill the first table and add it to the dataset
adp1 = New SqlDataAdapter
adp1.SelectCommand = cmd1
adp1.TableMappings.Add("Table", "CRM_EXCHANGE_USER_INFO")
adp1.Fill(ds)


As we did in the previous step , here for the Second table
adp2 = New SqlDataAdapter
adp2.SelectCommand = cmd2
adp2.TableMappings.Add("Table", "CRM_TRANSFERRED_OUTLOOK_DATA")
adp2.Fill(ds)

dc1 = ds.Tables("CRM_EXCHANGE_USER_INFO").Columns("User_Email_ID")
dc2 = ds.Tables("CRM_TRANSFERRED_OUTLOOK_DATA").Columns("User_Email_ID")

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

Simple one , bind the dataset after all operation to the Datagrid
DataGrid1.DataSource = ds.DefaultViewManager
Show the first table in the grid because its the primary table
DataGrid1.DataMember = "CRM_EXCHANGE_USER_INFO"
DataGrid1.DataBind()
End Sub
[/VB][/QUOTE]