Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

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.



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
            .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

End With
Set rs = Nothing



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")



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



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




End With

Set rs = Nothing

I hope you find this example helpful.  Thanks for reading!  Smile



What's This?
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.

Related Content
   Can I Bind an Attachment to a Specific Activity in Infor CRM version 7.54?
Recently I had a user ask if they could bind an attachment to a specific Activity within Infor CRM??
Posted on Jul 07, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (formerly SalesLogix) Administrator Locked Out
I thought I had seen all the issues affecting the datalink but this was a new one. We could not login t
Posted on Jun 18, 2015 by Mark Duthoy to SalesLogix Support
   How do I remove users from my Infor CRM system (formerly SalesLogix)?
I had an Administrator ask me for the steps they needed to take in order to remove a user from the Infor
Posted on May 08, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (Formerly Saleslogix) v8.1 update 03 and 05- Uncaught Error Scheduling Contact Processes
 In the Infor CRM (formerly Saleslogix) web client, under the Contact area you have the ability to s
Posted on May 07, 2015 by Kris Halsrud to Kris Halsrud's Blog
   Better Code Organization for Infor CRM (Saleslogix) Mobile Customizations
When customizing the Infor CRM (Saleslogix) Mobile client, it's common for the ApplicationModule to g
Posted on May 05, 2015 by Ryan Farley to Ryan Farley's Blog


The Customization Blog said:

I wanted to take this blog post to briefly outline the different types of Forms available to you in the SalesLogix LAN client, mostly for anyone who is new to creating views and customizing SalesLogix.

March 25, 2009 7:00 AM

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register