IT-Consultant: José Roca (PBWIN 10+/PBCC 6+) (Archive only) > COM Programming

ADO/ADOX Examples

<< < (2/16) > >>

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example demonstrates the use of the AddNew method.


--- Code: ---' ########################################################################################
' 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
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example illustrates the use of the Attributes property.


--- Code: ---' ########################################################################################
' 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
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
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: ---' ########################################################################################
' 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
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example illustrates the use of the DefinedSize property.


--- Code: ---' ########################################################################################
' 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
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example demonstrates the use of the Delete method.


--- Code: ---' ########################################################################################
' 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
' ========================================================================================

--- End code ---

Navigation

[0] Message Index

[#] Next page

[*] Previous page