Here's the scenerio: I am importing records into my database through the
program, the first time through the database is empty and so is the dataset.
Everything works fine until the second time through and I'm bring in a new
set of records, that may or may not already exist. I populate the dataset
and if the record doesn't already exist in the database/dataset, I add a new
row to the dataset. However, the problem comes in when the record does
already exist in the database/dataset. I don't want to delete the record, I
just want to modify some of the fields. How can I update a datarow in a
dataset with modified fields that I've collected in a datarow? Once I have
the dataset updated, I then pass it to my DataAdapter to write back to the
database. This is done after all the rows have been added or modified. Is
this possible or do I need to do something different with modified rows?
Code sample:
DataRow1 = FindExisting(DataSetMain, comparisonfield1, comparisonfield2)
if DataRow1 isnot nothing then
DataRowNew(0) = DataRow1(0)
DataRowNew("ID") = DataRow1("ID")
DataRowNew("fieldsupdated") = updatedvalues
DataRow1(0) = DataRowNew(0)
???? How to add/update the DataRow1 to DataSetMain ????
else
DataRowNew = newvalues
DataSetMain.Rows.Add(DataRowNew)
end if
DataAdapter.UpdateTable(DataSetMain) 'Updates the database
***********
Public Function UpdateTable(ByRef Target As DataTable, _
Optional ByVal Source As String = Nothing, _
Optional ByVal Trans As SqlTransaction =
Nothing) As Boolean
Try
If Source = Nothing Then
Source = "SELECT * FROM " & Target.TableName
End If
Using lcn As New SqlClient.SqlConnection(mstrConnString)
Using lda As New SqlClient.SqlDataAdapter(Source, lcn)
Using lbd As New SqlClient.SqlCommandBuilder(lda)
With lda
.InsertCommand = lbd.GetInsertCommand
.UpdateCommand = lbd.GetUpdateCommand
.DeleteCommand = lbd.GetDeleteCommand
.Update(Target)
Return True
End With
End Using
End Using
End Using
Catch ex As System.Exception
Debug.WriteLine("UpdateTable: " & ex.Message)
Return False
End Try
End Function
--
Cindy