Opening and closing a recordset in VB6 [Resolved]

[olamm2k]

Hi,

I'm developing a database application in Visual Basic 6 and there are some occaisions where I need to execute two different queries on different tables. When I try and close the recordset I'm using to do the first query for the second query, I get an error saying that the recordset is already closed, despite the fact that I have just opened it.

Private Sub DoUpdate(ByVal objConn as ADODB.Connection, _
  ByVal strSurname as String, ByVal strNew as String, _
  ByVal intID as Integer)
Dim objRS as ADODB.Recordset
 
Set objRS = New ADODB.Recordset
 
objRS.Open "INSERT INTO Members(Surname) VALUES('" & strSurname & "');", _
  objConn, adOpenStatic, adLockPessimistic
 
objRS.Close
 
objRS.Open "UPDATE Register SET NewUser='" & strNew & _
  "' WHERE MemID=" & intID & ";", objConn, adOpenStatic, adLockPessimistic
 
objRS.Close
 
End Sub

Any ideas?

[Hack]

Check the state of your recordset object, and take whatever steps are appropriate.

If objRs.State = adStateOpen Then objRs.Close

[brucevde]

Insert and Update statements do not open a recordset.

You should use the Execute method of the Connection or Command objects to run these type of statements.

But you could always check to see if the recordset is open before attempting to close.

If Not objRs is Nothing Then
   If objRs.State = adStateOpen then
         objRs.Close
   End If
End If

[olamm2k]

My current version of the code does use an If statement to check the status - that was just an example of what I was trying before.

I didn't know INSERT and UPDATE don't open a recordset. How would I implement these queries using the Execute method of the Connection or Command objects as suggested?

[Hack]

Insert and Update statements do not open a recordset.

Very true...thats what I get for scanning the question rather than reading it. *slaps self*

How would I implement these queries using the Execute method of the Connection or Command objects as suggested?

You would use the command object

objConn.Execute "Your SQL String"

[olamm2k]

You would use the command object

objConn.Execute "Your SQL String"

That's the connection object. How would I do it with a Command object (I've never used one before)?

[Hack]

That's the connection object. How would I do it with a Command object (I've never used one before)?

I'm having a bad day. I meant the connection object, not the command object. I don't use the command object for INSERTS or UPDATES either, but I do use the connection object.

[olamm2k]

I've just been reading what MSDN has to say about Command objects, and I don't think I need to use one here, so I'll use the Execute method of the Connection object.

Thanks