📚 SAP Business One SDK Help

DoQuery Method
See Also  Example
QueryStr

Specifies a query string, which contains the SQL query commands you want to run.

Description

Runs SQL queries.

Syntax

Visual Basic
Public Sub DoQuery( _
   ByVal QueryStr As String _
)

Parameters

QueryStr

Specifies a query string, which contains the SQL query commands you want to run.

Return Type

-2000 ODBC Error.

Remarks

If the method is successful, the object will contain the returned query result set.

Note: As of SAP Business One 10.0, the DoQuery function can access the current logon company DB only. If you access Common DB, the DoQuery function will throw exception.

Limitation: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Other limitations includes the following query usage: 

  • Nested Query
  • Query with Union and Union ALL
  • Using Like
  • Using Ali
  • Flow control statments (If / While / Break / Continue)
  • Calling procedures, fuctions, triggers and etc.
  • Distinct (i.e: "SELECT DISTINCT CardType FROM OCRD")
  • Multiple queries (i.e: Queries seperated with ; (semicolons) may not work, if there is no space before it)

Example

The following example shows the DoQuery and MoveNext methods.
DoQuery and MoveNext methods sample (Visual Basic)Copy Code
Dim Count As Long
Dim FldName As String
Dim FldVal As String
Dim i As Long
Dim RecSet As SAPbobsCOM.Recordset
Set RecSet = vCmp.GetBusinessObject(BoRecordset)

RecSet.DoQuery ("select * from OADM")

Count = RecSet.Fields.Count
  While RecSet.EOF = False
        'The inner loop runs over all the fields in one record (line) of the table
        For i = 0 To Count - 1
            FldName = RecSet.Fields.Item(i).Name
            FldVal = RecSet.Fields.Item(i).Value
            'Here you can manipulate the data as you want
        Next i
        'Move to the next record
        RecSet.MoveNext
  Wend

See Also