Btukfyl

Currently trying to update an SQL Server database from an Access db, it works importing new records but fails on when I reimport the file and there is a duplicate - in looking for it to insert the row if it's not there (working), but skip if it already exists. The first column has the primary key set.

Dim table As DataTable = New DataTable

Dim accConnection As New OleDb.OleDbConnection(

   "Provider=Microsoft.JET.OLEDB.4.0; Data Source='C:Tester.mdb';User Id=admin; Password=;")

Dim sqlConnection As New SqlClient.SqlConnection(

   "Data Source=10.75.24.94;Initial Catalog=CTData;User ID=sql;Password=")

Try

    'Import the Access data

    accConnection.Open()

    Dim accDataAdapter = New OleDb.OleDbDataAdapter(

        "SELECT * FROM Import_test", accConnection)

    accDataAdapter.Fill(table)

    accConnection.Close()

    'Export to MS SQL

    For Each row As DataRow In table.Rows

        row.SetAdded()

    Next

    sqlConnection.Open()

    Dim sqlDataAdapter As New SqlClient.SqlDataAdapter(

        "SELECT * FROM Import_test", sqlConnection)

    Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)

    sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()

    sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()

    sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()

    sqlDataAdapter.Update(table)

    sqlConnection.Close()

Catch ex As Exception

    If accConnection.State = ConnectionState.Open Then

        accConnection.Close()

    End If

    If sqlConnection.State = ConnectionState.Open Then

        sqlConnection.Close()

    End If

    MessageBox.Show("Import failed with error: " &

                    Environment.NewLine & Environment.NewLine &

                    ex.ToString)

End Try

The error I'm presented with is:

Violation of Primary key. Cannot insert duplicate key in object.

I think you can remove VB from the equation (I like to make things as simple as possible, without over-simplifying it). You have several options available.

INSERT INTO Table

SELECT * FROM #Table xx

WHERE NOT EXISTS (SELECT 1 FROM Table rs WHERE rs.id = xx.id)

Or . . .

DELETE FROM Table

WHERE (ID NOT IN (SELECT MAX(ID) AS Expr1

FROM Table

AS Table_1 GROUP BY NAME,ADDRESS,CITY,STATE,ZIP,PHONE,ETC.))

There are a lot of other ways to handle this as well. Is you need to incorporate VB, you can do something like this.

Dim cmd As New OleDbCommand

       Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:your_pathDB.accdb")



       Dim queryResult As Integer

       Dim sqlQRY As String = "SELECT COUNT(*) AS FROM ESRRegister WHERE ID = '" & IDtxt.Text & "'"

       con.Open()

       cmd.Connection = con

       cmd.CommandType = CommandType.Text

       If queryResult > 0 Then

           cmd.CommandText = "insert into ESRRegister (Dt,ID)VALUES ('" & Dttxt.Text & "' , '" & IDtxt.Text & "')"

           queryResult = cmd.ExecuteScalar()

           MsgBox("Added Successfuly")

       Else



           MessageBox.Show("Already Exists!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)



       End If