Assign datatable from DataSet

I want to assign a datatable to another datatable (create from a datasoruces)

            Dim mdtPO As New OrderTrackingDataSet.OrderMasterDataTable         '<---  data table created by datasources
            cnn.Open()
            mDA = New System.Data.OleDb.OleDbDataAdapter("Select * from " & mTableName, mCnn)
            mDA.Fill(mDS, mTableName)
            mCB = New System.Data.OleDb.OleDbCommandBuilder(mDA)
            mdtPO = mDS.Tables(mTableName)
            cnn.Close()

However I got the following error message !
"Unable to cast object of type 'System.Data.DataTable' to type 'OrderMasterDataTable'."

So how can I assign datatable "mDS.Tables(mTableName)" to mdtPO ?

Thanks in advance!

tora111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
try this:

     Dim mdtPO As New OrderTrackingDataSet.OrderMasterDataTable         '<---  data table created by datasources
     cnn.Open()
     mDA = New System.Data.OleDb.OleDbDataAdapter("Select * from " & mTableName, mCnn)
     mDA.Fill(mDS, mTableName)
     mCB = New System.Data.OleDb.OleDbCommandBuilder(mDA)
     mdtPO = cType( mDS.Tables(mTableName), OrderTrackingDataSet.OrderMasterDataTable)
     cnn.Close()

AW
tora111Author Commented:
Arthur_Wood,

It got the same error "Unable to cast object of type 'System.Data.DataTable' to type 'OrderMasterDataTable'."

It seems cannot assign to a datataboe to  a datatable(from data sources, xsd) directly !!!
 
LordWabbitCommented:
I would guess that 'OrderMasterDataTable' is a class which inherits from a datatable with some methods thrown in for fun and profit.  You might need to add the records one at a time.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Bob LearnedCommented:
How is mDS declared and set?

Bob
tora111Author Commented:
OrderTrackingDataSet a xsd  is created by data sources

mDS is a dataset
Bob LearnedCommented:
mDs should be OrderTrackingDataSet, and not just plain ol' DataSet.

Bob
tora111Author Commented:
Bob, it doesn't a matter! My objective is to allow add/change/delete the "OrderTrackingDataSet.OrderMasterDataTable" by coding.

Is it possible to use coding method to manipulate a xsd datatable ?
Bob LearnedCommented:
It does matter if you are trying to cast a DataTable from the strong type DataSet into a strong type.

Bob
tora111Author Commented:
I see, in other words, is it possible to manipulate a datatable from a strong type dataset by coding method ? Because I don't know how to ...., could you give me some example for it !
Bob LearnedCommented:
I have an Access database, and here is an example form to highlight using a strong-typed DataSet:

