Verdend
asked on
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=
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!
--------------This is my non-working attempt -----------------
Dim oConn As OleDbConnection
oConn = New OleDbConnection("Provider=
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!
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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?
One last question -- for proper code, besides closing the connection object, do I need to close or dereference any of the other objects?
yes,
table.Dispose()
adapter.Dispose()
updateCommand.Dispose()
selectCommand.Dispose()
connection.Close()
table.Dispose()
adapter.Dispose()
updateCommand.Dispose()
selectCommand.Dispose()
connection.Close()
ASKER
Thanks!
I would just loop through a dataset and execute your NonQuery.
regards,
KS