Why can't I write a simple SQL statement?

[MRM256]

I have a routine that returns the parent tables and their primary keys along with the name of the related table and its foreign keys. Like so:



I need to modify it t to return this:



The function is:

Public Function Get_OleDB_Tbl_Rel(ByVal strCnn As String, _
ByVal strTbl As String) _
As DataTable
'Purpose: Find all relationship information for
' the selected database table.
'Parameters: strCnn As String - A properly built
' OleDB connection string.
' strTbl As String - The table we are finding
' the relationships for.
'Returns: A DataTable of Relationships.
Dim schemaTable As DataTable
Dim restrictions() As String
Dim dr As DataRow
'Remove DataTable Columns from dt_Rel
Dim columncount As Integer = dt_Rel.Columns.Count - 1
For i = columncount To 0 Step -1
dt_Rel.Columns.RemoveAt(i)
Next
dt_Rel.Clear()
'Creating New columns for dt_Rel
dt_Rel.Columns.Add("PK_Tbl_Name", _
Type.GetType("System.String"))
dt_Rel.Columns.Add("PK_Col_Name", _
Type.GetType("System.String"))
dt_Rel.Columns.Add("FK_Tbl_Name", _
Type.GetType("System.String"))
dt_Rel.Columns.Add("FK_Col_Name", _
Type.GetType("System.String"))
Using cnn As New OleDbConnection(strCnn)
Try
With cnn
.Open()
'This restriction set only returns the relationships
'for strTbl.
restrictions = {Nothing, Nothing, Nothing, _
Nothing, Nothing, strTbl}

'No restrictions returns all table relationships
'in the database.
'restrictions = {}
schemaTable = .GetOleDbSchemaTable( _
OleDbSchemaGuid.Foreign_Keys, _
restrictions)
.Close()
'Loop through the schemaTable
For RowCount = 0 To schemaTable.Rows.Count - 1
If InStr(UCase(schemaTable.Rows(RowCount)! _
PK_TABLE_NAME.ToString), "MSYS") Then
Continue For
Else
dr = dt_Rel.NewRow
dr.Item("PK_Tbl_Name") = _
schemaTable.Rows(RowCount)! _
PK_TABLE_NAME.ToString()
dr.Item("PK_Col_Name") = _
schemaTable.Rows(RowCount)! _
PK_COLUMN_NAME.ToString()
dr.Item("FK_Tbl_Name") = _
schemaTable.Rows(RowCount)! _
FK_TABLE_NAME.ToString()
dr.Item("FK_Col_Name") = _
schemaTable.Rows(RowCount)! _
FK_COLUMN_NAME.ToString()
dt_Rel.Rows.Add(dr)
End If
Next RowCount
'Debug Purposes
'Call dt_Read(dt_Rel)
End With
Catch ex As Exception
'Log error
Dim el As New Log.ErrorLogger
el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
Return Nothing
End Try
End Using
Return dt_Rel
End Function

What I am looking for is the two parent table names. I could do this using a SQL query, but not anymore. The restrictions list does not allow for simple queries.

Anyone have any ideas?

Thanks,




MRM256

Continue reading...