Merging two datatables - Values are appending, not updating!?

See more:

VB

ASP.NET

Hi all,

I have a little background in .NET, mostly self taught and I rarely have to ask for help, however here I am.

I have a script that analyses CSV files (KBCSV Lib) and updates a database with the values from these CSV files. I can get that to work fine using SQLBulkCopy. The problem I am having is when the values already exist in the database, SQLBulkCopy will just append. After a few google searches, the solution seems to reside in populating datatables; One with values from the CSV, One with values from the Database - And then merging the two together.

I have done this, and the datatables populate and merge. The issue I am having is the data from the CSV gets appended, it does not update the original values in the datatable storing the database values.

Still following?

I have tried changing preserveChanges to both true and false. No avail.

Please help!?

Code below:

Dim firstdatatable As DataTable = New DataTable
            Dim Seconddatatable As DataTable = New DataTable
            Dim finaldatatable As DataTable = New DataTable
            Dim DbProdList = EntityToDatatable(fillqry, econtext)
            Dim TempProdList = EntityToDatatable(fillfromtemp, econtext)
            Firstdatatable = DbProdList.Copy()
            Seconddatatable = TempProdList.Copy()
            firstdatatable.Merge(Seconddatatable, False, MissingSchemaAction.Add
            firstdatatable.AcceptChanges()

The input:

User Name, Password, Full Name, Restriction list name, Banned word checking, Allow attachments, Template, Group, Mailbox size, Calendar, SyncML
psmith01,CLASS3B,Pauly Smith 1,YEAR2,1,N,ADVANCED,STAFF,1,Y,Y
smehta,CLASS3G,Smith Mehta,LOCAL,1,Y,STANDARD,PUPIL,2.1,N,Y
mrsjohns,SNHOJ,Mrs R Johns,UNRESTRICTED,-1,Y,ADVANCED,STAFF,2,Y,N
psmith02,CLASS588,Peter Smith 2, UKSCHOOLS,0,N,ADVANCED,STAFF,10,Y,Y
scohen,CLASS3G,Saul Cohen,LOCAL,2,Y,STANDARD,PUPIL,1,N,N
swright,CLASS1J,Shaun Wright,YEAR1,1,N,STANDARD,PUPIL,1,N,Y
amarkov,CLASS4E,Anya Markov,UKSCHOOLS,3,Y,STANDARD,PUPIL,1,N,N

Just a quick note: When I am importing the CSV, I have templates specified (I have coded logic where the user selects what column in the CSV corresponds to what column in the database) and before the CSV is SQLBulkCopied, I map the columns.

EG:

Using bcp As SqlBulkCopy = New SqlBulkCopy(connStr)

            'Checking to see if column has been specified, and mapping the column to the appropriate column in database.
            bcp.DestinationTableName = "dbo.tempinventoryimport"
            If categorybool = True Then
                bcp.ColumnMappings.Add(category, 2)
            End If
            If subcategorybool = True Then
                bcp.ColumnMappings.Add(subcategory, 3)

No matter how I try and merge the two datatables, the information appends to the end (Add's new rows with the same information). Some help would be muchly appreciated :)