Not allowed when connection is closed problem 2

[chris921]

Combo1.Text = "Select Stage"
    SQL = "SELECT * FROM STAGETEMP where wfname = '" & WorkFlowName & "'"
        If Temporary.TemporaryData.State = 0 Then
        Temporary.TemporaryData.Open
    End If
    rsStageTemp = Temporary.TemporaryData.Execute(SQL, adOpenForwardOnly, adLockReadOnly)

    'ERROR - OPEN/CLOSED DATABASE STUFF
    'Temporary.rsStageTemp.Source = "SELECT * FROM STAGETEMP where wfname = " & WorkFlowName
    'If Temporary.TemporaryData.State = 0 Then Temporary.TemporaryData.Open
    'If Temporary.TemporaryData.State <> 0 Then
    '    Temporary.TemporaryData.Close
   ' End If
    If Temporary.rsStageTemp.State = 0 Then
        rsStageTemp = Temporary.rsStageTemp.Open(SQL, conn, adOpenForwardOnly, adLockReadOnly)
    End If
    'Temporary.TemporaryData.Cancel
    'If (Temporary.rsStageTemp.State = 1) Then
    
        Do While Not Temporary.rsStageTemp.EOF          'loads the various stages in
            Combo1.AddItem (i & " " & Temporary.rsStageTemp.Fields("STAGENAME"))
            i = i + 1
            Temporary.rsStageTemp.MoveNext
        Loop
    'End If

Hi - I have the above code, when I get to the Do While Not loop - it says not allowed when connection is closed, yet wont allow me to open it.

As you can see from what I've commented out, I've tried opening the connection (called 'TemporaryData') the recordSet (rsStageTemp - throws another error when I do that!)

I'm a novice so I've done something (I'm sure) glaringly obvious.

Any chance someone could point it out to me? Wits end here....

Thanks!

 

[RonRepp]

Dim Temporary.rsStageTemp as ADODB.Recordset
Set Temporary.rsStageTemp = New ADODB.Recordset

Temporary.rsStageTemp.Open SQL, Connection

Do While Not Temporary.rsStageTemp.EOF          'loads the various stages in
            Combo1.AddItem (i & " " & Temporary.rsStageTemp.Fields("STAGENAME"))
            i = i + 1
            Temporary.rsStageTemp.MoveNext
        Loop
Temporary.rsStageTemp.Close
Set Temporary.rsStageTemp=Nothing

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[chris921]

Hi,

Thanks for your reply - the following come up as "invalid use of property":

Set Temporary.rsStageTemp = New ADODB.Recordset
Set Temporary.rsStageTemp=Nothing

And it wont let me do the following at all:

Dim Temporary.rsStageTemp as ADODB.Recordset

 

[johnwm]

I suppose you have got a reference to Microsoft ActiveX Data Objects library set in Projects|References

Also can you clarify what sort of object Temporary is?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[chris921]

Temporary" is the database object - its in the "designers" folder on the right if you use Microsoft's own VB6 development program?

 

[SBerthold]

>yet wont allow me to open it.
Which means?

You need to show us what error
Temporary.TemporaryData.Open
is throwing

Otherwise, if your connection is Temporary.TemporaryData, why are you setting the activeconnection property of the recordset's Open statement to "conn" ?

 

[BobRodes]

I'm mystified.

First off: Ron, I've never seen anything like what you have up there. I wasn't aware that you could use a period in a variable name in conjunction with a Dim statement. Perhaps you can explain the circumstances under which you are able to do so? Because when I try to put that up, I get a syntax error. Second: Chris, could you tell me exactly what you do to find this database object? I've never heard of it (which is NOT to say that it doesn't exist). Moreover, googling doesn't seem to reveal anything.

Thanks,

Bob

 

[SBerthold]

>what you do to find this database object
It looks like the DataEnvironment (Project-Components-Designer tab), or some other self made Data class.

If DE, then Temporary.rsStageTemp looks like a recordset already created int the DE, and the Source is just being modified.

Chris, have you tested the Connection in the DE to see if it works properly?

I am no regular user of the DE, but I think you may be mixing things up here.

Either you want to open a recordset object already defined in the DE (rsStageTemp), or you want to create a new recordset using a recordset object variable (rsTemp ) on the DE Connection's (Temporary.TemporaryData) Execute method.

For the first:

    Dim rsTemp      As ADODB.Recordset
    Dim SQL         As String
    Dim I           As Long
    
    SQL = "SELECT * FROM STAGETEMP where wfname = '" & WorkFlowName & "'"
    
    If Temporary.TemporaryData.State = adStateClosed Then
        Temporary.TemporaryData.Open
    End If

    Set rsTemp = Temporary.TemporaryData.Execute(SQL, adOpenForwardOnly, adLockReadOnly)
    
    Do While Not rsTemp.EOF          'loads the various stages in
        Combo1.AddItem (I & " " & rsTemp.Fields("STAGENAME"))
        I = I + 1
        rsTemp.MoveNext
    Loop
    rsTemp.Close

For the second, you assign a recordset object variable (rsStageTemp) to the DE Recordset object (Temporary.rsStage):

    Dim rsTemp      As ADODB.Recordset
    Dim I           As Long
    
    If Temporary.TemporaryData.State = adStateClosed Then
        Temporary.TemporaryData.Open
    End If
    
    Set rsTemp = Temporary.rsStageTemp

    If rsTemp.State = adStateClosed Then rsTemp.Open

    Do While Not rsTemp.EOF          'loads the various stages in
        Combo1.AddItem (I & " " & rsTemp.Fields("STAGENAME"))
        I = I + 1
        rsTemp.MoveNext
    Loop
    rsTemp.Close

A third method would be to use the DE recordset object more directly:

    Dim I           As Long
    If Temporary.TemporaryData.State = adStateClosed Then
        Temporary.TemporaryData.Open
    End If
    
    If Temporary.rsStageTemp.State = adStateClosed Then Temporary.rsStageTemp.Open

    Do While Not Temporary.rsStageTemp.EOF          'loads the various stages in
        Combo1.AddItem (I & " " & Temporary.rsStageTemp.Fields("STAGENAME"))
        I = I + 1
        Temporary.rsStageTemp.MoveNext
    Loop
    Temporary.rsStageTemp.Close

And yet a fourth method would be to use a mixture of the first or second methods, redefining the DE recordset object's Source:

    Dim rsTemp      As ADODB.Recordset
    Dim SQL         As String
    Dim I           As Long
    
    SQL = "SELECT * FROM STAGETEMP where wfname = '" & WorkFlowName & "'"
    
    If Temporary.TemporaryData.State = adStateClosed Then
        Temporary.TemporaryData.Open
    End If

    Set rsTemp = Temporary.rsStageTemp
    If rsTemp.State <> adStateClosed Then rsTemp.Close
    
    rsTemp.Source = SQL
    rsTemp.Open
    
    Do While Not rsTemp.EOF          'loads the various stages in
        Combo1.AddItem (I & " " & rsTemp.Fields("STAGENAME"))
        I = I + 1
        rsTemp.MoveNext
    Loop
    rsTemp.Close

Another thing is, that the State of the Temporary.TemporaryData is probably closed when the application runs, but it should automatically open when the recordset is opened. But you can do this also manually in code, as shown.

 

[BobRodes]

If you're using a DE, the recordset automatically opens when the application does. And what I'm saying is that a variable name can't contain a period, basically.

 

[SBerthold]

>And what I'm saying is that a variable name can't contain a period, basically.
I agree 100%

What I shortly answered was only to the other part:
>>what you do to find this database object

>If you're using a DE, the recordset automatically opens when the application does

I'm not sure I can agree with that.
My findings are that connection and DECommand recordsets are closed when the application runs, unless some other code or property tells to do otherwise.

For each DECommand recordset object in the DE, there is also a companion DECommand execution/open method, seperate from the DECommand object:

Example:
- a DECommand is named StageTemp and is CommandText

- The DECommand object is accessed through a DE property called Temporary.rsStageTemp

- The DE Execution method for the DECommand object "StageTemp" is also called "StageTemp" and executed using Temporary.StageTemp

- But the DECommand object is still accessed through Temporary.rsStageTemp

The DE presents two items: One is a method to open/execute the command. The other is one to access the command object's properties and methods.

Calling Temporary.StageTemp in code will open the connection (if closed) and execute the DECommand (open the rsStageTemp recordset in this case). You then use Temporary.rsStageTemp in order to access the recordset.
You can also open the recordset by using Temporary.rsStageTemp.Open

Regardless, a check ahould still be made whether the connection, or recordset is open or not, as the connection can be also closed at run time.

In the 3rd example in my previous post the line:

If Temporary.rsStageTemp.State = adStateClosed Then Temporary.rsStageTemp.Open

could be changed to:

If Temporary.rsStageTemp.State = adStateClosed Then Temporary.StageTemp

Which would open the connection, if not already opend, and open the recordset, which will return an error if already open, just like the Temporary.rsStageTemp.Open would.

These are my observations, and maybe there are other differences.

 

[BobRodes]

<I'm not sure I can agree with that.

You're quite correct, SBerthold, and my apologies. I've always used the data environment for the handy drag and drop feature, which automatically binds text boxes to fields in the recordset. This also automatically opens the recordset.

<The DECommand object is accessed through a DE property called Temporary.rsStageTemp

Now, that's not quite correct, or perhaps needs to be clarified. The property you're mentioning is in fact a reference to an ADO Recordset object, and it is that object that's accessed in the manner you describe. The command object is only associated with this object in a transient sense. The relationship is analogous to using a command's commandtext property as the source argument when opening a recordset object. So, given a data environment de, a connection cn, and a command cmd, there will be a recordset object rscmd.

Now, I did a little testing. If you have a text box that's bound (DataSource property is set to the data environment, DataField property is set to one of the fields in the recordset), then the state of the recordset will be open upon testing. If you do not have a bound control, it will be closed. You can open it simply by issuing the command (using my objects) [tt]de.rscmd.open[/tt]. In my case, I'm using Northwind.db, and the command object cmd has the sql statement "select * from customers". When I open rscmd it holds the customers table. However, if I instead issue the command with an additional sql argument, the argument in the command object will be overridden. For example, if I issue the command [tt]de.rscmd.open "select * from orders"[/tt] the command object is ignored, and rscmd holds the contents of the orders table.

Bob

 

[BobRodes]

Reading a little further in your post: using the command name as a method of the data environment (which it is) is a bit more generic, since if the command is an action query (add/update/delete) this is the way to run it. You will also notice that if you have, say, an update query in your command, you'll find that there is no rscommand type recordset associated with it. The data environment is smart enough to know whether the command's associated sql statement will return a recordset or not.

HTH

Bob

 

[chris921]

Thank you everyone

I've now learnt how to do this stuff pretty much.

For those who wanted to know, I'd stupidly closed the connection in a routine somewhere meaning it was only throwing errors when this routine had been activated - confusing me further!

But thanks for all the help