How to Update Data with Update-able Recordsets

This was an oldie but a goodie that I originally wrote for slxdeveloper.com way, way back in 2005! While most of my work is geared towards developing in the web client now, every once in a while I have to dust of the VBScript brain cells and remember out how to do these things.

In Infor CRM LAN VB scripting you have the ability to take advantage of ADO Recordsets for updating data. This is a far better route than building SQL statements to perform inserts or updates of data. This article examines using updatable Recordsets to add and update Infor CRM data.

Using SQL Statements

Using SQL statements in Infor CRM works fine in practice but always seems to create a messy looking piece of code. Consider the following code to insert a record into the HISTORY table:

    sql = "insert into history (historyid, type, accountid, accountname, "
    sql = sql & "contactid, contactname, startdate, userid, timeless, duration, "
    sql = sql & "description, notes, longnotes, "
    sql = sql & "createuser, createdate, completeduser, completeddate) "
    sql = sql & "values ('"
    sql = sql & m_historyID & "', "
    sql = sql & "'" & type & "', "
    sql = sql & "'" & GetContactValue(conId, "accountid") & "', "
    sql = sql & "'" & GetContactValue(conId, "account") & "', "
    sql = sql & "'" & conId & "', "
    sql = sql & "'" & GetContactValue(conId, "fullname") & "', "
    sql = sql & "'" & startDT & "', "
    sql = sql & "'" & userID & "', "
    sql = sql & "'T', "
    sql = sql & "'0', "
    sql = sql & "'" & Replace(desc, "'", "''") & "', "
    sql = sql & "'" & Replace(Mid(notes, 1, 254), "'", "''") & "', "
    sql = sql & "'" & Replace(notes, "'", "''") & "', "
    sql = sql & "'" & Application.BasicFunctions.CurrentUserID & "', "
    sql = sql & "'" & Now & "', "
    sql = sql & "'" & userID & "', "
    sql = sql & "'" & completedDT & "')"

    Set cn = Application.GetNewConnection
    cn.Execute sql

Set cn = Application.GetNewConnection
cn.Execute sql

Updating or inserting data this way, while it does work, introduces some major disadvantages:

  • You must build logic to determine if you need to insert or update a record. The statements then require separate formatting for each kind of statement.
  • The SQL statement is passed as a string with limitations in the provider at handling items like dates being in ISO date format.
  • It is hard to read/modify a long SQL statement if you must add or remove certain fields at a later time.
  • You have to handle things like invalid characters that will break the SQL command, like single quotes.

For this reason it is much better to take advantage of Infor CRMs capability to use ADO updatable recordsets. With a recordset, you can overcome the disadvantages of using Execute SQL commands as listed above.

Using an Updateable Recordset

Let’s take a look at how to use an updateable recordset. The following code is not intended as a standalone function or routine, instead it is just to show the concepts.

To begin with we need to create a recordset object

Set rs = CreateObject("ADODB.Recordset")

Now we work with the record set object, setting its properties accordingly.

With rs
Set .ActiveConnection = Application.GetNewConnection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic

Finally we populate the recordset with a SQL statement

.Open "select * from c_test where c_testid = '" & labelID.Caption & "'"

In this example we are building a statement based upon the value in a label on a form.

Next we test to see if the recordsets, recordcount property is 0, if it is we know that this record does not exist

If .RecordCount = 0 Then

Again, if the recordcount is 0 then we must add the record. We add the following code in to handle this.

.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

Notice that with this code, since the values are separate parameters, instead of an inline SQL statement we do not need to worry about formatting our dates into ISO format as the provider will do this for us.

Now that we have added the fields into the code that are required for every new record we can now go to the records that get updated regardless of if the record already exists.

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

Once we have listed all of the fields we want populated we can now update our recordset

.Update

After the recordset has been updated we wrap up the code with the clean up of closing the recordset, and destroying it

.Close
End With
Set rs = Nothing

Now let’s take a look at the completed code:

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

As you can see this makes reading what is being inserted much simpler. It also has build in logic for if the record is present already or not. As simple as this is this could be made even more simple and reusable by creating a class to handle this.

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!