Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA 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 April 7th, 2005, 10:19 AM
Registered User
 
Join Date: Apr 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default run-time error '3704'

Hello,
sorry maybe my topic is repeat other but I can't find out any answers.
So,
I have subjects mistake when I execute following code in Excel 2003:

Code:
Private Sub CommandButton1_Click()
Dim sDate1 As String
Dim sDate2 As String
Dim sClid As String

sDate1 = "2005-01-01"
sDate2 = "2005-12-31"
sClid = "0001"

Dim conG As ADODB.Connection
Set conG = New ADODB.Connection
Dim strConG As String

strConG = "PROVIDER=SQLOLEDB.1;Password=*****;Persist Security Info=True;User ID=*****;"

strConG = strConG & "DATA SOURCE=*****;INITIAL CATALOG=*****;"

conG.Open strConG

Dim Cmd1 As ADODB.Command
Dim rsG As ADODB.Recordset

Set Cmd1 = New ADODB.Command
Set rsG = New ADODB.Recordset

Cmd1.ActiveConnection = conG
Cmd1.CommandText = "sp_rep_up002"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = sDate1
Cmd1.Parameters(2).Value = sDate2
Cmd1.Parameters(3).Value = sClid

Set rsG = Cmd1.Execute()

rsG.NextRecordset
With rsG
    Sheet2.Range("A4").CopyFromRecordset rsG
    .Close
End With

End Sub
I use MS SQL 2000 Server. My stored procedure works fine - I've test it.

Any comments?
Thank you
 
Old April 7th, 2005, 10:30 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Does this help?

http://support.microsoft.com/default...b;en-us;190410

cheers

Matthew

 
Old April 7th, 2005, 12:00 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Virtually no one recognizes error by their number; what doe it say?

It would really be helpful if you could shed some light on [u]which statement</u> generates the error . . .
 
Old April 7th, 2005, 11:45 PM
Registered User
 
Join Date: Apr 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi guys,
Thx for your answers.

Here my code:
Code:
Dim sDate1 As String
Dim sDate2 As String
Dim sClid As String

sDate1 = "2005-01-01"
sDate2 = "2005-12-31"
sClid = "0001"

Dim conG As ADODB.Connection
Set conG = New ADODB.Connection
Dim strConG As String

strConG = "PROVIDER=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=*****;"

strConG = strConG & "DATA SOURCE=****;INITIAL CATALOG=****;"

conG.Open strConG

Dim Cmd1 As ADODB.Command
Dim rsG As ADODB.Recordset

Set Cmd1 = New ADODB.Command
Set rsG = New ADODB.Recordset

Cmd1.ActiveConnection = conG
Cmd1.CommandText = "parmupd_rep"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = sDate1
Cmd1.Parameters(2).Value = sDate2
Cmd1.Parameters(3).Value = sClid

Set rsG = Cmd1.Execute()

With rsG
    Sheet2.Range("A4").CopyFromRecordset rsG  
   .Close
End With
My error is:
" Run-time error '3704':

The operation requested by the application is not allowed if the object is closed. "

And here my sp:
Code:
CREATE PROCEDURE dbo.parmupd_rep (@STARTDAT VARCHAR(10), @ENDDAT  VARCHAR(10), @ClID VARCHAR(4)) AS

/*  Variables for goods */
DECLARE @GActive SMALLINT
DECLARE @GCODE VARCHAR(25)
DECLARE @GNAME VARCHAR(51)
DECLARE @GQty1 INT
DECLARE @GQty2 INT
DECLARE @GSum1 FLOAT
DECLARE @GSum2 FLOAT
/* End goods Variables */

/* Data variables */
DECLARE @ENDDATE SMALLDATETIME,@STARTDATE SMALLDATETIME
/* End Data variables */

SET @GQty1 = 0
SET @GQty2 = 0
SET @GSum1 = 0.00
SET @GSum2 = 0.00

SET     @STARTDATE = CONVERT(SMALLDATETIME, @STARTDAT , 121)
SET     @ENDDATE =   CONVERT(SMALLDATETIME, @ENDDAT   , 121)

IF OBJECT_ID('GoodsT') IS NOT NULL DROP TABLE GoodsT

CREATE TABLE GoodsT(
   GActive SMALLINT,   GCode Varchar(25), GName Varchar(51), Qty1 INT NULL, Sum1 FLOAT, Qty2 INT NULL, Sum2 FLOAT) ON [PRIMARY]

