While a datareader can be a great way to loop through datasets, they can also have unwanted side effects when you need to update the underlying data through say a ado object, because the datareader keeps your base data locked - thereby making updates impossible.

The main cause is that the datareader loops sequenialy through your records, and keeps that dataset locked during the loop.

In such a case, reading the selection into a table object and modifieing the data there works, because all modifications are made within the table object, and are then commited as a batch.



Dim mydataset As New DataSet
Dim mytable As New DataTable
Dim myrow As DataRow
 
Dim combuilder As New SqlCommandBuilder
Dim obj_sqlconnection As New SqlConnection(My.Settings.YourConnectionString)
Dim obj_dataadapter As New SqlDataAdapter
 
' load one or more tables into the dataset object
obj_dataadapter.SelectCommand = New SqlCommand("SELECT * FROM .... WHERE ....", obj_sqlconnection)
combuilder = New SqlCommandBuilder(obj_dataadapter)
combuilder.GetUpdateCommand()
obj_sqlconnection.Open()
obj_dataadapter.Fill(mydataset, "....tablename....")     ' place the sql cmd selected tabledata from the sql server into the dataset
 
'  place the data from the dataset in a table object to work with it
mytable = mydataset.Tables("....tablename....")        
 
 
For Each myrow In mytable.Rows
 ' process records
 Do While Len(Trim(myrow.Item("..someitem.."))) < 10
  myrow.Item("....someitem....") = "0" & Trim(myrow.Item("....someitem...."))
 Loop
 
Next
 
mytable.GetChanges()                                    ' save modified rows into the table object
obj_dataadapter.Update(mydataset, "....tablename....")  ' save the modified data object into the dataset
obj_sqlconnection.Close()                               ' close the connection

 

 

 


Visitor Comments