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.

 

Here's my Code so far.

 

' 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

 

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 Day's

 

Never fear I have found the solution.

:D :D

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.

 

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

       'It's 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 it's the primary table
       DataGrid1.DataMember = "tab1"

       'That's all folks 
       'Pirate

   End Sub

[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

 

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

       'It's 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 it's the primary table
       DataGrid1.DataMember = "CRM_EXCHANGE_USER_INFO"
       DataGrid1.DataBind()
   End Sub