Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Wednesday, December 11, 2019 
 
How to update Data with Updateable RecordSets  
Description:  In SalesLogix 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 SalesLogix data.

Category:  Architect How To Articles
Author:  Kris Halsrud
Submitted:  10/27/2005
   
Stats: 
Article has been read 26154 times

Rating: - 5.0 out of 5 by 10 users
 

fiogf49gjkf0d
How to update Data with Updateable RecordSets

In SalesLogix 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 SalesLogix data.


Using SQL Statements

Using SQL statements in SalesLogix 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

Updating or inserting data this way, while does work, introduces some major disadvantages:
  1. 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.
  2. The SQL statement is passed as a string with limitations in the provider at handling items like dates being in ISO date format.
  3. It is hard to read/modify a long SQL statement if you must add or remove certain fields at a later time.
For this reason it is much better to take advantage of SalesLogix 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

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

  1. To begin with we need to create a recordset object

    Set rs = CreateObject("ADODB.Recordset")

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

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

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

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

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

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

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

        .Update

  8. 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
(SalesLogix Business Partner)
Customer FX Corporation

fiogf49gjkf0d


View online profile for Kris Halsrud
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to update Data with Updateable RecordSets
Posted: 10/28/2005 10:13:45 AM
fiogf49gjkf0d
Great article Kris. I've had this topic on my list of articles to write for quite some time now, good to see I won't have to anymore. This is an article that has been needed here for some time now.

Thanks!
-Ryan
 
Stephen Redmond



Re: How to update Data with Updateable RecordSets
Posted: 10/30/2005 5:00:10 PM
fiogf49gjkf0d
I absolutely agree with this line of thinking about inserting data using the recordset object rather than using a generated SQL insert statement. I agree mainly for the same reasons as I prefered the DBInsert-DBSetValue-DBPost in Legacy code, it makes the code much more readable.

There is one exception where you might consider using a derived insert query and that might be when using a prepared query with a Command object with parameters to insert a large amount of data (from an import for example). It is probable that using the prepared query will improve the speed of execution.

Other than that, always go with the recordset method. Anyone who argues that:

rs.Fields("MODIFYDATE").Value = Now

Is less readable than:

"UPDATE TABLE SET MODIFYDATE = '" & Application.BasicFunctions.DateToISO(Now) & "' "

is barking!


Stephen


 
Mike Boysen



Re: How to update Data with Updateable RecordSets
Posted: 11/2/2005 3:22:28 PM
fiogf49gjkf0d
Kris,

You should have done a survey for the worst insert statement. Your's actually looks pretty nice. I've run across a few nightmare scenarios worth of The Daily WTF.

Good article. I wish more people coded this way.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to update Data with Updateable RecordSets
Posted: 11/2/2005 3:28:21 PM
fiogf49gjkf0d
Mike.

Hehe. That survey would produce some doozies! Don't you just hate to work with some SQL strings and have to go back to modify things later!

But you want to know what is really funny? The SQL statement Kris used in the article is from one of my first articles I posted to this site! (See http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=9). Hehe. Live and learn. I cringe at the sight of a SQL statement like that now. Pretty funny.

-Ryan
 
Mike Boysen



Re: How to update Data with Updateable RecordSets
Posted: 11/2/2005 3:45:34 PM
fiogf49gjkf0d
ROFL!!!!
 
RJ Samp

slxdeveloper.com Forum Top 10 Poster!

Re: How to update Data with Updateable RecordSets
Posted: 8/16/2007 10:26:18 AM
fiogf49gjkf0d
Now however we're finding out that for some reason in SLX 6.2 - 7.20 Gold (as this is being written) that there's a Provider side memory leak when doing ADO recordset inserts and updates......stuff is not getting disposed of. Their appears to be both a DELPHI side, and Provider (written in C++) side issue with this.....it only shows up after Thousands of inserts/updates/ WHERE clauses......or about a day of Inserting New Contact and Accounts manually.

To get around this, we've started coding BIG stuff in Command Parameterized ADO SQL....not ADO field based SQL.

