PdcDatabase.OpenQuery

Declaration

OpenQuery(Connection as pdcConnectionKind) as PdcDBQuery

Description

Return a IDBQuery for execution

Notes

This method opens a query for a specified SQL statement. The result set of records is available as a table. The input parameter is a connectionkind.

A select query without a join is always a live cursor. This means if you change records, the result of the query changes (recordcount) without calling the execute command again.

Code example

dim qryCustomer
dim intCnt

  set qryCustomer = pdc.Database.OpenQuery(pdccondata)
  qryCustomer.SQL = "SELECT * FROM LK_KLANT WHERE ((KL_SOORT = 'K') OR (KL_SOORT = 'B')) ORDER BY UCase(KL_NAAM)"

  if qryCustomer.Execute then
    if qryCustomer.RecordCount > 0 then
      qryCustomer.FirstRecord
      msgbox "The first customer is '" & qryCustomer.GetField("KL_NAAM") & "'."

      intCnt = 0
      do while not qryCustomer.Eof
        if UCase(Trim(qryCustomer.GetField("KL_PLAATS"))) = "AMSTERDAM" then
          intCnt = intCnt + 1
        end if
        
        qryCustomer.NextRecord
      loop
      msgbox intCnt & " customers found in Amsterdam."
    else
      msgbox "There are no records found in the result set."
    end if
  else
    msgbox "Error:" & vbCrLf & pdc.LastError & vbCrLf & vbCrLf & "SQL:" & vbCrLf & qryCustomer.SQL 
  end if

This example shows a situation with a live cursor. Every run of this script userfield 6 of one record is cleared with a second query. Directly after the execution of the UPDATE, the recordcount of the first query is changed.

dim qryDO_CALC
dim qryUpdateDO_CALC

  set qryDO_CALC = pdc.Database.OpenQuery(pdcconData)
  qryDO_CALC.SQL = "SELECT * FROM DO_CALC WHERE UPPER(DO_USER6)='TEST'"
  if qryDO_CALC.execute then
    msgbox qryDO_CALC.Recordcount
    qryDO_CALC.FirstRecord
    do while not qryDO_CALC.Eof
      set qryUpdateDO_CALC = pdc.Database.OpenQuery(pdcconData)
      qryUpdateDO_CALC.SQL = "UPDATE DO_CALC SET DO_USER6 = null WHERE DO_ORDNR = '" & qryDO_CALC.GetField("DO_ORDNR") & "'"
      if qryUpdateDO_CALC.execute then
        msgbox qryDO_CALC.RecordCount
        exit do
      end if
      set qryUpdateDO_CALC = nothing

      qryDO_CALC.NextRecord
    loop
  end if

For more help about the SQL language see the helpfile of Advantage.

Availability

Available since September 2008 (from version 3.8)