Link to home
Start Free TrialLog in
Avatar of chlade
chlade

asked on

determine table name

I am writing a routine that will be called from many programs.  I will pass into it a parameter that is a SQL command string.

For instance:
SELECT SupplierMaster.*, SupplierAddress.*
FROM SupplierMaster INNER JOIN SupplierAddresses ON SupplierMaster.Supplier = SupplierAddresses.Supplier"

My routine will display a grid showing all returned rows.  This all works great.

However, there is some additional code I'd like to add and for this I will need to know the table that each column comes from.

I will cycle through all returned fields and need to know the table the field is from.  For instance, in the query above, there will be a field called Supplier in each table.  For each instance of the Supplier field, I will need to know the table name that it is from.

Is there a way to find the name of the table a field is from?

Thanks,
Chris
Avatar of jake072
jake072
Flag of Canada image

Well If your cycling through your ROWS, and you have them in a DataSet, the easiest way to figure out what Table a given row belongs to is:

Dim dr As DataRow = [YourRow]
Dim dt As DataTable = dr.Table
Dim strTableName As String = dr.Table.TableName

Let me know if you need more help,

Jake
Avatar of arif_eqbal
arif_eqbal

Hi
As far as I know, you can't figure that out,(I might just be wrong). For the duplicate column "Supplier" though, the ColumnName property would be SupplierMaster.Supplier or SupplierAddresses.Supplier (with SQLClient or OLEDB, not sure for others) so you can know which underlying table the column is from, but not for others. What you can do is Modify your Query and use "Aliases" eg.
Select SupplierMaster.Field1 As SM_Field1, SupplierMaster.Field2 As SM_Field2, SupplierAddresses.SomeField As SA_SomeField ....
so that you know which Table it corresponds to, using the first 2 Chars of the Column Name.
Avatar of chlade

ASKER

Jake, unfortunately I will have joined tables populating a single row so I don't think that will work.

As for using aliases, I'm not sure that will work as I will need to select using "*".

I know that I can get field information such as type and size using dr.GetSchemaTable.  Unfortunately, it doesn't include the source table.
Have you tried using my suggestion?

Try it, and let me know...  If it doesn't work, then we can try and figure something out that will work.

Jake
Avatar of chlade

ASKER

I did, but it didn't look like it would work.  I have to run to a meeting but will post my code later.
Avatar of chlade

ASKER

I guess where I got stuck is that my datarow (ie. a row in my dataset) is derived from more than one table.  I'm not sure how to copy this dataset row to a datarow (whether that's even possible) and if so, how it will know the table since the table appears to be at the datarow level.  Perhaps I'm looking at this wrong.

The following is a test program that dumps all information about a SQL Query into the clipboard.  Paste the code into a new project, run it, and then open Excel and do a paste.  Of course, you'll have to adjust to point at tables on your system.  Be sure to do a JOIN.  You'll see all information on each column.

However, it doesn't include the table name and this is what I'm after.

Imports System.Data
Imports System.Data.SqlClient

Module Module1

    Sub Main()

        Dim SqlString As String = "SELECT ApSupplier.*, ApSupplierAddr.* " & _
            "FROM ApSupplier INNER JOIN ApSupplierAddr ON ApSupplier.Supplier = ApSupplierAddr.Supplier"

        Dim cn As New System.Data.SqlClient.SqlConnection( _
            "Server=Phoenix;Database=CompanyA;Uid=SysproUser;Pwd=123;")
        cn.Open()
        Dim cmd As New System.Data.SqlClient.SqlCommand(SqlString, cn)
        Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
        Dim dt As DataTable = dr.GetSchemaTable

        Dim msg As String = ""

        For y As Integer = 0 To dt.Columns.Count - 1
            msg &= dt.Columns(y).ColumnName & ControlChars.Tab
        Next
        msg &= ControlChars.CrLf

        For x As Integer = 0 To dt.Rows.Count - 1
            For y As Integer = 0 To dt.Columns.Count - 1
                msg &= dt.Rows(x).Item(y).ToString() & ControlChars.Tab
            Next
            msg &= ControlChars.CrLf
        Next

        cn.Close()

        Clipboard.Clear()
        Clipboard.SetText(msg)

        MessageBox.Show("Information copied to clipboard")

    End Sub

End Module
ASKER CERTIFIED SOLUTION
Avatar of arif_eqbal
arif_eqbal

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
arif_eqbal,

Upon further investigation, your right.  With a JOIN, there's no way around it (at least nothing that is easy and obvious).  I would recommend your solution, it's the only thing that I could get to work last night.

Good luck,

Jake
Avatar of chlade

ASKER

After digging a little more, unfortunately, I must agree.  It looks like I'll have to use a work-around of some sort.