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.

 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!  Smile

 

 

What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Retrieving Items from a Picklist via Javascript
Saleslogix is very light on how to use client side processes to do things in the Saleslogix web client.&n
Posted on Aug 29, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix Xbar update coming soon
Scheduled to be released 8/29/2014 Features in Update •Installation improvements •Enterp
Posted on Aug 21, 2014 by Scott Weber to SalesLogix Product Blog
 
   How do you add a parameter for File Attachment Size and Timeout in Saleslogix 8.1?
Question: Is it possible to set a parameter for file attachment size and timeout in Salelogix? If
Posted on Aug 07, 2014 by Dale Richter to SalesLogix Questions & Answers
 
   Fixing Duplicate Saleslogix FieldIndex Values in the SECTABLEDEFS
I had to fix a client's SECTABLEDEFS table that contained duplicate FieldIndex values.  FieldInd
Posted on Aug 07, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix XBar Installation
When installing the new XBar application I found the instructions a little vague so I thought I would pas
Posted on Jul 18, 2014 by Mark Duthoy to SalesLogix Support
 
Comments

 

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

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 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