[RESOLVED] [2005] readXML populate different Tables

[maps]

Ok. I Export two tables in XML using writeXML and i want to update another database with the same table schema with these XML tables.
Below is the Update code and Exported XML i am trying to use.
So far No Luck. where am i going wrong?
Code:

Private Sub updateFromXML()
        Dim dsXMLImport As New DataSet
        Dim cmdAdapter As New OleDbCommand
        myConn = New OleDbConnection(strAccessConn)
 
        Try
            myConn.Open()
            cmdAdapter.Connection = myConn
            Dim da1 As New OleDbDataAdapter()
            Dim da2 As New OleDbDataAdapter()
 
            dsXMLImport.ReadXml(Me.tbFilePath.Text)
            'For Table 1
            da1.SelectCommand = cmdAdapter
            da1.InsertCommand.Parameters.Add(dsXMLImport.Tables(0))
            da1.Fill(dsXMLImport)
            da1.Update(dsXMLImport)
            'For Table 2
            da2.SelectCommand = cmdAdapter
            da2.InsertCommand.Parameters.Add(dsXMLImport.Tables(1))
            da2.Fill(dsXMLImport)
            da2.Update(dsXMLImport)
 
        Catch ex As Exception
 
        Finally
            myConn.Close()
            cmdAdapter.Dispose()
        End Try
    End Sub

[maps]

**bump**

[maps]

bump....bum......bu...b.
Any way i am seeking to resolve this thread. Any helper?

[maps]

I get no error...but i get no results too!
If you dont like my Bump then Quit the Club. The majority say it usually serves its purpose in here.

[maps]

I am reading from an XML file and not writting to it.

[maps]

thanks shakti5385, i would rather you read my first post before posting any further. It describes what i am doing. As indicated,i am using 2005, and as indicated again, i am using readXML.

[maps]

But that does not mean that you Hijack threads just because you have the same problem. Besides your problem is almost the same but a little different.

[maps]

I have scratched my head and failed to get it,searched through the cyber but failed to get an answer. I thought it was the table name but after specifying the table name, i cant still have it. Some one help my head!

Private Sub updateFromXML()
        Dim dsXMLImport As New DataSet
        Dim cmdAdapter As New OleDbCommand
        myConn = New OleDbConnection(strAccessConn)
 
        Try
            myConn.Open()
            cmdAdapter.Connection = myConn
            Dim da1 As New OleDbDataAdapter()
            Dim da2 As New OleDbDataAdapter()
 
            dsXMLImport.ReadXml(Me.tbFilePath.Text)
            'For Table 1
            da1.SelectCommand = cmdAdapter
            da1.InsertCommand.Parameters.Add(dsXMLImport.Tables(0).TableName)
            da1.Fill(dsXMLImport, dsXMLImport.Tables(0).TableName)
            da1.Update(dsXMLImport, dsXMLImport.Tables(0).TableName)
            'For Table 2
            da2.SelectCommand = cmdAdapter
            da2.InsertCommand.Parameters.Add(dsXMLImport.Tables(1).TableName)
            da2.Fill(dsXMLImport, dsXMLImport.Tables(1).TableName)
            da2.Update(dsXMLImport, dsXMLImport.Tables(1).TableName)
 
 
        Catch ex As Exception
 
        Finally
            myConn.Close()
            cmdAdapter.Dispose()
        End Try
    End Sub

Ok...when i catch the exception, it tells me that object reference not set o an instance of object.
And when i remove the try catch block..i see the exception is here.
da1.InsertCommand.Parameters.Add(dsXMLImport.Tables(0).TableName).

[stanav]

What does the insertcommand text look like?

[maps]

Thanks stanav. there is no command text, i thought that the insertCommand given the tablename as the parameter can replace the insertcommand text.
I have tried to instantiate the insert command but still i get an exception on the same line saying...
"The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects."
I dont understand this now!...
i thought the parameter i am mean't to pass here should be the name of the first table in the XML! so if it does not allow this, then what can i pass?!
below is my code.

i would use insert statements and add fields as parameters to the insertcommand, but i want to import more than 10 tables with each having a minimum of 10 fields! So hardcording more than 10 insert command texts with each having 10 fields passed as parameters to the insert command would be alot of code.

