Parameterized Queries

When you’re working with data-bound fields, the SLX Provider handles names or other data with apostrophes—like “O’Reilly”—or fields with Null values in them, without you needing to worry about it.

If you’re doing explicit string SQL Inserts or Updates, however—or using fields that could have apostrophes/nulls in them in your WHERE clauses—you’re probably used to passing the field values through Replace functions, and either testing for Nulls or kludg-ily concatenating empty strings onto the end of each field.

If you use parameterized queries instead of SQL strings, you can go back to not worrying about stuff like that.

Sage actually uses exactly that method in the OTB “System:SLX Lead Support” script, but it’s full of a lot of other unrelated code. In bare-bones terms, parameterized queries in VBScript look like this:

Dim strCommand
Dim objParams
Dim objCmd
Dim objRs

...

Set objCmd = CreateObject("ADODB.Command") 'DNL
Set objCmd.ActiveConnection = Application.GetNewConnection

objCmd.CommandType = adCmdText
objCmd.Prepared = True
strCommand = "SELECT Lastname FROM Contact " _
     & "WHERE AccountID = ? AND IsPrimary = ?"
objCmd.CommandText = strCommand

objCmd.Parameters.Refresh
Set objParams = objCmd.Parameters

objParams.Item(0).Value = strAccountID
objParams.Item(1).Value = blnIsPrimary

Set objRs = objCmd.Execute

...

Set objRs  = Nothing
Set objParams = Nothing
Set objCmd = Nothing

It works exactly the same way for Inserts and Updates, just (obviously) without the objRs recordset.

If you’re getting weird errors, the Very First Thing to check is that the number of question-marks in the command string is equal to the number of parameters you’ve added.

And if you’ve ever wondered, the “DNL” that you see all over the place in SalesLogix OTB code stands for “Do Not Localize”—i.e., for strings which are just used internally and cannot be translated into other languages without having hugely detrimental effects on the program, as opposed to UI strings which can/should be localized into the user’s preferred language.

So it’s not just some developer’s initials. 🙂

Tips and Tricks – IV

Tips and Tricks IV: The Voyage Home.

• SalesLogix performance will suffer with cursors being left open. If you are coding in SalesLogix it is very important that you close your Recordsets, setting your Recordsets to Nothing is NOT enough, you should always have code like this:

If oRS.State = 1 Then
    oRS.Close
End If
Set oRS = Nothing

• Lookup hack:

' Need to restrict the Lookup to records where
' Account.Type='Customer' and Account.Location='US'):
LookUpName = "Account:Account"
RestrictAlways = True
Lookup.LookupRestrictField = "TYPE'"
Lookup.LookupRestrictOp = "='Customer' and LOCATION="
Lookup.LookupRestrictValue = "US"

• Create a “.txt” file. Rename the file to “.udl”. When you double-click on the file it will display the standard connection designer!

And lots of good stuff from Ryan Farley’s site:

• SalesLogix Synchronization Report
• Leveraging .NET in SalesLogix
• A Guide to The .NET Extension Deployment Attributes
• Introduction to .NET in SalesLogix Version 7
• How to Debug a SalesLogix .NET Extension
• .NET SlgxApi.dll Managed Wrapper
• The ADO.NET Primer
• SalesLogix Recurring data 101 (Activity.Recurring = True)
• The SalesLogix Mail Merge SDK – An In-Depth Example
• SalesLogix Web Services Toolkit (This document demonstrates how SalesLogix developers can use .NET Web Services as a mechanism to expose an API layer to users without compromising security and allowing many platforms that support SOAP and XML to communicate with the web service.)
• SLX v7 ERD
• Creating DataGrid Columns at Runtime
• Dynamically Creating DataGrid Columns
• Dynamically Populating Radio Buttons and Retrieving the Selected Value
• Generically Setting a Form as Read-Only
• The SalesLogix CheckListBox Control
• Using Classes in VBScript (bundle)
• E-mail Address Validation (using Regular Expressions)
• Using FileSystemObject to Create a vCard File
• Connecting with the RWPassword From External Applications
• The Myths and Legends of Prepared Queries
• How to Launch a Contact Process via Script (bundle)
• How to Save & Read Images in a SalesLogix Database (bundle)
• Building a SalesLogix Login Screen (bundle)
• Integrated Logins with the SalesLogix 6.2 Provider
• How To Update Controls Across Forms
• How To Retrieve a Record Count From an ADO Recordset
• How to Create a View in a SalesLogix Database that Points to a Table in a Different Database on a Different Server
• How to generically invoke the edit form for a SalesLogix datagrid
• How to Use Common Dialogs in SalesLogix
• How to use COALESCE with SalesLogix Queries
• “Simply” adding a new field to OppProduct