Is the Object closed or open - 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

General SQL Server Forums

New to SQL Server Programming

Is the Object closed or open

Author

Topic

peterhw
Starting Member

12 Posts

Posted - 2012-06-12 : 11:26:57

I am 'playing' and learning with SQL and am creating a simple client interface in EXCEL / VBA with the following:-


'Public sSQL As String
'Public rs As ADODB.Recordset
'Public cn As ADODB.Connection
'Public cmdObj As ADODB.Command
'Public My_SQL_db As String
'Public My_SQL_Table As String
'Public My_SQL_field As String
    My_cn = "Provider = SQLOLEDB.1; Integrated Security = SSPI; Initial catalog = zzzzz ;  Data Source = ACER-X1301\SQLEXPRESS"
    My_cn = Replace(My_cn, "zzzzz", My_SQL_db)
    If cn.State = adStateOpen Then
        cn.Close
    End If
    cn.Open My_cn
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    t1 = sSQL   '
    rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

    If rs.EOF Then

when I run the code I get an error at the above (EOF)
'3704 Operation is not allowed when the object is closed'
The t1= sSQL just enables t1 to be displayed in a the locals window

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 12:26:19

what is the sql you are executing?
This happens if you populate a temp table before returning a resultset and don't have set nocount on in the code.
Could just be that you don't have a resultset though.

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

peterhw
Starting Member

12 Posts

Posted - 2012-06-13 : 03:32:23

The code is as below.
The code is part of a small application (a multi tab EXCEL program) which lists the available databases. Once a database (My_SQL_db) is selected then the tables are listed and again one is selected (My_SQL_Table). A final option allows a field to be selected and 1 (or more selection criteria established). Then the SQL query is created (sSQL) ready for execution.

USE Test SELECT * FROM Test_Table_1 WHERE 
test_age > 30

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 03:48:13

Check that the statement is being executed and returning a resultset is probably the first step.
Also stop the application after the rs.open and see what is there.

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

peterhw
Starting Member

12 Posts

Posted - 2012-06-13 : 06:33:02

Tried a # of options :-


    My_cn = "Provider = SQLOLEDB.1; Integrated Security = SSPI; Initial catalog = zzzzz ;  Data Source = ACER-X1301\SQLEXPRESS"
    My_cn = Replace(My_cn, "zzzzz", My_SQL_db)
    Set cn = New ADODB.Connection
    If cn.State = adStateOpen Then
        cn.Close
    End If
    cn.Open My_cn
    'sSQL = "USE Test SELECT * FROM Test_Table_1 WHERE test_age > 30"
    'sSQL =          "SELECT * FROM Test_Table_1 WHERE test_age > 30"
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    If rs.EOF Then
        MsgBox ("No records selected rs.EOF " & rs.EOF)
    Else

The commented out sSQL commands are 2 options that I pass from VBA. The first option fails (i.e. includes USE Test) but the second option runs and returns records. [Test_Table_1 is in database Test]. Initial catalog = zzzzz (replaced with Test).

Both commands work fine when I use directly in SSMS

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 06:43:18

then just use
SELECT * FROM Test..Test_Table_1 WHERE test_age > 30

Or connect to the correct database.

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

peterhw
Starting Member

12 Posts

Posted - 2012-06-13 : 07:07:20

quote:
Originally posted by nigelrivett

then just use
SELECT * FROM Test..Test_Table_1 WHERE test_age > 30

That works fine..... thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources