VBBRett
asked on
Can an insert or update be done without a merge with ADO.NET?
I just wanted to know can an Update or Insert can be done with two different tables without merging them together? It seems everywhere I look, they want you to merge the tables first, then you are able to update or insert the information from one table to another. Please let me know. Thanks!
Use two SqlCommands objects to update each table
Yes, you may be able to... provided that you perform a bit of magic
If you are certain that there won't be any primary key collisions, then the task is kinda easy...
DataAdapterOne.Fill(DataTa bleOne)
DataAdapterTwo.Fill(DataTa bleTwo)
' change the row state to "Added"
for each dr as DataRow in DataTableOne.Rows
dr.SetAdded ' new to v2.0
next
' assuming you've got a commandbuilder or other way to create the insert commands
DataAdatperTwo.Update(Data TableOne)
When colisions are anticipated, then the middle part gets a bit complicated
for each dr as DataRow in DataTableOne.Rows
if DataTableTwo.Contains(dr.c olums(0)) then ' assumes primary key is in col 0
dr.SetModified ' new to v2.0
else
dr.SetAdded ' new to v2.0
end if
next
PS: Example was typed "by hand"
If you are certain that there won't be any primary key collisions, then the task is kinda easy...
DataAdapterOne.Fill(DataTa
DataAdapterTwo.Fill(DataTa
' change the row state to "Added"
for each dr as DataRow in DataTableOne.Rows
dr.SetAdded ' new to v2.0
next
' assuming you've got a commandbuilder or other way to create the insert commands
DataAdatperTwo.Update(Data
When colisions are anticipated, then the middle part gets a bit complicated
for each dr as DataRow in DataTableOne.Rows
if DataTableTwo.Contains(dr.c
dr.SetModified ' new to v2.0
else
dr.SetAdded ' new to v2.0
end if
next
PS: Example was typed "by hand"
ASKER
Would you be able to show me how this is done if I give you my code? here is my code in case you want to modify it. I'd really apprecaite it. I didn't show the whole function, just the most important parts to the code. Thank you once again!
If conn_string.State = ConnectionState.Open Then
MsgBox("The connection string has connected to the source.")
Else
MsgBox("Unable to connect")
End If
'The data adapter fills the dataset with the table information from the database
da.Fill(ds)
' da.Fill(ds, "Test_NIP_Valuation_nonpro duction")
da.Fill(dt)
Dim tester As String
Dim field1, field2, field3, field4, field5, field6, field7, field8, field9, field10 As String
Dim field11, field12, field13, field14, field15, field16, field17, field18, field19, field20 As String
Dim row As DataRow
Dim retInt As Integer
Dim count As Integer = 0
For Each row In ds.Tables(0).Rows
tester = "insert into jhirniak.NIP_Valuation(LOB , PolicyNO, OccuranceNO, PolOccNo" & _
"PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE" & _
"OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription" & _
"Select '" & row.Item("LOB").ToString & ", '" & row.Item("Pol #").ToString & "', '" & _
row.Item("Occ #").ToString & "', '" & row.Item("Pol Occ #").ToString & "', '" & _
row.Item("Pol Comm").ToString & "', '" & row.Item("Loss Day").ToString & "', " & _
row.Item("RiskState").ToSt ring & ", '" & row.Item("First Notice").ToString & "', '" & _
row.Item("Valn Mo").ToString & "', '" & row.Item("Acc Yr").ToString & "', " & _
row.Item("PdIndem").ToStri ng & ", '" & row.Item("PdALAE").ToStrin g & ", " & _
row.Item("OS Indem").ToString & ", " & row.Item("OS ALAE").ToString & ", " & _
row.Item("IncdIndem").ToSt ring & ", " & row.Item("IncdALAE").ToStr ing & ", " & _
row.Item("Incd L&ALAE").ToString & ", " & row.Item("Insured").ToStri ng & ", " & _
row.Item("Loss Description") & "FROM " & Sheetvar & _
"WHERE NOT EXISTS "
'row.Item("LOB").ToString & _
' row.Item("Pol #").ToString & _
' row.Item("Occ #").ToString & _
' row.Item("Pol Occ #").ToString & _
' row.Item("Pol Comm").ToString & _
' row.Item("Loss Day").ToString & _
' row.Item("RiskState").ToSt ring & _
' row.Item("First Notice").ToString & _
' row.Item("Valn Mo").ToString & _
' row.Item("Acc Yr").ToString()
count = count + 1
If retInt = 1 Then Continue For
'build update
MsgBox(tester)
row.SetAdded()
Next
End If
If conn_string.State = ConnectionState.Open Then
MsgBox("The connection string has connected to the source.")
Else
MsgBox("Unable to connect")
End If
'The data adapter fills the dataset with the table information from the database
da.Fill(ds)
' da.Fill(ds, "Test_NIP_Valuation_nonpro
da.Fill(dt)
Dim tester As String
Dim field1, field2, field3, field4, field5, field6, field7, field8, field9, field10 As String
Dim field11, field12, field13, field14, field15, field16, field17, field18, field19, field20 As String
Dim row As DataRow
Dim retInt As Integer
Dim count As Integer = 0
For Each row In ds.Tables(0).Rows
tester = "insert into jhirniak.NIP_Valuation(LOB
"PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE" & _
"OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription" & _
"Select '" & row.Item("LOB").ToString & ", '" & row.Item("Pol #").ToString & "', '" & _
row.Item("Occ #").ToString & "', '" & row.Item("Pol Occ #").ToString & "', '" & _
row.Item("Pol Comm").ToString & "', '" & row.Item("Loss Day").ToString & "', " & _
row.Item("RiskState").ToSt
row.Item("Valn Mo").ToString & "', '" & row.Item("Acc Yr").ToString & "', " & _
row.Item("PdIndem").ToStri
row.Item("OS Indem").ToString & ", " & row.Item("OS ALAE").ToString & ", " & _
row.Item("IncdIndem").ToSt
row.Item("Incd L&ALAE").ToString & ", " & row.Item("Insured").ToStri
row.Item("Loss Description") & "FROM " & Sheetvar & _
"WHERE NOT EXISTS "
'row.Item("LOB").ToString & _
' row.Item("Pol #").ToString & _
' row.Item("Occ #").ToString & _
' row.Item("Pol Occ #").ToString & _
' row.Item("Pol Comm").ToString & _
' row.Item("Loss Day").ToString & _
' row.Item("RiskState").ToSt
' row.Item("First Notice").ToString & _
' row.Item("Valn Mo").ToString & _
' row.Item("Acc Yr").ToString()
count = count + 1
If retInt = 1 Then Continue For
'build update
MsgBox(tester)
row.SetAdded()
Next
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I had a thought.... this is probably about the same as doing a Merge operation. In fact, a merge will probably be a lot faster (particularly if you have to leave in the code that checks for duplicates).
So, what was wrong with doing a Merge to begin with?
So, what was wrong with doing a Merge to begin with?
ASKER
I would get an error with doing a merge. It did not work. All I need is an insert.
just curious... what kind of error?
The only failure that I can imagine is a schema-related error or lack of a primary key, etc. If so, then I'd bet the error will return using my example above
The only failure that I can imagine is a schema-related error or lack of a primary key, etc. If so, then I'd bet the error will return using my example above
ASKER
Well, I remember looking up the error, and I was told when I did some searching that the table name, the field name, and the datatypes have to be exactly the same...otherwise the command would not execute.
----------
what do you mean with merging them?
do you mean 2 tables in different databases? then you can open 2 connections and use the datareader and commandobject to update or insert as needed
for example i have a process reading in a textfile that returns a datatable
then i use that datatable to update or insert records in a SQL db
this is done with no 'merging' as far as i know
----------
bruintje
share what you know, learn what you don't