2 Replies Latest reply on Sep 17, 2007 10:27 AM by 596927

    Binding in OO4O

      I've inherited some Excel 2002 VBA code which makes a number of similar calls to the database (9.2 client/10.2 server), I'd like to put a bind variable in to speed everything up and be nicer to the server.

      I've altered the SQL and put the Parameters.Add line into the function which hits the DB:
      With g_OraDatabase
      g_OraDatabase.Parameters.Add "CompId", sCompId, 1
      Set Results = .createdynaset(sSQL, 0&)
      ' do stuff with the results
      End With    
      This works the first time in the loop, but then fails telling me that "Results could not be retrieved
      OIP-04123: Duplicate parameter name, CompId"

      I presume that this is because I'm adding the parameter, how do I get it to update the value that I want bound.

      Thanks in advance for any help

        • 1. Re: Binding in OO4O
          Simon Greener

          You could either:

          1. Delete all Parameters and then add them in again...
              ' Delete any existing SQL parameters
              For i = 0 To mobjOraDatabase.Parameters.Count - 1
                mobjOraDatabase.Parameters.Remove 0
              Next i
              ' Add in new parameters...
              mobjOraDatabase.Parameters.Add "MYPARAM", <vba variable>, ORAPARM_INPUT, ORADB_DOUBLE
          2. Iterate over the Parameters collection and when you get a match on teh parameter name change its value...
          If debug Then
               For i = 0 To mobjOraDatabase.Parameters.Count - 1
                 If mobjOraDatabase.Parameters(i).Name = "MY VALUE" Then
                    ' Should check parameter data type and status..
                    mobjOraDynaset.Parameters(i).Value = ...
                 End If
               Next i
          End If
          • 2. Re: Binding in OO4O
            code it this way.

            With g_OraDatabase
            .Parameters.Add "CompId", sCompId, 1
            Set Results = .createdynaset(sSQL, 0&)
            ' do stuff with the results
            .Parameters.Remove "CompId"
            End With

            Have a nice day!