• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Loop through and update a recordset in ASP.NET

In ASP.NET, I need to loop through all records from a table in MS Access and update the content. Below I've tried to use a DataReader in a similar way to a Recordset in classic ASP - but I'm getting an error that "There is already an open DataReader associated with this Connection". When I tried creating a second connection object, I got an error that the record was locked by another process.  

--------------This is my non-working attempt -----------------
Dim oConn As OleDbConnection
   oConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" ...)
   oConn.Open()                                                
Dim oCmd As OleDbCommand
   oCmd = New OleDbCommand( "SELECT ContentID, PageText From tContent", oConn )
Dim oDR As OleDbDataReader
   oDR = oCmd.ExecuteReader()      
   'loop through the database
      Do While oDR.Read()
         NewPageText="insert updated txt here"      '-- this comes from a function
         strSQL="UPDATE tContent SET PageText='" & NewPageText & "' WHERE ContentID=" & oDR("ContentID")
         oCmd = New OleDbCommand( strSQL, oConn )
         oCmd.ExecuteNonQuery()
      Loop
  oDR.Close()
oConn.close      
      
This seems like it should be a simple thing, but apparently not. Please give me a working example of looping through and updating a recordset in ASP.NET.  Thanks!
0
Verdend
Asked:
Verdend
  • 3
  • 2
  • 2
1 Solution
 
Ramesh SrinivasTechnical ConsultantCommented:
.net doesnt allow you to open a connection whilst a datareader one is already open i think.

I would just loop through a dataset and execute your NonQuery.

regards,

KS
0
 
Ramesh SrinivasTechnical ConsultantCommented:
Fill a dataset with your records (called DS), then:

Dim oCmd As OleDbCommand
Dim dv As DataView = DS.Tables(0).DefaultView
Dim dr As DataRowView
oConn.Open()
For Each dr In dv
         NewPageText="insert updated txt here"      '-- this comes from a function
         strSQL="UPDATE tContent SET PageText='" & NewPageText & "' WHERE ContentID=" & dr("ContentID")
         oCmd = New OleDbCommand( strSQL, oConn )
         oCmd.ExecuteNonQuery()
Next
oConn.Close()

something like that!
0
 
b1xml2Commented:
Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" ...)
Dim selectCommand As New OleDbCommand( "SELECT ContentID, PageText From tContent", connection )
Dim updateCommand As New OleDbCommand("UPDATE tContent SET PageText= ? WHERE ContentID = ?",connection)
Dim adapter As New OleDbDataAdapter(selectCommand)
Dim table As New DataTable
Dim row As DataRow
connection.Open()
adapter.Fill(table)
For Each row in table.Rows
      updateCommand.Parameters.Clear()
      'example of passing value from a function to a parameter
      updateCommand.Parameters.Add("PageText",NewPageText())
      updateCommand.Parameters.Add("ContentID",row("ContentID"))
      updateCommand.ExecuteNonQuery()

Next
connection.Close()
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
b1xml2Commented:
You do not have to re-create the OleDbCommand every time you issue the UPDATE process. You just clear the parameters, or re-use them and execute the command.

Also, please consider using self-describing names. try to avoid hungarian notation like oConn and oCmd. Consider the developer friendly code that I have presented. Naming conventions are important.
0
 
VerdendAuthor Commented:
THANK YOU, b1xml2. That did just exactly what I needed!  

One last question -- for proper code, besides closing the connection object, do I need to close or dereference any of the other objects?
0
 
b1xml2Commented:
yes,
table.Dispose()
adapter.Dispose()
updateCommand.Dispose()
selectCommand.Dispose()
connection.Close()
0
 
VerdendAuthor Commented:
Thanks!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now