SalesLogix Basics 4 – ADO Recordsets in SalesLogix LAN

By using ADO, you have a powerful resource for reading and storing data in SalesLogix.  In this basics article, I’ll outline the main components involved in constructing and using an ADO recordset in general, and specifically with SalesLogix .  If you’ve never worked with ADO before, this simple example will give you what you need to incorporate ADO into your own customizations.

 Example:

 

Set rs = CreateObject(“ADODB.Recordset”)
With rs
         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open “select * from c_test where c_testid = ‘” & labelID.Caption & “‘”

         If .RecordCount = 0 Then
            .AddNew
            .Fields(“c_testid”).Value = Application.BasicFunctions.GetIDFor(“c_test”)
            .Fields(“accountid”).Value = CurrentID
            .Fields(“createuser”).Value = Application.BasicFunctions.CurrentUserID
            .Fields(“createdate”).Value = Now
         End If

         .Fields(“modifyuser”).Value = Application.BasicFunctions.CurrentUserID
         .Fields(“modifydate”).Value = Now
         .Fields(“field1”).Value = edit1.Text
         .Fields(“field2”).Value = edit2.Text
         .Update

         .Close
End With
Set rs = Nothing

 

Connection

The basic
steps for using an ADO Recordset are:

1. Create and ADO connection to the database
2. Open the database connection
3. Create an ADO recordset
4. Set connection options for the
   recordset.
5. Open the recordset
6. Extract/Modify the data you
   need from the recordset
7. Update the recordset (if
   modified)
8. Close the recordset
9. Close the connection.

 

*These
first two steps are handled by SalesLogix so we don’t need to worry about them
within the SLX Environment.  I’m
including them here just to list all the steps in working with a ADO Recordset

 

 

Create an ADO connection to the database

 

set conn=Server.CreateObject(“ADODB.Connection”)

conn.Provider=”Microsoft.Jet.OLEDB.4.0″

 

In this
code, we have created a connection object and are using the JET OLEDB provider
to connect to an Access database

 

Open the database connection

 

conn.Open
“c:/webdata/northwind.mdb”

 

This code
uses the connection object to open the access database, “Northwind”.

 

Create an ADO recordset

 

Set objRS =
CreateObject(“ADODB.Recordset”)

Set objRS.ActiveConnection =
Applcation.GetNewConnection

 

Here we
have created the basic ADO
recordset object, and set it to use a connection from the SLX connection pool.

 

Set connection options for the
recordset

 

Cursors are
used to control a few different things, namely record navigation, visibility of
changes in the database, and updatability of data.

 

Cursor
Location:

 

The cursor
location can be set to use either the Server or Client cursor service.  Basically, this controls weather or not
processing is done on the server or on the individual client machine.  Server-side cursors are used by default.

 

Enumerated
values:

• adUseServer (default)
• adUseClient

 

Cursor
Type:

 

The cursor
type controls how you can cycle through the records returned in the recordset
as well as visibility of any changes you make.

 

Enumerated
values:

• adOpenForwardOnly (Default)
• Uses a forward-only
  cursor.  Identical to a static
  cursor, except you can only scroll forward through records.  This can improve performance when you
  need to make only one pass through a recordset.
• adOpenKeySet
• like a dynamic cursor, except
  that you can’t see records that other users add, although records that
  other users delete are inaccessible from your Recordset.  Data changes from other users are
  visible.
• adOpenDynamic
• Additions, changes and
  deletions by other users are visible, and all types of movement through
  the Recordset are allowed.
• adOpenStatic
• Additions, Edits and Deletes
  from other users are not visible.

 

Lock
Type:

 

The lock
type specifies the type of locking when editing values in the recordset.

 

Enumerated
Values:

• adLockReadOnly
• Recordset is read-only
• adLockPessimistic
• The provider locks records
  immediately after editing.
• adLockOptimistic
• Records are locked only when
  Update is called
• adLockBatchOptimistic
• Records are locked when
  updated is called.  Required for
  batch updates.

 

 

objRS.CursorLocation = adUseClient

objRS.CursorType = adOpenStatic

objRS.LockType = adLockOptimistic

 

Open the recordset

 

Here we are
opening the recordset, using the SQL query provided.

 

objRS.Open “Select * from TestTable
where 0=1”

 

 

Extract/Modify data from the
recordset

 

Values are
returned using (in this case set to a variable strValue):

strValue = objRS.Fields(“FieldName”).Value

 

Values can
also be set by using: 

 

objRS.Fields(“FieldName”).Value =
“New Value”

 

Update the recordset

 

objRS.Update or objRS.UpdateBatch

 

The update
command applies changes made to the recordset. 
Until this is called, the changes are not permanent.  Closing the recordset before calling update
would cause the changes to be lost.

 

Close the recordset

 

objRS.Close

Set objRS.Nothing

 

This
statement closes the recordset when you are done.  Setting the object = “Nothing”, aka “Destroying”
the object, frees up the resources used by the recordset object.

Close the connection

 

This step
is not needed when using recordsets in SalesLogix.  The connections would be closed when closing
the database.

 

 

 

Batch Updates Example:

 

By using the locktype of adLockBatchOptimistic and UpdateBatch, you can loop through a number of updates, and commit them all at once.

 

Set rs = CreateObject(“ADODB.Recordset”)

With rs

         Set .ActiveConnection = Application.GetNewConnection

         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockBatchOptimistic

         .Open “select * from c_test where Value = ‘” & labelID.Caption & “‘”

          While not (.Bof or .Eof)

              .Fields(“modifyuser”).Value = Application.BasicFunctions.CurrentUserID
              .Fields(“modifydate”).Value = Now
              .Fields(“field1”).Value = edit1.Text
              .Fields(“field2”).Value = edit2.Text

              .MoveNext

         Wend

         .UpdateBatch
         .Close

End With

Set rs = Nothing

I hope you find this example helpful.  Thanks for reading!  [:)]