asked on

UPDATING ACCESS DATATABLE FROM VB .NET

I HAVE 2 DATABASES

C:\DB.MDB AND D:\DB.MDB

THIS IS EXACTLY THE SAME DATABSE THE ONLY DIFFERNCE IS THAT THE FIRST DB IS FROM SHOP1 AND THE SECOND DATABASE HAS THE DATA FROM ALL THE SHOPS
SO I WANT TO UPDATE NOT ONLY THE "NEW" DATA FROM SHOP1 I WANT TO DO SOMETHING MORE SIMPLE
DELETE FROM THE SECOND DATABASE FROM THE DATATABLE THAT NEED TO BE UPDATED EVERYTHING HAS FROM SHOP1 THEN LOAD EVERYTHING FROM FIRST DATABASE AND "ADD" THEM TO SECOND DATABASE

EXAMPLE

TARGET DB
D:\DB.MDB TABLE MOVEMENTS

1 SHOP1 CLIENT1 CAR1 20.000
2 SHOP1 CLIENT2 CAR1 20.000
3 SHOP3 CLIENT3 CAR2 20.000
4 SHOP4 CLIENT4 CAR2 20.000

SOURCE DB
C:\DB.MDB TABLE MOVEMENTS

1 SHOP1 CLIENT1 CAR1 20.000
2 SHOP1 CLIENT2 CAR1 20.000
3 SHOP1 CLIENT6 CAR2 20.000
4 SHOP1 CLIENT9 CAR2 20.000

SO I WANT TO DELETE FROM TARGET DB THE ROWS 1 & 2 (WHERE SHOP = SHOP1)
THEN ADD FROM THE SOURCE DB ALL THE ROWS (WHERE SHOP = SHOP1)

THIS IS NOT FOR ONE TIME JOB THIS HAVE TO BE DONE EVERY MONTH SO I HAVE TO DO THIS IN VISUAL BASIC

I HAVE TRY THIS FOR ONE TABLE ONLY
CON1 = TARGET CONNECTION
CON2 = SOURCE CONNECTION

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

Dim targetSQL As String = "DELETE * FROM MOVEMENTS WHERE SHOP;='" & SHOP.Text & "'"
Dim targetDA As New OleDbDataAdapter(targetSQL, CON1)
Dim cb As New OleDbCommandBuilder(targetDA)
Dim dt As New Data.DataTable
Dim dr As Data.DataRow
targetDA.Fill(dt)
For Each dr In dt.Rows
dr.Delete()
Next
targetDA.Update(dt)

Dim sourceSQL2 As String = "SELECT * FROM MOVEMENTS WHERE SHOP;='" & SHOP.Text & "'"
Dim targetSQL2 As String = "SELECT * FROM MOVEMENTS WHERE SHOP;='" & SHOP.Text & "'"

Dim targetDA2 As New OleDbDataAdapter(targetSQL2, CON1)
Dim cb2 As New OleDbCommandBuilder(targetDA2)
Dim targetCommand As OleDbCommand = cb2.GetInsertCommand

Dim sourceCommand As New OleDbCommand(sourceSQL2, CON2)
'targetCommand.Connection.Open()
'sourceCon.Open() I ALLREADY OPEN THIS CONNECTION
Dim sourceReader As OleDbDataReader
sourceReader = sourceCommand.ExecuteReader()
While sourceReader.Read()
'for each row from source
For i As Integer = 1 To sourceReader.FieldCount - 1
'load values into parameters ...
'... EXCEPT field 0, which is AutoGenerated Primary Key
targetCommand.Parameters(i - 1).Value = sourceReader(i) '<<< ERROR LINE >>> Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.
Next
targetCommand.ExecuteNonQuery()
End While
sourceReader.Close()
CON2.Close()
targetCommand.Connection.Close()

ALLREADY POST THIS
https://www.experts-exchange.com/questions/21975556/copy-database-data-from-one-database-to-onother-same-structure.html

ANY IDEAS

DIMITRIS

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockas1982

ASKER

thnx 4 your time Yzlat

error

Sub GetConnection(ByVal strConn As String) As OleDbConnection <-- last word expression does not produce a value

YZlat

did you import System.Data.OleDb?

rockas1982

ASKER

yes i did

YZlat

what error are you getting in that line?

YZlat

do you pass connection object or a connection string to GetDataSet??

GetDataSet accepts two arguments: database connection STRING and SQL string

YZlat

let me see your code

rockas1982

ASKER

is there any easier way to do this
why so many "TRY"
so many subs

iwould be nice to do this in one sub

look here http://12345.gr/arxeia/1.jpg

YZlat

you can do this in one sub

YZlat

but this way your code is cleaner and more readable

StateGuy

I tried to plagerize this solution, but got stuck. The snippet is what I created based on the solution given above. It blows on the line "cmd.ExecuteNonQuery()" with the error "No value given for one or more required parameters."

Specifics (using the Northwind Access database):
* The value of the connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb"
* The value of the SQL string is "Update Customers Set CompanyName = 'Around the Horn' where CustomerID = 'AROUT'"
* "strConnectionString" and "strCommandString" are being passed in successfully.
* The connection (conn) is opening successfully (value of "1").
* The Immediate Window is giving me the message: "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll."

What am I missing?

StateGuy

    Private Sub ConnectAndUpdate()
        Dim conn As OleDbConnection = New OleDbConnection(strConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(strCommandString, conn)
        Try
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Private Sub ConnectAndUpdate: " & ex.Message.ToString)
        Finally
            conn.Close()
        End Try
    End Sub

Open in new window