Help with Merge and INSERT/UPDATE commands

  • Thread starter Thread starter John Cosmas
  • Start date Start date
J

John Cosmas

I have a DATATABLE which I have populated in my application, and I need it
written out to a particular table I specify in my ACCESS database. My code
works to the point of the MERGE and UPDATE, but it creates exactly the
number of BLANK records per the populated DATATABLE. Here is my code...

pstrDestinationTable = "tws_tbl_Case_Scanner_" &
GetDateTimeStamp()
pstrSQL = "SELECT * INTO " & pstrDestinationTable & " FROM
tws_tbl_Case_Scanner"

pobjCmdEventLog = New
System.Data.OleDb.OleDbCommand(pstrSQL, pobjConnection)
pobjCmdEventLog.ExecuteNonQuery()

pstrSQL = "SELECT * FROM " & pstrDestinationTable
pobjCmdEventLog = New
System.Data.OleDb.OleDbCommand(pstrSQL, pobjConnection)
pobjAdpEventLog = New
System.Data.OleDb.OleDbDataAdapter(pobjCmdEventLog)
'Dim pobjDataTable As System.Data.DataTable = New
System.Data.DataTable
pobjAdpEventLog.Fill(gdsTWWData, pstrDestinationTable)

gdsTWWData.Tables(pstrDestinationTable).Merge(gdsTWWData.vdtCaseScanner,
True)

Dim pobjCmdBuilder As System.Data.OleDb.OleDbCommandBuilder
pobjCmdBuilder = New
System.Data.OleDb.OleDbCommandBuilder(pobjAdpEventLog)
pobjAdpEventLog.InsertCommand =
pobjCmdBuilder.GetInsertCommand()
'pobjAdpEventLog.UpdateCommand =
pobjCmdBuilder.GetUpdateCommand()

pobjAdpEventLog.Update(gdsTWWData.Tables(pstrDestinationTable))
gdsTWWData.Tables(pstrDestinationTable).AcceptChanges()

What my code is intended to do is to create a DUMP of my populated DATATABLE
an put it into a named table in my ACCESS database. That is why the use of
the PRSTDESTINATIONTABLE is specific. At the completion of the MERGE
command, I did notice that the other COLUMNS in the PRSTDESTINATIONTABLE is
BLANK/NULL. The schemas of both DATATABLE (SOURCE and DESTINATION) are
identical. Please help.

TIA
John
 
John,

We see a bunch of code, but not in which way it is related. Here a bunch of
advices.

If you use the commandbuilder than let it do its job alone it is dynamicly
building what it needs at the latest moment.

More a question, but the use of acceptchanges triggers us forever were do
you use that (in other words are you sure for what is that method, because
mostly people have a wrong idea about it)

Use parameters and don't include words in your strings, you will see that it
makes at least your code much cleaner.


http://lab.msdn.microsoft.com/search/refinement.aspx?__VIEWSTATE=&query=oledb+parameters+

A pity is that MSDN shows forever the wrong (very old not deleted) page
about OleDB parameters, know that you have to support them to the collection
in the excact sequense as they are expected and tht the @named parameter
makes no sensec. Just a questionmark or nothing does the same.

I hope this helps,

Cor
 
Cor;

I really appreciate your response. I am still experiencing problems. I
removed the MERGE statement and used an IMPORTROW method just to be on the
safe side. Yes, I also used the COMMANDBUILDER, as you may have noticed in
my code. The COMMANDBUILDER however, does not like to generate the UPDATE
command because a KEY was not returned even though it does exist in the
SOURCE table. Unfortunately, I'm still getting the same response where it
creates the NULL records in the database but does not fill the COLUMNs. Let
me know if you have any other advise. I do appreciate your help.

John
 
Back
Top