Wrox Programmer Forums
|
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 8th, 2003, 02:09 PM
Authorized User
 
Join Date: Jun 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to sidneyfuerte Send a message via AIM to sidneyfuerte Send a message via MSN to sidneyfuerte Send a message via Yahoo to sidneyfuerte
Default SP with Results

Hi everyone,

I'm getting an error "run-time error '3704' operation is not allowed when object is closed." when i tried to access the content of my recordset, I am calling a stored procedure with the following sql statement:

sSq = "DECLARE @iIntId INT " & _
      "SET @iIntId = null " & _
      "exec GetID 1,@iIntId OUTPUT " & _
      "SELECT @iIntId " & _
rs.Open sSQl, gDbConn, adOpenDynamic, adLockOptimistic
if Not rs.Eof then
   MyId = rs("iIntId")
End if

The error occur in the "if Not rs.eof..." line co'z i debug the program and i'm sure the recordset has one row in it... the ID.

Can somebody explain to me why this is happening? I really appreciate any comment or help.

Thanks/Regards
Sidney
__________________
Thanks/Regards
Sidney
 
Old September 8th, 2003, 02:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

Ok, the code that you posted seems to have errors in it, So first lets assume that you didn't just mis-type in the post...

sSq = "DECLARE @iIntId INT " & _
      "SET @iIntId = null " & _
      "exec GetID 1,@iIntId OUTPUT " & _
      "SELECT @iIntId " & _
rs.Open sSQl, gDbConn, adOpenDynamic, adLockOptimistic
if Not rs.Eof then
   MyId = rs("iIntId")
End if

This looks like you are trying to add your rs.Open to the SQL string.

If your code is right, and this is just a typo, then I am wondering if you are getting enbedded recordsets back. The first recordset being the empty results from the exec GetID, and the ".NextRecordset" holding your id.

I didn't test this, but it is a thought.



John R Lick
JohnRLick@hotmail.com
 
Old September 8th, 2003, 05:09 PM
Authorized User
 
Join Date: Jun 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to sidneyfuerte Send a message via AIM to sidneyfuerte Send a message via MSN to sidneyfuerte Send a message via Yahoo to sidneyfuerte
Default

Yes it was just a typo, and your correct i want to have get the id as an output, if i execute this sql command in the querry analyzer i get 1 row back... which is the id. but when i execute it in vb where should i get the row? is it not in my current opened recordset?

I really appreciate your response to my question :)

Thanks/Regards
Sidney
 
Old September 9th, 2003, 08:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

I cannot recreate the error you got. What I do get is that the field name that you are assuming to get back does not exist. This is due to the fact that you are selecting a varaible, and not giving it a name.

You should use:
  SELECT iIntId = @iIntId

As far as the error that you got, I need more information since I can't recreate it.

John R Lick
JohnRLick@hotmail.com
 
Old September 9th, 2003, 01:48 PM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 205
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My initial hunch would be to check the connection object and make sure it is open. Just a couple of suggestions:

1. I don't know what your error handling looks like, but if you are not doing so use error lines to get which line it fails on. If it fails on the rs.Open, the connection is bad. If it fails on rs.EOF the recordset failed to execute properly.

2. Try to run the stored proc through a command object rather than a recordset (a technique, I happen to favor as a whole). See what happens.

Yehuda





Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit Query Results in Results Grid druid2112 SQL Server 2005 1 June 28th, 2007 08:49 AM
exec sp within another sp collie SQL Server 2000 1 December 22nd, 2004 05:46 AM
SP used in a SP Mitch SQL Server 2000 16 October 28th, 2003 12:59 PM
SP Variable hortoristic SQL Server 2000 2 July 22nd, 2003 06:01 PM
Can a SP run another SP as sa? dbradley SQL Server 2000 0 July 17th, 2003 08:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.