ADO/ADOX Examples

[José Roca]

 
ADO

Microsoft ActiveX Data Objects (ADO) enable your client applications to access and manipulate data from a variety of sources through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.

ADOX

Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based approach to schema manipulation, you can write code that will work against various data sources regardless of differences in their native syntaxes.

ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects.

[José Roca]

The following example illustrates the use of the AbsolutePage property.
The cursor location must be set to adUseClient.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_AbsolutePage.bas
' Contents: ADO example
' Demonstrates the use of the AbsolutePage, PageCount and PageSize properties.
' The cursor location must be set to %adUseClient.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL nPageCount AS LONG
   LOCAL nPageSize AS LONG
   LOCAL i AS LONG
   LOCAL x AS LONG
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Use client cursor to enable AbsolutePosition property
      pRecordset.CursorLocation = %adUseClient
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Display five records at a time
      pRecordset.PageSize = 5
      ' // Retrieve the number of pages
      nPageCount = pRecordset.PageCount
      ' // Parse the recordset
      FOR i = 1 TO nPageCount
         ' // Set the cursor at the beginning of the page
         pRecordset.AbsolutePage = i
         ' // Retrieve the number of records of the page
         nPageSize = pRecordset.PageSize
         FOR x = 1 TO nPageSize
            ' // Get the content of the "Name" column
            vRes = pRecordset.Collect("Name")
            ? VARIANT$$(vRes)
            ' // Fetch the next row
            pRecordset.MoveNext
            IF pRecordset.EOF THEN EXIT FOR
         NEXT
         WAITKEY$
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example illustrates the use of the AbsolutePosition property.
The cursor location must be set to adUseClient.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_AbsolutePosition.bas
' Contents: ADO example
' This example demonstrates how the AbsolutePosition property can track the progress of a
' loop that enumerates all the records of a Recordset. It uses the CursorLocation property
' to enable the AbsolutePosition property by setting the cursor to a client cursor.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the cursor location
      pRecordset.CursorLocation = %adUseClient
      ' // Open the recordset
      SqlStr = "Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdTable
      DO
        ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the absolute position
         PRINT "Position:" & STR$(pRecordset.AbsolutePosition) " ";
         ' // Get the Publisher's name
         vRes = pRecordset.Collect("Name")
         PRINT VARIANT$$(vRes)
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

This example illustrates the ActiveCommand property.