Private Sub updateTableFromXML()
        Dim dsXMLImport As New DataSet
        Dim cmdAdapter As New OleDbCommand
        myConn = New OleDbConnection(strAccessConn)
 
        Try
            myConn.Open()
            cmdAdapter.Connection = myConn
            Dim da1 As New OleDbDataAdapter()
            Dim da2 As New OleDbDataAdapter()
 
            dsXMLImport.ReadXml(Me.tbFilePath.Text)
            'For Table 1
            da1.SelectCommand = cmdAdapter
            da1.InsertCommand = New OleDbCommand()
            da1.InsertCommand.Parameters.Add(dsXMLImport.Tables(0).TableName)
            da1.Fill(dsXMLImport, dsXMLImport.Tables(0).TableName)
            da1.Update(dsXMLImport, dsXMLImport.Tables(0).TableName)
            'For Table 2
            da2.SelectCommand = cmdAdapter
            da2.InsertCommand = New OleDbCommand()
            da2.InsertCommand.Parameters.Add(dsXMLImport.Tables(1).TableName)
            da2.Fill(dsXMLImport, dsXMLImport.Tables(1).TableName)
            da2.Update(dsXMLImport, dsXMLImport.Tables(1).TableName)
 
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            myConn.Close()
            cmdAdapter.Dispose()
        End Try
    End Sub

[maps]

Ok. i got it work.
Here is what i did.
Created an SQL statement, a new adapter and a new insertCommand
read my XML, inserted parameters for the insertCommand with items from the dataset.
Updated the dataAdapter...and Bingo i imported one table.

I think i will stay with this,which implies if i have 10 tables to update on the Fly, i will repeat the same routine for all the 10 tables.

[FastEddie]

maps, if you don't mind could you please post your code. I have been trying to accomplish the same thing now for a couple of weeks and have not been successful.

[maps]

Here you are.
You will need to modify it a little to suit your needs.

Private Sub update_tblPopulatedPlaces_fromXML()
        Dim dsXMLImport As New DataSet
        myConn = New OleDbConnection(strAccessConn)
        Dim strSQL As String = "INSERT INTO tblPopulatedPlaces(AdminBdry1_ID,AdminBdry2_ID,AdminBdry3_ID,AdminBdry4_ID,PPname,uid_Created,date_Created,uid_Modified,date_Modified)" & _
        " VALUES(@AdminBdry1_ID,@AdminBdry2_ID,@AdminBdry3_ID,@AdminBdry4_ID,@PPname,@uid_Created,@date_Create  d,@uid_Modified,@date_Modified)" 
        Dim da As New OleDbDataAdapter()
        da.AcceptChangesDuringFill = False
        da.InsertCommand = New OleDbCommand(strSQL, myConn)
        Try
            myConn.Open()
            dsXMLImport.ReadXml(Me.tbFilePath.Text)
            With da.InsertCommand.Parameters
                For i As Integer = 0 To dsXMLImport.Tables(1).Rows.Count - 1
                    drPublic = dsXMLImport.Tables(1).Rows(i)
                    .AddWithValue("@AdminBdry1_ID", drPublic(1))
                    .AddWithValue("@AdminBdry2_ID", drPublic(2))
                    .AddWithValue("@AdminBdry3_ID", drPublic(3))
                    .AddWithValue("@AdminBdry4_ID", drPublic(4))
                    .AddWithValue("@PPname", drPublic(5))
                    .AddWithValue("@uid_Created", drPublic(6))
                    .AddWithValue("@date_Created", drPublic(7))
                    .AddWithValue("@uid_Modified", drPublic(8))
                    .AddWithValue("@date_Modified", drPublic(9))
                Next i
            End With
            da.Update(dsXMLImport, dsXMLImport.Tables(1).TableName)
        Catch exSQL As OleDbException
            ShowErrorBoxOK("update_tblPopulatedPlaces_fromXML():", exSQL.Message, My.Resources.myStrings.capSQLError)
        Catch ex As Exception
            ShowErrorBoxOK("update_tblPopulatedPlaces_fromXML():", ex.Message, My.Resources.myStrings.capGeneralError)
        Finally
            myConn.Close()
            myConn.Dispose()
            da.Dispose()
        End Try
    End Sub