Closing a record set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Closing a record set

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-13 : 17:54:50

Hi,

I have opened a connection, then I declare 2 record sets

After I use the first record set I call recset1.close

Then, we I try to use the second record set, I get an error
saying that I can not work on a close record set.

Anyone know why?

thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-13 : 18:04:56

Are you doing something like
SET RS2 = RS1

When you close #1, you are closeing #2 because #2 references #1.

If this is not the case, post your code so we can help better.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-13 : 19:02:02

'Get an ID for this record.
Dim sMyQuery As String
sMyQuery = "SELECT * FROM ID"

Dim oRs1 As New ADODB.Recordset
oRs1.Open sMyQuery, oConn, adOpenStatic, adLockOptimistic

Dim value As Single
value = oRs1.Fields("Number")

sMyQuery = "UPDATE ID SET Number = '" & (value + 1) & "'"

Dim oRs2 As New ADODB.Recordset
oRs2.Open sMyQuery, oConn, adOpenStatic, adLockOptimistic

ReturnID = value

oRs1.Close
oRs2.Close

When I get to ors2.close i get the message,
operagion is not allowed when object is closed

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-13 : 19:57:00

The update statement doesn't produce a recordset.
You can just use the execute method of the command object without a recordset.
For the ones that do just set the recordset to the result of the above command.

Have a look at
http://www.nigelrivett.net/VB6DataAccessLayer.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-13 : 22:15:18

Hmm, so I don't have to write
Set oRs = oConn.Execute(sMyQuery)

I just need to write
oConn.Execute sMyQuery

when doing an update?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-14 : 00:58:46

That should work. I always use a command object as this allows you to9 pass parameters and get return values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources