none
VB.NET DataSet with Hierarchical Shape data, looses table names RRS feed

  • Question

  • I am attempting to use a MSDataShape attached to pretty much any grid control.  In the process of doing so I have noticed that the DataSet connector totally looses the base table information.  This screws up my use of grids.

    ====
    Dim ds As New System.Data.DataSet()

    Dim sqlq as string = "SHAPE {SELECT * FROM tblMyTableOne} AS table1 APPEND ({SELECT * FROM tblMyTableTwo} AS table2 RELATE ID to ID"


    Dim oConn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=#####;Integrated Security=SSPI;Initial Catalog=#####")

    Dim oAdapter As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlq, oConn)

    oAdapter.Fill(ds)

    ====
    The first table is always then named 'Table'
    Each table deeper is a concatenation of all tables related.

    ds.Tables.Item(0).TableName  = 'Table'
    ds.Tables.Item(1).TableName  = "tblMyTableTwo"


    I contacted a few grid vendors and since Shapes are 'on the fringe' they had no other users report issues or have any suggestions.

    I have hundreds of shapes.  It was suggested that after applying my dataset I loop through the Ds.tables.items and apply the real name.  This isn't dynamic nor scaleable.

    Any ideas?
    Tuesday, January 3, 2012 5:00 PM

Answers

  • Hi Ryan,

    Thanks for your feedback.

    To tell your truth, I'm not familiar with "SHAPE". As you say, you can use "adapter.fill(ds, "My custom Table Name")... " to set the first table. I do some research and I find there is no good way to dynamic to change the tables name, thanks for understanding.

    BTW, I found the link here: http://msdn.microsoft.com/en-us/library/ms676105(VS.85).aspx

    --------------

     

    This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.

    -------------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 5, 2012 9:08 AM
    Moderator

All replies

  • Hi Ryan,

    I think you can refer this link here: http://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.71).aspx

    The first table's name should be: Table1, the second table's name should be: Table1Table2

    -------------------------------------------

    Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter) can be used to fill the contents of a DataSet. When the DataAdapterencounters a chaptered column during a Fill operation, a DataTable is created for the chaptered column, and that table is filled with the columns and rows from the chapter. The table created for the chaptered column is named using both the parent table name and the chaptered column name in the form "ParentTableNameChapteredColumnName". If a table already exists in the DataSet that matches the name of the chaptered column, the current table is filled with the chapter data. If there is no column in an existing table that matches a column found in the chapter, a new column is added.

    -------------------------------------------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 4, 2012 8:19 AM
    Moderator
  • You are correct, my example I mis-spoke..

    ds.Tables.Item(0).TableName  = 'Table1'
    ds.Tables.Item(1).TableName  = 'Table1Table2'

     

    It's rather silly and I'm not exactly sure the point of the DS naming the tables such.  An argument might be if you want to know what the table names higher in the hierarchy are, you can string process, but you can simply refer to ds.Tables.Item(##).ParentRelations.....TableName.. etc...

    So why did they choose to have the table names borked in this fashion?  

    Why even have a property TableName if the name is going to be arbitrary, and/or wrong?    I can correct the initial table name by using adapter.fill(ds, "My custom Table Name")... however that seems really silly, non-dynamic, etc.  It should be able to ascertain this information right from the sql connection, etc, like an adodb.recordset can.

     


     


    Wednesday, January 4, 2012 6:26 PM
  • Hi Ryan,

    Thanks for your feedback.

    To tell your truth, I'm not familiar with "SHAPE". As you say, you can use "adapter.fill(ds, "My custom Table Name")... " to set the first table. I do some research and I find there is no good way to dynamic to change the tables name, thanks for understanding.

    BTW, I found the link here: http://msdn.microsoft.com/en-us/library/ms676105(VS.85).aspx

    --------------

     

    This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.

    -------------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 5, 2012 9:08 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side.Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 13, 2012 1:40 AM
    Moderator
  • I attempted to write a workaround.  After my fill command I loop through the ds.tables collection and fix the ds.tables.item(i).TableName.  I then present this dataset to my grids.  Sadly that appears to not work.  For whatever reason I can only change item 0 in the list, and have the setting stick.  

     

    =======

                ds.Tables.Item(0).TableName = oAdapter.InsertCommand.CommandText.Split(" "c)(2)

     

                Dim sTables(0) As String

                sTables(0) = "Table"

                For j As Int32 = 1 To ds.Tables.Count - 1

                    Console.WriteLine(ds.Tables.Item(j).TableName.ToString)

                    For k As Int32 = 0 To sTables.GetUpperBound(0)

                        If Left(ds.Tables.Item(j).TableName, Len(sTables(k))) = sTables(k) Then

                            ds.Tables.Item(j).TableName = Right(ds.Tables.Item(j).TableName, Len(ds.Tables.Item(j).TableName) - Len(sTables(k)))

                        End If

                    Next

                    If ds.Tables.Item(j).TableName <> "" Then

                        ReDim Preserve sTables(sTables.GetUpperBound(0) + 1)

                        sTables(sTables.GetUpperBound(0)) = ds.Tables.Item(j).TableName

                    End If

                Next

    =======

     

    I looked all over the dataset and could find no additional references to the table names, collection names, etc except the .TableName field.  Any ideas how I can modify my dataset's table names?

     

     

    Friday, January 27, 2012 6:36 PM