ImportRow doesn't saves changes to database table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: ImportRow doesn't saves changes to database table

  1. #1
    Join Date
    Feb 2006
    Posts
    6

    Question ImportRow doesn't saves changes to database table

    Can anybody help me please.
    I'm new to VB.NET and I'm trying to import some data from SQL Server to MS
    Access but cannot make it to work (note: I don't want to use DTS)
    ....goes like this

    Dim daSql As New SqlDataAdapter("select * from table1", SqlConnection)
    Dim dsSql As New DataSet() ' to hold info from SQL Server
    daSql.Fill(dsSql, "table1")

    Dim daAcc As New OleDb.OleDbDataAdapter("select * from table2",
    AccessConnection)
    ' table2 is an empty table with the same schema as table1
    Dim dsAcc As New DataSet() 'to hold info for Access
    daAcc.Fill(dsAcc, "table2")

    For i as Integer = 0 To dsSql.Tables(0).Rows.Count - 1
    dsAcc.Tables(0).NewRow()
    dsAcc.Tables(0).ImportRow(dsSql.Tables(0).Rows(i))
    Next i

    dsAcc.Tables(0).AcceptChanges()
    daAcc.AcceptChangesDuringUpdate = True
    daAcc.Update(dsAcc, "table2")

    '...end

    what am I doing wrong?
    messagebox inserted anywhere after For..Next loop shows desired number of
    rows in Access dataSet but the are no changes made to the actual database.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Are the tables from each database identical in structure? I would think the import would be easier to accomplish (and much quicker) using a SQL statement.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Feb 2006
    Posts
    6
    Thank you for such a prompt response.
    Yes they are identical.
    How would you approach this through SQL statement if one table is in SQL Server and another in MS Access? can you do this way?

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    See if the following works for you:

    Code:
    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                    "Data Source=C:\Test Files\db1.mdb")
    AccessConn.Open()
    
    'New table
    'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)
    
    'Existing table
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)
    
    AccessCommand.ExecuteNonQuery()
    AccessConn.Close()
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Feb 2006
    Posts
    6
    Paul,
    Thank you!!!
    It worked just great.
    Is there any place where I can rate this reply to my question?

    Just for my curiosity would you happen to know why my ImportRow() wasn't saving data to the table?
    Thanks

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    I don't know why the ImportRow method didn't work. I'll see if I can take a closer look and get you an answer.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Replies: 0
    Last Post: 08-29-2005, 10:25 PM
  2. Replies: 0
    Last Post: 08-15-2002, 09:57 AM
  3. Replies: 1
    Last Post: 03-28-2002, 04:38 PM
  4. Retrieve database field name from table
    By Jeremiah in forum VB Classic
    Replies: 3
    Last Post: 03-05-2001, 06:54 PM
  5. Replies: 1
    Last Post: 01-24-2001, 07:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center