Passing Strings and String Arrays | Ado Examples and Best Practices

Team-Fly

A tempting alternative to Recordsets can be as simple as passing a string back from the server. However, this alternative makes it harder to perform updates on the underlying data—harder, but not impossible. In cases where updating is not done through cursors, it does not matter. For example, when the query is complex—too complex for ADO to manage updatability on its own—you'll have to fall back on a "manual" update strategy anyway. This is typically the case when the result set is generated from stored procedures, especially those with complex or multiple result sets. Situations like this are often handled through stored procedures designed to process the updates. To get these to work, you'll have to construct the parameters to pass the primary key on your own—ADO can't do it for you as it does when working with Recordsets.

Note

When considering this strategy, remember that the Data Object Wizard can generate the Visual Basic code needed to execute canned procedures (stored or ad hoc), as well as the code needed to create custom controls to display and capture user changes. The Data Object Wizard can make the implementation of this design painless.

When working with string-based result sets, you also need to consider that Visual Basic is notoriously clumsy at handling strings, as we discussed earlier. If you have to have Visual Basic parse the strings field by field, you'll lose ground to a Recordset approach, where field management is done for you. You won't be able to simply cram a multirow string into a grid unless you first format it correctly.

Filling a Grid with a Clip String

To get around Visual Basic's limitation in parsing strings, you can construct a clip-format delimited string that you can pass directly to a control supporting the Clip property (such as the MSHFlexGrid), but setting up the grid to accept it is a little tiresome. The following example shows how to do it, for those who want to try. Another subtle point here—you might want to include the column names as the first row of your delimited string, so the grid control has its headings filled in. This code expects that to be the case. I set the FixedRows property to 1 and the Rows property to 5 for this example:

 Sub FillGrid(strParm As String) With MSHFlexGrid1     .Clear     .Row = 0     .ColSel = 1     .RowSel = 4     .Clip = strParm     .Row = 1     .ColWidth(0) = TextWidth(String(Len(.Text), "M"))     .RowSel = 1 End With End Sub

If you want ADO to construct a delimited string from the data in your result set, you can use the GetString method—it's similar to the GetClipString method in RDO. The GetString method is more flexible than RDO's version, but it has a fairly serious shortcoming—it does not know how to properly frame the strings. That is, GetString does not put quotes around the string elements; it simply separates them with a selected delimiter. This means that if your string has embedded delimiters (embedded tabs by default), ADO becomes confused and does not construct the delimited string result set correctly. This limitation aside, GetString (according to some tests done by the MDAC team) is about five times faster than performing the same operations yourself in code.

You'll also find that ADO does not attempt to "stringify" binary fields. That is, if you have selected a column containing your customer's picture, ADO will simply return a null when it tries to convert it to a string. When we look at XML, we'll find that ADO does convert binary fields to their Unicode equivalent.

Here's an example of constructing a clip string in code. In this case, we run a query to return the authors for a selected ISBN.

 Public Function GetAuthors(ISBN) As String Const Headers As String = "Author" & vbTab & "Year Born" & vbCr Dim strData As String Dim i As Integer OpenConnection Set rs = New Recordset rs.Open "SELECT Authors.Author, Authors.Year_Born " _             & " FROM Authors INNER JOIN Title_Author_             & ON Authors.Au_ID = Title_Author.Au_ID " _             & " WHERE (Title_Author.ISBN = '" & ISBN & "' )", _ cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.EOF Then     GetAuthors = ""     Exit Function Else     strData = rs.GetString(adClipString, −1, , , "<null>")     GetAuthors = Headers & strData End If cn.Close End Function

Note that the "strData =" line (in bold, a few lines up) executes the GetString method using the default (tab) delimiter for fields and vbCr (0×0D) for rows. We also pass in the string "<null>" whenever the value is not provided, as when the year born data is not known.

My tests show that once you get past the first dozen or so rows, passing strings gets to be increasingly expensive, in terms of time, memory, LAN, and patience consumption. Most of the time seems to be lost in handling the delimited string itself, if you have to process the string element by element. You also have to deal with embedded framing quotes and other string-delimiter details, such as when strings contain the character you're using as a framing quote. This is the typical 'O'Malley' problem.

Consider that when working with the Web and ASP (Visual Basic Script), you don't really work with strings at all—you have to deal with Variants containing strings. These can be even slower.

In the end, you'll find it's far faster to pass single-row result sets via a simple delimited string than it is to pass a single-row (or few-row) Recordset. If all you need in response to your query is a simple "yes" or "no," then it's silly to construct a full-blown Recordset to pass the data.

Team-Fly