Datagrid from more than 1 table

uji

Member
Joined
Aug 23, 2006
Messages
7
Programming Experience
Beginner
I have 1 datagrid which source comes from 1 table. It is working. But I need to combine the table with other 2 tables as the main table only shows the key of the other 2. I need to get the description of the keys from the 2 tables. I know the sql command,and it uses dataview but I'm not sure how to implement it into datagrid. Below is the coding i use to get the data from 1 table to the datagrid.I use VStudio 2003. I'm a beginner, therefore if someone could explain and show me the url that shows the coding to combined few tables into the datagrid, I will appreciate it very much.

Private Sub LoadGrid()
Dim data As DataTable = OgManager.GetData(" status <> " & eStatus.Deleted & " AND SerialNo like '%" & txtSearchbySerialNo.Text & "%'")
grdData.DataSource = data
Dim dsn As String = System.Configuration.ConfigurationSettings.AppSettings("dsn")
End Sub

Public Shared Function GetData(ByVal criteria As String) As DataTable
Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
Dim query As String = "select * from SerialNo"
If criteria <> "" Then
query = query & " where " & criteria
End If
Dim Dt As DataTable = DataManager.ExecuteQuery(ConnectionString, query, "SerialNo")
Return Dt
End Function

Public Shared Function ExecuteQuery(ByVal ConnectionString As String, ByVal query As String, Optional ByVal tableName As String = "Table1") As DataTable
Try
Dim myConnection As OleDbConnection = New OleDbConnection(ConnectionString)
Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, myConnection)
Dim ds As DataSet = New DataSet
myAdapter.Fill(ds, tableName)
ds.Tables(0).TableName = tableName
Return ds.Tables(0)
Catch ex As Exception
Try
Utils.LogError(ex, "Error in ExecuteQuery() method in DataManager. <BR><BR>" & query)
Catch
End Try
Throw
End Try
End Function
 

Anti-Rich

Well-known member
Joined
Jul 1, 2006
Messages
325
Location
Perth, Australia
Programming Experience
1-3
couldnt you simply specify in the select query the data from all tables?

ie.

SELECT i.Field1, i.Field2, a.Field1, a.Field2 FROM Table1 i, Table 2 a WHERE i.Field2 = a.Field1

and then bind the dataset to the data grid as normal

hope that helps

regards
adam
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,728
Location
Sydney, Australia
Programming Experience
10+
As Anti-Rich says, use a join. Here's an example of the more modern format for a join:
VB.NET:
SELECT Child.ID, Parent.Name AS ParentName, Child.Name FROM Parent INNER JOIN Child ON Parent.ID = Child.ParentID
Note that the second column will now contain the parent's name instead of the parent's ID, which it would if you'd selected the Child.ParentID column. Depending on what you need to do with the data you may well still want to get the child's parent ID too, but just not display it in the grid.
 

uji

Member
Joined
Aug 23, 2006
Messages
7
Programming Experience
Beginner
Appreciate all the feedbacks ..it is now working...All I need to do is just change the sql command....
 
Top Bottom