This is in C# .NET, but it's NOT that different for VB Script (in fact this code was adapted to C# .NET from a VB .NET project):
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
static void CreateContact(OleDbConnection Conn, string AccountID,string AccountName,string AddressID, long iContactNo)
{
string isemployee;
string emp_accountID;
string TheRole;
sQuery = "INSERT INTO sysdba.CONTACT (CONTACTID, ACCOUNTID, ACCOUNT, LASTNAME, MIDDLENAME, FIRSTNAME, ACCOUNTMANAGERID, IMPORTSOURCE, ADDRESSID, SHIPPINGID, EMAIL, WORKPHONE, HOMEPHONE, MOBILE, LASTNAME_UC, STATUS, TYPE, SECCODEID, CONTACTNO) " + "VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
OleDbCommand cmd = new OleDbCommand(sQuery, Conn);
{
cmd.Parameters.AddWithValue("CONTACTID", sContactID);
cmd.Parameters.AddWithValue("ACCOUNTID", sAccountID);
cmd.Parameters.AddWithValue("ACCOUNT", AccountName);
cmd.Parameters.AddWithValue("LASTNAME", strLastName);
cmd.Parameters.AddWithValue("MIDDLENAME", strMiddleName);
cmd.Parameters.AddWithValue("FIRSTNAME", strFirstName);
cmd.Parameters.AddWithValue("ACCOUNTMANAGERID", slxUSERID);
cmd.Parameters.AddWithValue("IMPORTSOURCE", gImportSource);
cmd.Parameters.AddWithValue("ADDRESSID", AddressID);
cmd.Parameters.AddWithValue("SHIPPINGID", AddressID);
cmd.Parameters.AddWithValue("EMAIL", strEMail);
cmd.Parameters.AddWithValue("WORKPHONE", strPhone1);
cmd.Parameters.AddWithValue("HOMEPHONE", strPhone1);
cmd.Parameters.AddWithValue("FAX", strPhone2);
cmd.Parameters.AddWithValue("LASTNAME_UC", strLastName.ToUpper());
cmd.Parameters.AddWithValue("STATUS", ContactStatus);
cmd.Parameters.AddWithValue("TYPE", ContactType);
cmd.Parameters.AddWithValue("SECCODEID", slxOwnerID);
cmd.Parameters.AddWithValue("CONTACTNO", iContactNo);
cmd.ExecuteNonQuery();
}
// -------------------------------------------------------
// Create the Contact2 record
emp_accountID = "";
if (strCompanyName != "")
{
TheRole = "Broker";
isemployee = "T";
sCompany = strCompanyName.Replace("'", "''");
emp_accountID = GetField("ACCOUNTID", " ACCOUNT", "ACCOUNT = '" + sCompany + "' ", Conn);
}
else
{
TheRole = "";
isemployee = "F";
}
sQuery = "INSERT INTO ASI_CONTACT_EXT (CONTACTID, CLIENT_CLASSIFICATION, " + "FAMILY_ACCOUNTID, INV_INDUSTRY_EMPLOYEE, EMPLOYER, EMPLOYER_ACCOUNTID, FIRM_ROLE, FIRM_TYPE) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
using(OleDbCommand cmd2 = new OleDbCommand(sQuery,Conn))
{
cmd2.Parameters.AddWithValue("CONTACTID", sContactID);
cmd2.Parameters.AddWithValue("CLIENT_CLASSIFICATION", ContactType);
cmd2.Parameters.AddWithValue("FAMILY_ACCOUNTID", sAccountID);
cmd2.Parameters.AddWithValue("INV_INDUSTRY_EMPLOYEE", isemployee);
cmd2.Parameters.AddWithValue("EMPLOYER", strCompanyName);
cmd2.Parameters.AddWithValue("EMPLOYER_ACCOUNTID", emp_accountID);
cmd2.Parameters.AddWithValue("FIRM_ROLE", TheRole);
cmd2.Parameters.AddWithValue("FIRM_TYPE", TheRole);
cmd2.ExecuteNonQuery();
}
// -------------------------------------------------------
// Create the Contact NO record
string CNID;
CNID = CreateIDFor("CONTACT_NO", Conn);
sQuery = "INSERT INTO CONTACT_NO (CONTACT_NOID, CONTACTID, CONTACT_NO, EMAIL, POSTALCODE, PHONENUMBER, IMPORTDATE, DATEENTERED, IMPORT_SOURCE )" + "VALUES (?, ?, ?, ?,?, ?, ?, ?,?)";
using (OleDbCommand cmd3 = new OleDbCommand(sQuery, Conn))
{
cmd3.Parameters.AddWithValue("CONTACT_NOID", CNID);
cmd3.Parameters.AddWithValue("CONTACTID", sContactID);
cmd3.Parameters.AddWithValue("CONTACT_NO", intCustomerID);
cmd3.Parameters.AddWithValue("EMAIL", strEMail);
cmd3.Parameters.AddWithValue("POSTALCODE", strZipCode);
cmd3.Parameters.AddWithValue("PHONENUMBER", strPhone1);
cmd3.Parameters.AddWithValue("IMPORTDATE", strCreateDate);
cmd3.Parameters.AddWithValue("DATEENTERED", strDateEntered);
cmd3.Parameters.AddWithValue("IMPORT_SOURCE", gImportSource);
cmd3.ExecuteNonQuery();
}
// -------------------------------------------------------
// Create the Inv Firm record
string IFID;
IFID = CreateIDFor("INVESTMENT_FIRMS", Conn);
Broker_AccountID = "";
if (strBrokerFirmName.Length != 0)
{
Broker_AccountID = GetField("COALESCE(ACCOUNTID,'')", "ACCOUNT ", " ACCOUNT = '" + strBrokerFirmName.Replace("'", "''") + "' ", Conn);
}
sQuery = "INSERT INTO INVESTMENT_FIRMS (INVESTMENT_FIRMSID, CONTACTID, FIRM_NAME, FIRM_ACCOUNTID )" + "VALUES (?, ?, ?, ?)";
using(OleDbCommand cmd4 = new OleDbCommand(sQuery,Conn))
{
cmd4.Parameters.AddWithValue("INVESTMENT_FIRMSID", IFID);
cmd4.Parameters.AddWithValue("CONTACTID", sContactID);
cmd4.Parameters.AddWithValue("FIRM_NAME", strBrokerFirmName);
cmd4.Parameters.AddWithValue("FIRM_ACCOUNTID", Broker_AccountID);
cmd4.ExecuteNonQuery();
}
}
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2019 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 12/11/2019 6:21:22 AM