Public Class Form1
  Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

  Public Sub New()
    MyBase.New()

    'This call is required by the Windows Form Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call

  End Sub

  'Form overrides dispose to clean up the component list.
  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    If disposing Then
      If Not (components Is Nothing) Then
        components.Dispose()
      End If
    End If
    MyBase.Dispose(disposing)
  End Sub

  'Required by the Windows Form Designer
  Private components As System.ComponentModel.IContainer

  'NOTE: The following procedure is required by the Windows Form Designer
  'It can be modified using the Windows Form Designer.  
  'Do not modify it using the code editor.
  Friend WithEvents OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter
  Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection
  Friend WithEvents DataSet21 As Test.DataSet2
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
    Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
    Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
    Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
    Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
    Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
    Me.DataSet21 = New Test.DataSet2
    CType(Me.DataSet21, System.ComponentModel.ISupportInitialize).BeginInit()
    '
    'OleDbDataAdapter1
    '
    Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
    Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
    Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
    Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Questions", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Asker", "Asker"), New System.Data.Common.DataColumnMapping("Date", "Date"), New System.Data.Common.DataColumnMapping("Grade", "Grade"), New System.Data.Common.DataColumnMapping("Points", "Points"), New System.Data.Common.DataColumnMapping("QID", "QID"), New System.Data.Common.DataColumnMapping("Text", "Text"), New System.Data.Common.DataColumnMapping("Title", "Title"), New System.Data.Common.DataColumnMapping("TopicArea", "TopicArea"), New System.Data.Common.DataColumnMapping("TopicAreaPath", "TopicAreaPath")})})
    Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
    '
    'OleDbDeleteCommand1
    '
    Me.OleDbDeleteCommand1.CommandText = "DELETE FROM Questions WHERE (QID = ?) AND (Asker = ? OR ? IS NULL AND Asker IS NU" & _
    "LL) AND ([Date] = ? OR ? IS NULL AND [Date] IS NULL) AND (Grade = ? OR ? IS NULL" & _
    " AND Grade IS NULL) AND (Points = ? OR ? IS NULL AND Points IS NULL) AND (Title " & _
    "= ? OR ? IS NULL AND Title IS NULL) AND (TopicArea = ? OR ? IS NULL AND TopicAre" & _
    "a IS NULL)"
    Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_QID", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "QID", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Asker", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Asker", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Asker1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Asker", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Date", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Date1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Grade", System.Data.OleDb.OleDbType.VarWChar, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Grade", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Grade1", System.Data.OleDb.OleDbType.VarWChar, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Grade", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Points", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Points", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Points1", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Points", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Title", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Title", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Title1", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Title", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TopicArea", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TopicArea", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TopicArea1", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TopicArea", System.Data.DataRowVersion.Original, Nothing))
    '
    'OleDbConnection1
    '
    Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
    "ocking Mode=1;Data Source=""C:\My Stuff\My Visual Studio Solutions\EEAnswerReader" & _
    "\Answers.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLE" & _
    "DB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Pro" & _
    "perties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create " & _
    "System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Com" & _
    "pact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactio" & _
    "ns=1"
    '
    'OleDbInsertCommand1
    '
    Me.OleDbInsertCommand1.CommandText = "INSERT INTO Questions(Asker, [Date], Grade, Points, QID, [Text], Title, TopicArea" & _
    ", TopicAreaPath) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
    Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Asker", System.Data.OleDb.OleDbType.VarWChar, 50, "Asker"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Date", System.Data.OleDb.OleDbType.DBDate, 0, "Date"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Grade", System.Data.OleDb.OleDbType.VarWChar, 1, "Grade"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Points", System.Data.OleDb.OleDbType.SmallInt, 0, "Points"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("QID", System.Data.OleDb.OleDbType.VarWChar, 10, "QID"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Text", System.Data.OleDb.OleDbType.VarWChar, 0, "Text"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Title", System.Data.OleDb.OleDbType.VarWChar, 255, "Title"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("TopicArea", System.Data.OleDb.OleDbType.VarWChar, 255, "TopicArea"))
    Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("TopicAreaPath", System.Data.OleDb.OleDbType.VarWChar, 0, "TopicAreaPath"))
    '
    'OleDbSelectCommand1
    '
    Me.OleDbSelectCommand1.CommandText = "SELECT Asker, [Date], Grade, Points, QID, [Text], Title, TopicArea, TopicAreaPath" & _
    " FROM Questions"
    Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
    '
    'OleDbUpdateCommand1
    '
    Me.OleDbUpdateCommand1.CommandText = "UPDATE Questions SET Asker = ?, [Date] = ?, Grade = ?, Points = ?, QID = ?, [Text" & _
    "] = ?, Title = ?, TopicArea = ?, TopicAreaPath = ? WHERE (QID = ?) AND (Asker = " & _
    "? OR ? IS NULL AND Asker IS NULL) AND ([Date] = ? OR ? IS NULL AND [Date] IS NUL" & _
    "L) AND (Grade = ? OR ? IS NULL AND Grade IS NULL) AND (Points = ? OR ? IS NULL A" & _
    "ND Points IS NULL) AND (Title = ? OR ? IS NULL AND Title IS NULL) AND (TopicArea" & _
    " = ? OR ? IS NULL AND TopicArea IS NULL)"
    Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Asker", System.Data.OleDb.OleDbType.VarWChar, 50, "Asker"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Date", System.Data.OleDb.OleDbType.DBDate, 0, "Date"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Grade", System.Data.OleDb.OleDbType.VarWChar, 1, "Grade"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Points", System.Data.OleDb.OleDbType.SmallInt, 0, "Points"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("QID", System.Data.OleDb.OleDbType.VarWChar, 10, "QID"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Text", System.Data.OleDb.OleDbType.VarWChar, 0, "Text"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Title", System.Data.OleDb.OleDbType.VarWChar, 255, "Title"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("TopicArea", System.Data.OleDb.OleDbType.VarWChar, 255, "TopicArea"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("TopicAreaPath", System.Data.OleDb.OleDbType.VarWChar, 0, "TopicAreaPath"))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_QID", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "QID", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Asker", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Asker", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Asker1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Asker", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Date", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Date1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Grade", System.Data.OleDb.OleDbType.VarWChar, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Grade", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Grade1", System.Data.OleDb.OleDbType.VarWChar, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Grade", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Points", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Points", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Points1", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Points", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Title", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Title", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Title1", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Title", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TopicArea", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TopicArea", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TopicArea1", System.Data.OleDb.OleDbType.VarWChar, 255, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TopicArea", System.Data.DataRowVersion.Original, Nothing))
    '
    'DataSet21
    '
    Me.DataSet21.DataSetName = "DataSet2"
    Me.DataSet21.Locale = New System.Globalization.CultureInfo("en-US")
    '
    'Form1
    '
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.BackColor = System.Drawing.SystemColors.Control
    Me.ClientSize = New System.Drawing.Size(516, 306)
    Me.KeyPreview = True
    Me.Name = "Form1"
    Me.Text = "Form1"
    CType(Me.DataSet21, System.ComponentModel.ISupportInitialize).EndInit()

  End Sub

#End Region


  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Me.OleDbDataAdapter1.Fill(Me.DataSet21)

    Dim table As DataSet2.QuestionsDataTable = Me.DataSet21.Tables("Questions")
    Dim row As DataSet2.QuestionsRow = table.Rows(0)
    MessageBox.Show(row.Asker)
  End Sub

End Class

Bob

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tora111Author Commented:
Thanks Bob, I'm wonder if we can use tableadapter and bindingsource to manipulate the strong type dataset table then it'll be more simple.

But I don't how to change the database source (for example a mdb file), I don't want to hard code the database because sometimes we need to deploy to different location and mdb file!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.