How do I make lots of changes to a database table using a dataset

  • Thread starter Thread starter HONOREDANCESTOR
  • Start date Start date
H

HONOREDANCESTOR

Hi,
I'd like to take a table in a database and modify some rows and delete
some others. So I created a dataset, and tried to make modifications
to one of its tables, and then I tried to send it back to the
database. But I got an error message: "update requires a valid
UpdateCommand when passed datarow collection with Modified rows."
Here is my code fragment. What's missing? I looked at the Microsoft
MSDN pages, but they often seem to explain one method at a time,
without showing how to use the methods together to accomplish a task.
-- HA

Function PlayWithExperimentalTable() As Boolean
Dim myOleDbDataAdapter As OleDbDataAdapter
Dim myDataSet As DataSet = New DataSet
Dim ChangesDataSet As DataSet = New DataSet
Dim myDataTable As DataTable = New DataTable
Dim myOleDbconnection As OleDbConnection
Dim retval As Boolean
Dim RowCount As Integer
Dim Index As Integer
Dim myDisplayRow As DataRow
Dim strSQL As String
Dim FirstName As String
Dim LastName As String
Dim Age As Integer

strSQL = "Select * from ExperimentalTable"

If ConnectToAddinDbase(constStockDatabase, myOleDbconnection)
Then
Try
myOleDbDataAdapter = New OleDbDataAdapter(strSQL,
myOleDbconnection)
myOleDbDataAdapter.TableMappings.Add("Table",
"ExperimentalTable")
myOleDbDataAdapter.Fill(myDataSet)
myDataTable = myDataSet.Tables("ExperimentalTable")
RowCount = myDataTable.Rows.Count
If RowCount = 0 Then
ErrorBox("No Records found in experimentalTable")
Else
For Index = 0 To RowCount - 1
myDisplayRow = myDataTable.Rows(Index)
FirstName = myDisplayRow("FirstName").ToString
LastName = myDisplayRow("lastname").ToString
Age = CInt(myDisplayRow("Age"))
myDataTable.Rows(Index)("FirstName") =
FirstName & "-" & Left(FirstName, 1) & "junior"
If Index = 5 Then
myDataTable.Rows(Index).Delete()
End If
Next
If myDataSet.HasChanges(DataRowState.Modified
Or DataRowState.Added Or DataRowState.Deleted) Then

' Use GetChanges to extract subset.
ChangesDataSet =
myDataSet.GetChanges(DataRowState.Deleted Or _
DataRowState.Modified Or
DataRowState.Added)

myOleDbDataAdapter.Update(ChangesDataSet)
End If
End If
retval = True
Catch ex As Exception
InternalErrorBox("Error Reading From TS2IBSymbol.
Internal Message: " & ex.Message & ". routine was
'PlayWithExperimentalTable', sql statement was " & strSQL)
retval = False
Finally
myOleDbconnection.Close()
myOleDbconnection = Nothing
End Try
Return (retval)
Else
Return (False)
End If
End Function
 
The DataAdapter must be configured one way or another--unlike ADO classic
where the action commands were generated on the fly, in ADO.NET the
responsibility to create the InsertCommand, UpdateCommand and DeleteCommand
is yours. Of course, you can relegate this responsibility to the Visual
Studio Data Source code generator which uses the CommandBuilder. This
approach closely approximates the ADO classic approach but many developers
soon grow out of it as it has a number of significant limitations.

I discuss how to build the TableAdapter in Chapter 6 and elsewhere...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
The message means exactly what it says: you need to create an UpdateCommand
(which is what actually submits your data changes to the database).
 
Back
Top