Re: Closing recordset generates exception

Hans van de Laar wrote:
Hi,

I want to create a new record within a Try Catch block using the
following code

Function Process() As Boolean
Dim rsAttendance As New clsMSRecordSet
Dim rs As ADODB.Recordset
rs = rsAttendance.recordset
Try
rs.Open("AANWREG", Conn)
rs.AddNew()

rs.Fields("ADM#").Value = mfldAttAdmID
rs.Fields("CURSUS#").Value = mfldAttCourseID
rs.Fields("EVENEM#").Value = mfldAttEventID
rs.Fields("DEELNEM# " ).Value =
mfldAttParticipentID
rs.Fields("EV_PLN# ").Value = mfldAttPlanItemID

rs.Update()
Process = True
Catch ex As System.Runtime.InteropServices.COMException
'TO DO : logging exception to xml file
Finally
If (rs.State = ADODB.ObjectStateEnum.adStateOpen)
Then rs.Close() < - generating exception
rsAttendance = Nothing
End Try
End Function

When the update fails I get a descriptive message but I an other error
"operation not allowed in this context" is thrown up when the
rs.Close() is issued. Any idea why is this happening?
I'm using VB.NET and Visual Studio 2005

I think if you inspect the rs.State property during debugging you may
get more of a clue. A recordset's state can be a combination of
ObjectStateEnum values ...
http://msdn2.microsoft.com/en-us/library/ms675068.aspx

I share Stephen's concern with the "AANWREG" given that you are not
specifying the CommandType setting in the Open statement. Given the lack
of a syntax error, It seems that ADODB is correctly interpreting it and
using adCmdTable, but, for this operation (inserting a record), you
should probably be using a sql statement that does not return any rows.
Something like:

Dim sql as string = "Select ADM#, CURSUS#,EVENEM#, " & _
"DEELNEM#,EV_PLN# FROM AANWREG WHERE 1=2"
rs.Open(sql,conn,,,,1) '1=adCmdText
'etc.

Personally, I would not be using a recordset for this operation: just
execute a simple parameterized SQL INSERT statement using a Command
object (why are you insisting on using classic ADO anyways?)


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages