Query with parameters not working - why not?

[The_Grudge]

I have a few queries in my app that I've rewritten to work with parameters. They all work fine.

When I went to update this part of the code today though, I've run into trouble and can't get this working.
I need it to query data for six Wednesday's but since it isn't working I've commented everything out to try and get just a single Wednesday to return. Every time I execute the query it returns "An Error Has Occurred - No Value Given For One Or More Required Parameters".

I feel like this is a super silly error that I'm glazing over and could use another set of eyes.

Thanks.

Dim dtFirstWednesday As Date

 dtFirstWednesday = strWednesdayDate(0)
' dtSecondWednesday = strWednesdayDate(1)
' dtThirdWednesday = strWednesdayDate(2)
' dtFourthWednesday = strWednesdayDate(3)
' dtFifthWednesday = strWednesdayDate(4)
' dtSixthWednesday = strWednesdayDate(5)

'Now query the database using the dates of the 6 Wednesday's obtained above.
 strSQL = "Select Distinct PROD.CSU_CLINIC_INFO.CLINIC_DATE, PROD.CSU_CLINIC_INFO.OCCURS " & _
          "From PROD.CSU_CLINIC_INFO WHERE PROD.CSU_CLINIC_INFO.CLINIC_DATE = ?"
 

 Dim objCommand As ADODB.Command
 Set objCommand = New ADODB.Command
 
 With objCommand
      .ActiveConnection = SQLCon
      .CommandType = adCmdText
      .CommandText = strSQL
      .Prepared = True
      
 .Parameters.Append .CreateParameter("FirstWed", adDate, adParamInput, , dtFirstWednesday)
' .Parameters.Append .CreateParameter("SecondWed", adDate, adParamInput, , dtSecondWednesday)
' .Parameters.Append .CreateParameter("ThirdWed", adDate, adParamInput, , dtThirdWednesday)
' .Parameters.Append .CreateParameter("FourthWed", adDate, adParamInput, , dtFourthWednesday)
' .Parameters.Append .CreateParameter("FifthWed", adDate, adParamInput, , dtFifthWednesday)
' .Parameters.Append .CreateParameter("FifthWed", adDate, adParamInput, , dtSixthWednesday)
 
 
 End With
rsGetRotationWeeks.Open strSQL, SQLCon, adOpenStatic, adLockOptimistic

[dilettante]

You are not using the Command. You create it, assign some properties, use Parameters.Append to assign Parameter values, and then never use the Command.

[The_Grudge]

Of course!

Thank you. Now I'm getting an error that says "Operation Not Allowed when the Object is Open".

I have a global connection variable and my code now looks like this

strSQL = "Select Distinct PROD.CSU_CLINIC_INFO.CLINIC_DATE, PROD.CSU_CLINIC_INFO.OCCURS " & _
          "From PROD.CSU_CLINIC_INFO WHERE PROD.CSU_CLINIC_INFO.CLINIC_DATE = ?"
 
 Dim objCommand As ADODB.Command
 Set objCommand = New ADODB.Command
 
 With objCommand
      .ActiveConnection = SQLCon
      .CommandType = adCmdText
      .CommandText = strSQL
      .Prepared = True
      
 .Parameters.Append .CreateParameter("FirstWed", adDate, adParamInput, , dtFirstWednesday)
' .Parameters.Append .CreateParameter("SecondWed", adDate, adParamInput, , dtSecondWednesday)
' .Parameters.Append .CreateParameter("ThirdWed", adDate, adParamInput, , dtThirdWednesday)
' .Parameters.Append .CreateParameter("FourthWed", adDate, adParamInput, , dtFourthWednesday)
' .Parameters.Append .CreateParameter("FifthWed", adDate, adParamInput, , dtFifthWednesday)
' .Parameters.Append .CreateParameter("FifthWed", adDate, adParamInput, , dtSixthWednesday)
 
 rsGetRotationWeeks.CursorType = adOpenDynamic
 Set rsGetRotationWeeks = objCommand.Execute
 Set objCommand = Nothing
 
 End With
 rsGetRotationWeeks.Open strSQL, SQLCon, adOpenStatic, adLockOptimistic

[TysonLPrice]

You can't open a connection that is already open. What I try to do is only open a connection when needed and close it right away. Keeping a global connection open can cause issues. I don't know if it will hurt your logic flow but check if the connection is open first and close it. Check out this link:

http://www.vbforums.com/showthread.p...is-open-closed

[dilettante]

There are no "issues" with keeping Connections open and using them multiple times. Sounds more like an attempt to cargo-cult around buggy techniques.


You don't have named parameters in your query, so there is nothing to be gained by using the clunky Properties collection. It is far easier to use a named Command, which becomes an extended method of your Connection. For row-returning queries these methods expect a predefined Recordset with the desired characteristics as the last argument in the list. This is a lot easier to maintain as well:

    Dim Connection As ADODB.Connection
    Dim Command As ADODB.Command
    Dim FirstWednesday As Date
    Dim RotationWeeks As ADODB.Recordset

    Set Connection = ADODB.ConnectModeEnum
    Connection.Open "some connection string"
    FirstWednesday = #1/1/2011#

    '... whatever goes on before the query...

    Set Command = New ADODB.Command
    With Command
        .CommandType = adCmdText
        .CommandText = "Select Distinct PROD.CSU_CLINIC_INFO.CLINIC_DATE, " _
                     & "PROD.CSU_CLINIC_INFO.OCCURS From PROD.CSU_CLINIC_INFO " _
                     & "Where PROD.CSU_CLINIC_INFO.CLINIC_DATE = ?"
        .Name = "GetRotationWeeks"
        .ActiveConnection = Connection
    End With

    Set RotationWeeks = New ADODB.Recordset
    With RotationWeeks
        .CursorLocation = adUseClient 'Since you appear to want a static cursor.
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic 'Are you really trying to get an updateable rowset here?
                                     'Should this really be adLockReadOnly instead?
    End With

    Command.GetRotationWeeks FirstWednesday, RotationWeeks
    Set Command = Nothing 'Or just let it go out of scope.

    'Use RotationWeeks, then:
    RotationWeeks.Close

The bonus is you get the kind of Recordset you want and not just the forward-only, read-only, server-side cursor Recordset which is all you can get back from ADO's Execute methods.

[TysonLPrice]

There are no "issues" with keeping Connections open and using them multiple times. Sounds more like an attempt to cargo-cult around buggy techniques.


You don't have named parameters in your query, so there is nothing to be gained by using the clunky Properties collection. It is far easier to use a named Command, which becomes an extended method of your Connection. For row-returning queries these methods expect a predefined Recordset with the desired characteristics as the last argument in the list. This is a lot easier to maintain as well:

    Dim Connection As ADODB.Connection
    Dim Command As ADODB.Command
    Dim FirstWednesday As Date
    Dim RotationWeeks As ADODB.Recordset

    Set Connection = ADODB.ConnectModeEnum
    Connection.Open "some connection string"
    FirstWednesday = #1/1/2011#

    '... whatever goes on before the query...

    Set Command = New ADODB.Command
    With Command
        .CommandType = adCmdText
        .CommandText = "Select Distinct PROD.CSU_CLINIC_INFO.CLINIC_DATE, " _
                     & "PROD.CSU_CLINIC_INFO.OCCURS From PROD.CSU_CLINIC_INFO " _
                     & "Where PROD.CSU_CLINIC_INFO.CLINIC_DATE = ?"
        .Name = "GetRotationWeeks"
        .ActiveConnection = Connection
    End With

    Set RotationWeeks = New ADODB.Recordset
    With RotationWeeks
        .CursorLocation = adUseClient 'Since you appear to want a static cursor.
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic 'Are you really trying to get an updateable rowset here?
                                     'Should this really be adLockReadOnly instead?
    End With

    Command.GetRotationWeeks FirstWednesday, RotationWeeks
    Set Command = Nothing 'Or just let it go out of scope.

    'Use RotationWeeks, then:
    RotationWeeks.Close

The bonus is you get the kind of Recordset you want and not just the forward-only, read-only, server-side cursor Recordset which is all you can get back from ADO's Execute methods.

The issues as I see it are if you leave it open and try and open it again because of trying to keep a global one open and losing track of its state...I seems much cleaner to open, use, and close. I may be all wet but error seems to be from trying to open a connection when it is already open. So find out where it was left open.