By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,919 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,919 IT Pros & Developers. It's quick & easy.

Help with Merge and INSERT/UPDATE commands

P: n/a
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(gdsT WWData.vdtCaseScanner,
True)

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

pobjAdpEventLog.Update(gdsTWWData.Tables(pstrDesti nationTable))
gdsTWWData.Tables(pstrDestinationTable).AcceptChan ges()

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
Oct 13 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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...db+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

"John Cosmas" <jo********@hotmail.comschreef in bericht
news:cH****************@newsfe05.lga...
>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(gdsT WWData.vdtCaseScanner,
True)

Dim pobjCmdBuilder As System.Data.OleDb.OleDbCommandBuilder
pobjCmdBuilder = New
System.Data.OleDb.OleDbCommandBuilder(pobjAdpEvent Log)
pobjAdpEventLog.InsertCommand =
pobjCmdBuilder.GetInsertCommand()
'pobjAdpEventLog.UpdateCommand =
pobjCmdBuilder.GetUpdateCommand()
pobjAdpEventLog.Update(gdsTWWData.Tables(pstrDesti nationTable))
gdsTWWData.Tables(pstrDestinationTable).AcceptChan ges()

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

Oct 13 '06 #2

P: n/a
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
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:OT**************@TK2MSFTNGP02.phx.gbl...
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...db+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

"John Cosmas" <jo********@hotmail.comschreef in bericht
news:cH****************@newsfe05.lga...
>>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(pobjCmdEventLo g)
'Dim pobjDataTable As System.Data.DataTable = New
System.Data.DataTable
pobjAdpEventLog.Fill(gdsTWWData, pstrDestinationTable)
gdsTWWData.Tables(pstrDestinationTable).Merge(gds TWWData.vdtCaseScanner,
True)

Dim pobjCmdBuilder As
System.Data.OleDb.OleDbCommandBuilder
pobjCmdBuilder = New
System.Data.OleDb.OleDbCommandBuilder(pobjAdpEven tLog)
pobjAdpEventLog.InsertCommand =
pobjCmdBuilder.GetInsertCommand()
'pobjAdpEventLog.UpdateCommand =
pobjCmdBuilder.GetUpdateCommand()
pobjAdpEventLog.Update(gdsTWWData.Tables(pstrDest inationTable))
gdsTWWData.Tables(pstrDestinationTable).AcceptChan ges()

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


Oct 13 '06 #3

P: n/a
John,

Can you show the code in your update completely?

Cor

"John Cosmas" <jo********@hotmail.comschreef in bericht
news:TQ***************@newsfe02.lga...
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
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:OT**************@TK2MSFTNGP02.phx.gbl...
>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...db+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

"John Cosmas" <jo********@hotmail.comschreef in bericht
news:cH****************@newsfe05.lga...
>>>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(pobjCmdEventL og)
'Dim pobjDataTable As System.Data.DataTable = New
System.Data.DataTable
pobjAdpEventLog.Fill(gdsTWWData, pstrDestinationTable)
gdsTWWData.Tables(pstrDestinationTable).Merge(gd sTWWData.vdtCaseScanner,
True)

Dim pobjCmdBuilder As
System.Data.OleDb.OleDbCommandBuilder
pobjCmdBuilder = New
System.Data.OleDb.OleDbCommandBuilder(pobjAdpEve ntLog)
pobjAdpEventLog.InsertCommand =
pobjCmdBuilder.GetInsertCommand()
'pobjAdpEventLog.UpdateCommand =
pobjCmdBuilder.GetUpdateCommand()
pobjAdpEventLog.Update(gdsTWWData.Tables(pstrDes tinationTable))
gdsTWWData.Tables(pstrDestinationTable).AcceptChan ges()

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



Oct 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.