Tip>Designer

Using ADO to write back data to an ODBC data source

 

Article #:

0045

Contributor:

Ricky Burrell

GF version:

4.0

Applies to:

ADO links to data sources

Last updated:

January 13, 2003

Description

You can use GhostFill script to establish an ADO link to an ODBC data source and edit the data in the data source.

Explanation

The following example shows how you can establish an ADO connection to a data source and then manipulate the data from FillPoints.

 

Use the CreateObject task to establish a connection to a data source, then open the connection:

%[adStateOpen = 1] %[adOpenDynamic = 2] %[adLockOptimistic = 3] %[adCmdText = 1]

%[Connection = CreateObject("ADODB.Connection")]

%[ResultSet = CreateObject("ADODB.Recordset")]

%[Connection.ConnectionString = "Data source = GhostFill ODBC Sample;"]

%[Connection.Open()]

 

Having established the connection, you can now manipulate the data in the data source.

Click here to view an example of how to iterate through the records and append data to a field.

  1. Select and open the table called "Contacts":

%[SQL = 'Select * from Contacts']

%[ResultSet.Open(SQL, Connection, adOpenDynamic, adLockOptimistic, adCmdText)]

  1. Start the loop:

%[RepeatBlockWhile(ResultSet.EOF = False)]

  1. Append "xyz" to the field ClientName:

%[ResultSet.Fields('ClientName').Value = ResultSet.Fields('ClientName').Value & 'xyz']

  1. Update the record:

%[ResultSet.Update]

  1. Display the resultant value of this field in the document:

%[ResultSet.Fields('ClientName').Value]

  1. Move to the next record:

%[ResultSet.MoveNext()]

%[EndBlock()]

  1. Close the Result Set:

%[ResultSet.Close()]

 

Click here to view an example of how to filter to a single record, and then write back data to that record.

  1. Filter to the record containing "Mr Joe Smith" in the field ClientName:

%[SQL = 'Select * from contacts where ClientName LIKE "%Mr Joe Smith%"' ]

%[ResultSet.Open(SQL, Connection, adOpenDynamic, adLockOptimistic, adCmdText)]

  1. Now set the field Telephone to have the value "XYZ":

%[ResultSet.Fields('Telephone').Value = "XYZ"]

  1. Update the record:

%[ResultSet.Update]

  1. Display the resultant value of this field in the document:

%[ResultSet.Fields('Telephone').Value]

  1. Close the connection:

%[Connection.Close()]