DECLARE Goods_CURSOR CURSOR FOR 
 SELECT Goods.ACTIVE, GOODS.CODE, GOODS.NAME 
 FROM LG_020_ITEMS GOODS
 ORDER BY GOODS.Name, GOODS.CODE

OPEN Goods_CURSOR 
FETCH NEXT FROM Goods_CURSOR INTO @GActive,@GCODE,@GNAME
WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO GoodsT(GActive,GCode,GName,Qty1,Sum1,Qty2,Sum2)
        VALUES(@GActive,@GCODE,@GNAME,@GQty1,@GSum1,@GQty2,@GSum2)

   FETCH NEXT FROM Goods_CURSOR INTO @GActive,@GCODE,@GNAME
 END

CLOSE Goods_CURSOR
DEALLOCATE Goods_CURSOR

DECLARE Data_07_CURSOR CURSOR FOR 
 SELECT GOODS.CODE GCode, SUM(STRNS1.AMOUNT) KOL_VO, SUM(STRNS1.LINENET) SUMMA
 FROM LG_020_02_STLINE STRNS1, LG_020_CLCARD CLIENT, LG_020_ITEMS GOODS
 WHERE
 STRNS1.STOCKREF = GOODS.LOGICALREF AND STRNS1.CLIENTREF=CLIENT.LOGICALREF AND (STRNS1.TRCODE IN (7,8)) 
 AND STRNS1.STFICHEREF<>0 AND ((STRNS1.LINETYPE=0) OR (STRNS1.LINETYPE=1)) AND STRNS1.DATE_<=@ENDDATE 
 AND STRNS1.DATE_>=@STARTDATE AND STRNS1.CANCELLED=0
 AND Client.Code = @ClID
 Group BY
 GOODS.CODE, STRNS1.STOCKREF

OPEN Data_07_CURSOR 
FETCH NEXT FROM Data_07_CURSOR INTO @GCODE,@GQty1,@GSum1
WHILE @@FETCH_STATUS = 0
 BEGIN
  UPDATE GoodsT 
   SET Qty1 = @GQty1, Sum1 =@GSum1
   WHERE GoodsT.GCode = @GCODE  

   FETCH NEXT FROM Data_07_CURSOR INTO @GCODE,@GQty1,@GSum1
 END

CLOSE Data_07_CURSOR
DEALLOCATE Data_07_CURSOR

DECLARE Data_02_CURSOR CURSOR FOR 
 SELECT GOODS.CODE GCode, SUM(STRNS1.AMOUNT) KOL_VO, SUM(STRNS1.LINENET) SUMMA
 FROM LG_020_02_STLINE STRNS1, LG_020_CLCARD CLIENT, LG_020_ITEMS GOODS
 WHERE
 STRNS1.STOCKREF = GOODS.LOGICALREF AND STRNS1.CLIENTREF=CLIENT.LOGICALREF AND (STRNS1.TRCODE IN (2,8)) 
 AND STRNS1.STFICHEREF<>0 AND ((STRNS1.LINETYPE=0) OR (STRNS1.LINETYPE=1)) AND STRNS1.DATE_<=@ENDDATE 
 AND STRNS1.DATE_>=@STARTDATE AND STRNS1.CANCELLED=0
 AND Client.Code = @ClID
 Group BY
 GOODS.CODE, STRNS1.STOCKREF

OPEN Data_02_CURSOR 
FETCH NEXT FROM Data_02_CURSOR INTO @GCODE,@GQty2,@GSum2
WHILE @@FETCH_STATUS = 0
 BEGIN
  UPDATE GoodsT 
   SET Qty2 = @GQty2, Sum2 =@GSum2
   WHERE GoodsT.GCode = @GCODE  

   FETCH NEXT FROM Data_02_CURSOR INTO @GCODE,@GQty2,@GSum2
 END

CLOSE Data_02_CURSOR
DEALLOCATE Data_02_CURSOR

SELECT * FROM GoodsT
GO
And finally:
Simple "SELECT * FROM GoodsT" works fine...

 
Old April 8th, 2005, 11:53 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Between these lines
Code:
    Set rsG = Cmd1.Execute()

    With rsG
    insert a test of rsg to check whether it is open or not.