A subroutine is given a Recordset object whose ActiveCommand property is used to display the command text and parameter that created the recordset.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_ActiveCommand.bas
' Contents: ADO example
' This example demonstrates the use of the ActiveCommand property.
' A subroutine is given a Recordset object whose ActiveCommand property is used to display
' the command text and parameter that created the Recordset.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' The ShowActiveCommand routine is given only a Recordset object, yet it must print the
' command text and parameter that created the Recordset. This can be done because the
' Recordset object's ActiveCommand property yields the associated Command object.
' The Command object's CommandText property yields the parameterized command that was
' substituted for the command's parameter placeholder ("?").
' ========================================================================================
SUB ShowActiveCommand (BYVAL pConnection AS ADOConnection, BYVAL pRecordset AS ADORecordset)

   LOCAL bstrPrmName AS WSTRING
   LOCAL pCommand AS ADOCommand
   LOCAL bstrCommandText AS WSTRING
   LOCAL pParameters AS ADOParameters
   LOCAL pParameter AS ADOParameter
   LOCAL vValue AS VARIANT
   LOCAL bstrAuID AS WSTRING
   LOCAL bstrAuName AS WSTRING

   TRY
      pCommand = pRecordset.ActiveCommand
      bstrCommandText = pCommand.CommandText
      pParameters = pCommand.Parameters
      pParameter = pParameters.Item("Name")
      vValue = pParameter.Value
      bstrPrmName = VARIANT$$(vValue)
      PRINT "Command text: " & bstrCommandText
      PRINT "Parameter: " & bstrPrmName
      IF pRecordset.BOF THEN
         PRINT "Name = '" & bstrPrmName & "', not found"
      ELSE
         vValue = pRecordset.Collect("Author")
         bstrAuName = VARIANT$$(vValue)
         vValue = pRecordset.Collect("Au_ID")
         bstrAuID = STR$(VARIANT#(vValue))
         PRINT "Name = " & bstrAuName & ", ID = " & bstrAuID
      END IF
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   END TRY

END SUB
' ========================================================================================

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL pCommand AS ADOCommand
   LOCAL pRecordset AS ADORecordset
   LOCAL pParameters AS ADOParameters
   LOCAL pParameter AS ADOParameter

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create an ADO command object
   pCommand = NEWCOM "ADODB.Command"
   IF ISFALSE ISOBJECT(pCommand) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ADOCommand active connection
      pCommand.putref_ActiveConnection = pConnection
      ' // Set the command text
      pCommand.CommandText = "SELECT * FROM Authors WHERE Author = ?"
      ' // Create the parameter
      pParameter = pCommand.CreateParameter("Name", %adChar, %adParamInput, 255, "Bard, Dick")
      ' // Add the parameter to the collection
      pParameters = pCommand.Parameters
      pParameters.Append pParameter
      ' // Create the recordset by executing the command string
      pRecordset = pCommand.Execute
      ' // Display the results
      ShowActiveCommand pConnection, pRecordset
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF ISOBJECT(pRecordset) THEN
         IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      END IF
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example illustrates the use of the ActualSize property.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_ActualSize.bas
' Contents: ADO example
' This example uses the ActualSize and DefinedSize properties to display the defined size
' and actual size of a field.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL pFields AS ADOFields
   LOCAL pField AS ADOField
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Get a reference to the Fields collection
      pFields = pRecordset.Fields
      DO
         ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the Publisher's name
         pField = pFields.Item("Name")
         vRes = pField.Value
         PRINT "Name: " & VARIANT$$(vRes) " - ";
         PRINT "Actual size:" & STR$(pField.ActualSize) " - ";
         PRINT "Defined size:" & STR$(pField.DefinedSize)
         pField = NOTHING
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
      ' // Release the collection
      pFields = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example demonstrates the use of the AddNew method.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_AddNewRecod.bas
' Contents: ADO example
' This example uses the AddNew method to create a new record.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL bConStr AS WSTRING
   LOCAL bSqlStr AS WSTRING
   LOCAL v1 AS VARIANT
   LOCAL v2 AS VARIANT
   DIM   vFieldList(4) AS VARIANT
   DIM   vValues(4) AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      bConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open bConStr
      ' // Open the recordset
      bSqlStr = "Publishers"
      pRecordset.Open bSqlStr, pConnection, %adOpenKeyset, %adLockOptimistic, %adCmdTableDirect
      ' // Fill the array of fields
      vFieldList(0) = "PubID"
      vFieldList(1) = "Name"
      vFieldList(2) = "Company Name"
      vFieldList(3) = "Address"
      vFieldList(4) = "City"
      ' // Fill the array of values
      vValues(0) = 10000 AS LONG
      vValues(1) = "Wile E. Coyote"
      vValues(2) = "Warner Brothers Studios"
      vValues(3) = "4000 Warner Boulevard"
      vValues(4) = "Burbank, CA. 91522"
      ' // Store the arrays in variants
      v1 = vFieldList()
      v2 = vValues()
      ' // Add the record
      pRecordset.AddNew v1, v2
      STDOUT "Record added"
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example illustrates the use of the Attributes property.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_Attributes.bas
' Contents: ADO example
' Demonstrates the use of the Attributes and Name properties.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL pProperties AS ADOProperties
   LOCAL pProperty AS ADOProperty
   LOCAL pFields AS ADOFields
   LOCAL pField AS ADOField
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL nCount AS LONG
   LOCAL lAttr AS LONG
   LOCAL i AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Parse the Properties collection
      pProperties = pRecordset.Properties
      nCount = pProperties.Count
      FOR i = 0 TO nCount - 1
         pProperty = pProperties.Item(i)
         PRINT "Property name: " & pProperty.Name " - ";
         PRINT "Attributes: " & STR$(pProperty.Attributes)
         pProperty = NOTHING
      NEXT
      pProperties = NOTHING
      ' // Parse the Fields collection
      pFields = pRecordset.Fields
      nCount = pFields.Count
      IF nCount THEN
         PRINT
         PRINT "Nullable fields:"
         PRINT "================"
         PRINT
      END IF
      FOR i = 0 TO nCount - 1
         pField = pFields.Item(i)
         ' // Get the attributes of the field
         lAttr = pField.Attributes
         ' // Display fields that are nullable
         IF (lAttr AND %adFldIsNullable) = %adFldIsNullable THEN
            PRINT "Field name: " & pField.Name
         END IF
         pField = NOTHING
      NEXT
      pFields = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example opens a connection with the biblio.mdb database, creates a recordset and parses the result. Instead of using  AdoRecordset.Open, this example sets the properties individually. It also uses the Source property to show an alternate way to set the source for the recordset.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_ConnectionString.bas
' Contents: ADO example
' Demonstrates the use of the ConnectionString, ActiveConnection and Source properties.
' Opens a connection with the biblio.mdb database, creates a recordset and parses the result.
' Instead of using  ADORecordset.Open, this example sets the properties individually.
' It also uses the Source property to show an alternate way to set the source for the recordset.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT
   LOCAL vOpt AS VARIANT

   vOpt = ERROR %DISP_E_PARAMNOTFOUND

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Set the connection string
      pConnection.ConnectionString = Constr
      ' // Open the database
      pConnection.Open
      ' // Set the recordset's active connection
      pRecordset.putref_ActiveConnection = pConnection
      ' // Set the cursor location
      pRecordset.CursorLocation = %adUseClient
      ' *** The cursor type and lock type can't be set individually
      ' *** when using direct interface calls because the call to the
      ' *** Open method will reset them to 0.
      ' // Set the cursor type
'      pRecordset.CursorType = %adOpenKeyset
      ' // Set the lock type
'      pRecordset.LockType = %adLockOptimistic
      ' // Set the source for the recordset
      SqlStr = "SELECT * FROM Authors ORDER BY Author"
      pRecordset.Source = SqlStr
      ' // Open the recordset
      pRecordset.Open vOpt, vOpt, %adOpenKeyset, %adLockOptimistic
      DO
        ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example illustrates the use of the DefinedSize property.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_DefinedSize.bas
' Contents: ADO example
' This example uses the ActualSize and DefinedSize properties to display the defined size
' and actual size of a field.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL pFields AS ADOFields
   LOCAL pField AS ADOField
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Get a reference to the Fields collection
      pFields = pRecordset.Fields
      DO
         ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the Publisher's name
         pField = pFields.Item("Name")
         vRes = pField.Value
         PRINT "Name: " & VARIANT$$(vRes) " - ";
         PRINT "Actual size:" & STR$(pField.ActualSize) " - ";
         PRINT "Defined size:" & STR$(pField.DefinedSize)
         pField = NOTHING
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
      ' // Release the collection
      pFields = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example demonstrates the use of the Delete method.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_DeleteRecord.bas
' Contents: ADO example
' This example uses the Delete method to remove a specified record from a Recordset.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL bConStr AS WSTRING
   LOCAL bSqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      bConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open bConStr
      ' // Retrieve the record to update
      bSqlStr = "SELECT * FROM Publishers WHERE PubID=10000"
      pRecordset.Open bSqlStr, pConnection, %adOpenKeyset, %adLockOptimistic, %adCmdText
      vRes = pRecordset.Collect("PubID")
      IF VARIANT#(vRes) = 10000 THEN
         pRecordset.Delete %adAffectCurrent
         STDOUT "Record deleted"
      ELSE
         STDOUT "Record not found"
      END IF
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

We can create a disconnected recordset by setting its ActiveConnection property to NOTHING.

One of the primary requisites for a recordset to become a disconnected recordset is that it should use client side cursors. That is, the CursorLocation should be initialized to adUseClient.

The following example demonstrates how to do it using PowerBASIC:

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_DisconnectedRecordset.bas
' Contents: ADO example
' Disconnected recordset example.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the cursor location
      pRecordset.CursorLocation = %adUseClient
      ' // Open the recordset
      SqlStr = "SELECT * FROM Authors"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Disconnect the recordset by setting is active connection to Nothing
      pRecordset.putref_ActiveConnection = NOTHING
      ' // Close and release the connection
      pConnection.Close
      pConnection = NOTHING
      DO
         ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF ISOBJECT(pConnection) THEN
         IF pConnection.State = %adStateOpen THEN pConnection.Close
      END IF
   END TRY

   WAITKEY$

END FUNCTION


[José Roca]

 
This example demonstrates how to subscribe and unsubscribe to the Connection and Recordset events. If you want ADO to ignore an event, set adStatus = %adStatusUnwantedEvent.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_Events.bas
' Contents: ADO example
' Opens a connection, creates a recordset and parses the result.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT
   LOCAL pADOConnectionEvents AS ADOConnectionEventsImpl
   LOCAL pADORecordsetEvents AS ADORecordsetEventsImpl

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   ' // Connect events
   pADOConnectionEvents = CLASS "CADOConnectionEvents"
   EVENTS FROM pConnection CALL pADOConnectionEvents
   pADORecordsetEvents = CLASS "CADORecordsetEvents"
   EVENTS FROM pRecordset CALL pADORecordsetEvents

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT TOP 20 * FROM Authors ORDER BY Author"
      pRecordset.Open SqlStr, pConnection, %adOpenKeyset, %adLockOptimistic, %adCmdText
      DO
         ' // While not at the end of the recordset...
         IF ISTRUE pRecordset.EOF() THEN EXIT DO
         ' // Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Disconnect events
   IF ISOBJECT(pADOConnectionEvents) THEN EVENTS END pADOConnectionEvents
   IF ISOBJECT(pADORecordsetEvents) THEN EVENTS END pADORecordsetEvents

   WAITKEY$

END FUNCTION
' ========================================================================================

' ########################################################################################
' Class CConnectionEvents
' Interface name = ConnectionEvents
' IID = {00000400-0000-0010-8000-00AA006D2EA4}
' Attributes = 4096 [&H1000] [Dispatchable]
' Code generated by the TypeLib Browser 4.0.8.0 (c) 2008 by José Roca
' Date: 07 ago 2008   Time: 06:14:03
' ########################################################################################

CLASS CADOConnectionEvents GUID$("{BD67A17B-4C2B-4E02-A185-252353E7981E}") AS EVENT

INTERFACE ADOConnectionEventsImpl GUID$("{00000400-0000-0010-8000-00AA006D2EA4}") AS EVENT

  INHERIT IDispatch

   ' =====================================================================================
   METHOD InfoMessage <0> ( _
     BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD BeginTransComplete <1> ( _
     BYVAL TransactionLevel AS LONG _                   ' __in long TransactionLevel
   , BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD CommitTransComplete <3> ( _
     BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD RollbackTransComplete <2> ( _
     BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD WillExecute <4> ( _
     BYREF Source AS WSTRING _                          ' __inout BSTR *Source
   , BYREF CursorType AS LONG _                         ' __inout CursorTypeEnum *CursorType
   , BYREF LockType AS LONG _                           ' __inout LockTypeEnum *LockType
   , BYREF Options AS LONG _                            ' __inout long *Options
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pCommand AS ADOCommand _                     ' __in _Command *pCommand
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD ExecuteComplete <5> ( _
     BYVAL RecordsAffected AS LONG _                    ' __in long RecordsAffected
   , BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pCommand AS ADOCommand _                     ' __in _Command *pCommand
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD WillConnect <6> ( _
     BYREF ConnectionString AS WSTRING _                ' __inout BSTR *ConnectionString
   , BYREF UserID AS WSTRING _                          ' __inout BSTR *UserID
   , BYREF Password AS WSTRING _                        ' __inout BSTR *Password
   , BYREF Options AS LONG _                            ' __inout long *Options
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     ? ConnectionString
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD ConnectComplete <7> ( _
     BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD Disconnect <8> ( _
     BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pConnection AS ADOConnection _               ' __in _Connection *pConnection
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

END INTERFACE

END CLASS
' ========================================================================================

' ########################################################################################
' Class CRecordsetEvents
' Interface name = RecordsetEvents
' IID = {00000266-0000-0010-8000-00AA006D2EA4}
' Attributes = 4096 [&H1000] [Dispatchable]
' Code generated by the TypeLib Browser 4.0.8.0 (c) 2008 by José Roca
' Date: 07 ago 2008   Time: 06:19:17
' ########################################################################################

CLASS CADORecordsetEvents GUID$("{7D3FC1E4-D47D-49FC-9042-970A342FAFFE}") AS EVENT

INTERFACE ADORecordsetEventsImpl GUID$("{00000266-0000-0010-8000-00AA006D2EA4}") AS EVENT

  INHERIT IDispatch

   ' =====================================================================================
   METHOD WillChangeField <9> ( _
     BYVAL cFields AS LONG _                            ' __in long cFields
   , BYVAL Fields AS VARIANT _                          ' __in VARIANT Fields
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD FieldChangeComplete <10> ( _
     BYVAL cFields AS LONG _                            ' __in long cFields
   , BYVAL Fields AS VARIANT _                          ' __in VARIANT Fields
   , BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD WillChangeRecord <11> ( _
     BYVAL adReason AS LONG _                           ' __in EventReasonEnum adReason
   , BYVAL cRecords AS LONG _                           ' __in long cRecords
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD RecordChangeComplete <12> ( _
     BYVAL adReason AS LONG _                           ' __in EventReasonEnum adReason
   , BYVAL cRecords AS LONG _                           ' __in long cRecords
   , BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD WillChangeRecordset <13> ( _
     BYVAL adReason AS LONG _                           ' __in EventReasonEnum adReason
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD RecordsetChangeComplete <14> ( _
     BYVAL adReason AS LONG _                           ' __in EventReasonEnum adReason
   , BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD WillMove <15> ( _
     BYVAL adReason AS LONG _                           ' __in EventReasonEnum adReason
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD MoveComplete <16> ( _
     BYVAL adReason AS LONG _                           ' __in EventReasonEnum adReason
   , BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD EndOfRecordset <17> ( _
     BYREF fMoreData AS INTEGER _                       ' __inou VARIANT_BOOL *fMoreData
   , BYREF adStatus AS LONG _                           ' __inou EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD FetchProgress <18> ( _
     BYVAL Progress AS LONG _                           ' __in long Progress
   , BYVAL MaxProgress AS LONG _                        ' __in long MaxProgress
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   METHOD FetchComplete <19> ( _
     BYVAL pError AS ADOError _                         ' __in Error *pError
   , BYREF adStatus AS LONG _                           ' __inout EventStatusEnum *adStatus
   , BYVAL pRecordset AS ADORecordset _                 ' __in _Recordset *pRecordset
   )                                                    ' void

     ' *** Insert your code here ***
     PRINT FUNCNAME$

   END METHOD
   ' =====================================================================================

END INTERFACE

END CLASS
' ========================================================================================


[José Roca]

The following example demonstrates how to create a recordset using the Execute method of the Command object.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_Execute.bas
' Contents: ADO example
' Demonstrates the Execute method when run from a Command object.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pCommand AS ADOCommand
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Command object
   pCommand = NEWCOM "ADODB.Command"
   IF ISNOTHING(pCommand) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the active connection
      pCommand.putref_ActiveConnection = pConnection
      ' // Set the CommandText property
      SqlStr = "SELECT TOP 20 * FROM Authors ORDER BY Author"
      pCommand.CommandText = SqlStr
      ' // Create the recordset
      pRecordset = pCommand.Execute
      DO
         ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close and release the recordset
      IF ISOBJECT(pRecordset) THEN
         IF pRecordset.State = %adStateOpen THEN pRecordset.Close
         pRecordset = NOTHING
      END IF
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

The following example demonstrates the use of the Field object.

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_Field.bas
' Contents: ADO example
' Demonstrates the use of the Fields collection and the Field object
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL pFields AS ADOFields
   LOCAL pField AS ADOField
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT
   LOCAL nCount AS LONG
   LOCAL i AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT * FROM Authors ORDER BY Author"
      pRecordset.Open SqlStr, pConnection, %adOpenKeyset, %adLockOptimistic, %adCmdText
      ' // Get a reference to the Fields collection
      pFields = pRecordset.Fields
      nCount = pFields.Count
      ' // Parse the collection (Ado collections are zero based)
      FOR i = 0 TO nCount - 1
         ' // Get a reference to the Field object
         pField = pFields.Item(i)
         PRINT " ===================================================================="
         PRINT "Name: " & pField.Name
         PRINT "Type: " & STR$(pField.Type)
         PRINT "Status: " & STR$(pField.Status)
         PRINT "Actual size: " & STR$(pField.ActualSize)
         PRINT "Attibutes: " & STR$(pField.Attributes)
         PRINT "Defined size: " & STR$(pField.DefinedSize)
         vRes = pField.Value
         PRINT "Value: " & IIF$(VARIANTVT(vRes) = %VT_BSTR, VARIANT$$(vRes), STR$(VARIANT#(vRes)))
         PRINT "Precision: " & STR$(pField.Precision)
         PRINT "Numeric scale: " & STR$(pField.NumericScale)
         vRes = pField.OriginalValue
         PRINT "Original value: " & IIF$(VARIANTVT(vRes) = %VT_BSTR, VARIANT$$(vRes), STR$(VARIANT#(vRes)))
'         vRes = pField.UnderlyingValue
'         PRINT "Underlying value: " & IIF$(VARIANTVT(vRes) = %VT_BSTR, VARIANT$$(vRes), STR$(VARIANT#(vRes)))
         ' // Release the Field object
         pField = NOTHING
         PRINT " ===================================================================="
         WAITKEY$
      NEXT
      ' // Release the Fields collection
      pFields = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
      WAITKEY$
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

END FUNCTION
' ========================================================================================


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: ADOEX_Fields.bas
' Contents: ADO example
' Opens a connection, creates a recordset and parses the result.
' This example uses the Fields collection and the Field object instead of the Collect
' method to retrieve the information.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL pFields AS ADOFields
   LOCAL pField AS ADOField
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers ORDER BY PubID"
      pRecordset.Open SqlStr, pConnection, %adOpenKeyset, %adLockOptimistic, %adCmdText
      ' // Get a reference to the Fields collection
      pFields = pRecordset.Fields
      ' // Parse the recordset
      DO
         ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         pField = pFields.Item("PubID")
         vRes = pField.Value
         PRINT VARIANT$$(vRes)" ";
         pField = NOTHING
         pField = pFields.Item("Name")
         vRes = pField.Value
         PRINT VARIANT$$(vRes)" ";
         pField = NOTHING
         pField = pFields.Item("Company Name")
         vRes = pField.Value
         PRINT VARIANT$$(vRes)
         pField = NOTHING
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
      ' // Release the collection
      pFields = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================