Example I - Using Scripting

In this example, database components are not used, as using them would not show all the possibilities of scripting.

1. Initialize variables and values. Option Explicit requires that all variable names be defined (with the Dim statement). Const is similar to a variable, except that its value cannot be changed at runtime:

   Option Explicit

   Const adCmdText = 1

   Const adStateOpen = 1

   Dim cnVizrt

   Dim cmdQuery

   Dim rsVizrtStory

2. Create a database connection using the Connection object:

   Set cnVizrt = CreateObject("ADODB.Connection")

3. A minimum set of connection properties can be set. In this case the Provider and Data Source. In this case User ID and Password is also provided. If none is required the sentence can be removed:

   cnVizrt.ConnectionString = _

   "Provider=Microsoft.Jet.OLEDB.4.0; " & _

   "User ID= SomeUser; Password; SomePassword; " & _

   "Data Source=C:\test.mdb"

4. Open the database:

   cnVizrt.Open

5. Create a query string using the Command object:

   Set cmdQuery = CreateObject("ADODB.Command")

   With cmdQuery

   Set .ActiveConnection = cnVizrt

   .CommandText = "SELECT id, headline, source " & _

   "FROM dbTable " & _

   "WHERE source = 'Vizrt'"

   .CommandType = adCmdText

   Set rsVizrtStory = .Execute

   End With

6. Test to see if the database connection was successful:

   If rsVizrtStory.State = adStateOpen Then

   MsgBox("Recordset opened successfully.")

   End If

7. Clear redundant data from the listboxes:

   TWUniListBox1.Clear

   TWUniListBox2.Clear

   TWUniListBox3.Clear

8. Add data from the database to text fields:

   Do While Not rsVizrtStory.EOF

   TWUniListBox1.Items.Add(rsVizrtStory.Fields("id"))

   TWUniListBox2.Items.Add(rsVizrtStory.Fields("headline"))

   TWUniListBox3.Items.Add(rsVizrtStory.Fields("source"))

   rsVizrtStory.MoveNext

   Loop

9. Delete all objects from memory that are no longer required:

   rsVizrtStory.Close

   Set rsVizrtStory = Nothing

   Set cmdQuery = Nothing

   cnVizrt.Close

   Set cnVizrt = Nothing

Example II - Using Components

Setting up a database connection is also possible using database components. A simple setup can be described as follows:

1. Add an ADO Connection component to the template and set the following properties:

   • ConnectionString: Information required to connect the ADO Query component;

     Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb;

   • Provider: This property is auto generated based on the connection string property.

2. Add an ADO Query component to the template and set the following properties:

   • Connection: Select the ADO Connection name from the drop-list.

   • SQL: Click the browse button and enter the query string. For example:

     SELECT id, headline, source FROM dbTable WHERE source = 'Vizrt';

3. Check the Connected property in the Object Inspector for the ADO Connection component. A Database login window appears asking for User Name and Password. If one is not set, click OK.

4. Check the Active property in the Object Inspector for the ADO Query component.

   

5. Open the ADO Query context menu by right clicking the component icon on the template editor. Click the Fields Editor ... menu option.

   

6. Select fields by clicking the arrow and plus button in the Fields Editor window. If the connection is not active, a dialog appears asking for User Name and Password.

   

7. Add Unicode labels and listboxes to the template.

8. The following script will list the result from the ADO Query component in the listboxes:

   Option Explicit

   Dim index

   ADOConnection1.Connected = True

   ADOQuery1.Active = True

9. Test to see if the database connection was successful:

   If ADOQuery1.State = 1 Then

   MsgBox("Recordset opened successfully.")

   MsgBox(ADOQuery1.RecordCount)

   End If

10. Test to see how many records were found based on the SQL statement:

    MsgBox(ADOQuery1.RecordCount)

11. Clear listboxes:

    TWUniListBox1.Clear

    TWUniListBox2.Clear

    TWUniListBox3.Clear

12. Before iterating a record set, it's recommended to reset the cursor in order to start from the top when running the script several times:

    ADOQuery1.First

13. The For statement iterates according to the RecordCount -1 adding data to the listboxes. Since the loop starts at 0, the RecordCount method must be set to -1. This prevents the loop from giving a copy of the last record:

    For index = 0 To ADOQuery1.RecordCount -1

    TWUniListBox1.UTF8Items.Add(ADOQuery1id1.Text)

    TWUniListBox2.UTF8Items.Add(ADOQuery1headline1.Text)

    TWUniListBox3.UTF8Items.Add(ADOQuery1source1.Text)

    ADOQuery1.Next

    Next

    Note: The field names (for example ADOQuery1headline1) are auto-generated names. All database fields, selected from the Fields Editor, are added as template components.

14. Disconnect the database connection and deactivate the query component:

    ADOConnection1.Connected = False

    ADOQuery1.Active = False

    Although the ADO Query component can establish a connection directly using its ConnectionString property, it is often desirable to use an ADO Connection to share a single connection among several ADO components. This can reduce resource consumption, and allows transactions to be created that span multiple data sets.

Notable Properties

• Connected: Specifies whether the connection is active.

• ConnectionString: Specifies the connection information for the data store.

• DefaultDatabase: Indicates the database the ADO connection uses by default.

• LoginPrompt: Specifies whether a login dialog appears immediately before opening a new connection.

• Provider: Specifies the provider for the ADO connection. Entering a valid ConnectionString usually generates this input.

Notable Properties

• Active: When checked, sets the query component active. If the SQL query fails it will not be set to active.

• Connection: Use Connection to specify an ADO connection component to connect to a data store.

• SQL: SQL query parameter.

Notable Properties

• Active: When selected, the component is active.

• Connection: Specifies the ADO connection component to use.

• Name: Specifies the name of the control as referenced in code.

• ReadOnly: When checked ,the database connection is set to be read only.

• RecordCount: Indicates the total number of records in the record set.

• Recordset: Provides direct access to the ADO record set object.

Notable Properties

• Active: When checked, it sets the query component active. If the SQL query fails, it is not possible to set it active.

• Connection: Use Connection to specify an ADO Connection object to use to connect to a data store.

• ConnectionString: Set ConnectionString to specify the information needed to connect the ADO Connection component to the data store.

• DataSource: Links the edit control to the data set that contains the field it represents.