I am certain that you have properly set up the reference to Sheet2 but are you sure that the sheet has been opened?
 
Old April 8th, 2005, 11:37 PM
Registered User
 
Join Date: Apr 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Brian,

As you advise I insert
Code:
Set rsG = Cmd1.Execute()

MsgBox rsG.Fields(0)

With rsG
'   Sheet2.Range("A4").CopyFromRecordset rsG
    .Close
End With
but I've another error code:

Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal.

It's very strange. I think that it maybe in my sp problem. When I remove code where I use "CURSOR" - all works fine. But with "CURSOR" it wouldnt work.
:(

Jedem das Seine
 
Old July 27th, 2007, 09:31 PM
Registered User
 
Join Date: Jul 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sphinx85 Send a message via Yahoo to sphinx85
Default

Good Day!

I just want to know what are the reason why When i try to set up my computer.. I counter this problem that states : Run time error "3704" operation is not allowed when the object is closed...
what does it mean? please help me in solving this one...

Thanks

 
Old July 28th, 2007, 08:45 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Before I begin I have to point out I am not an SQL Programmer.
However, heres what Ive got.

1. Your initial error indicated the connection to the DB was closed.
2. When attempting to query the first field with rsG.Fields(0) the "Item not found" error was thrown.
3. You say when removing the CURSOR statement the error is fixed.

The above says to me that your recordset is closed. Instead of your rsG.Fields(0) statement
insert 'Debug.Assert rsG.State = ObjectStateEnum.adStateOpen' this will cause the
compiler to break if the connection is not open.

Check the debug code and see if it stopping there, implying the connection isn't open.

If it is not, then I would recommend checking your stored procedure and ensure it is not the
closing the connection.

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
 
Old September 18th, 2009, 09:36 AM
Authorized User
 
Join Date: Jul 2005
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello was wondering if there was any resolution to this as I am having a very similar issue. Not sure whether the problem is with my VBA code or in my stored procedure. SP uses table variables. When I run the macro from Visual Basic Editor in Excel I can see my sp firing in SQL Profiler. SP it seems is executing. However I get the same error.
Inserted the "'Debug.Assert rsG.State = ObjectStateEnum.adStateOpen' as robzyc suggested. It seems as if I am having a connection issue maybe. Any suggestions for a fix would be greatly appreciated.

Code:
 Sub GetClevelabsActualsforMaureenQ1()
     'This was set up using Microsoft  ActiveX Data Components version 6.0
     
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range
Dim Rs1 As ADODB.Recordset
Dim Cmd1 As ADODB.Command
 
Set cn = New ADODB.Connection
'Use the SQL Server OLE DB Provider.
strConn = "Provider=SQLOLEDB.1;Data Source=CMDIVST004\JASON08;Initial Catalog=QuoteDB;User Id=cmdiapp;Password=appadmin;"
'Now open the connection.
cn.Open strConn
' Open recordset.
   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = cn
   Cmd1.CommandText = "sprocgetActuals"
   Cmd1.CommandType = adCmdStoredProc
   Cmd1.Parameters.Refresh
   Cmd1.Parameters(1).Value = 2009
   Cmd1.Parameters(2).Value = "mphillips"
   Cmd1.Parameters(3).Value = "January"
   Cmd1.Parameters(4).Value = "February"
   Cmd1.Parameters(5).Value = "March"
   Cmd1.Parameters(6).Value = "Clevelabs"
   
   Set Rs1 = Nothing
   
   Set Rs1 = Cmd1.Execute()
'   Debug.Assert Rs1.State = ObjectStateEnum.adStateOpen
 

  
  
   Set wbBook = ActiveWorkbook
   Set wsSheet = wbBook.Worksheets(1)
   With wsSheet
        Set rnStart = .Range("C9")
        
    End With
   
    
'Here we add the Recordset
    rnStart.CopyFromRecordset Rs1
   
   Rs1.Close
   Set Rs1 = Nothing
   
   
  End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
run-time error(s) Chacko C++ Programming 0 March 4th, 2007 02:28 PM
run time error ashishroyk Java GUI 0 October 8th, 2004 01:42 AM
Run Time Error JBond Access VBA 0 May 27th, 2004 09:50 AM
RUN-TIME ERROR compcad Beginning VB 6 2 May 21st, 2004 02:01 AM





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