GetString Method Example (VB)
TOC
Collapse the table of content
Expand the table of content

GetString Method Example (VB)

 

This example demonstrates the GetString method.

Assume you are debugging a data access problem and want a quick, simple way of printing the current contents of a small Recordset.

'BeginGetStringVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub Main()
    On Error GoTo ErrorHandler

     ' connection variables
    Dim Cnxn As ADODB.Connection
    Dim rstAuthors As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQLAuthors As String
    Dim varOutput As Variant

     ' specific variables
    Dim strPrompt As String
    Dim strState As String

     ' open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn

     ' get user input
    strPrompt = "Enter a state (CA, IN, KS, MD, MI, OR, TN, UT): "
    strState = Trim(InputBox(strPrompt, "GetString Example"))

     ' open recordset
    Set rstAuthors = New ADODB.Recordset
    strSQLAuthors = "SELECT au_fname, au_lname, address, city FROM Authors " & _
                "WHERE state = '" & strState & "'"
    rstAuthors.Open strSQLAuthors, Cnxn, adOpenStatic, adLockReadOnly, adCmdText

    If Not rstAuthors.EOF Then
    ' Use all defaults: get all rows, TAB as column delimiter,
    ' CARRIAGE RETURN as row delimiter, EMPTY-string as null delimiter
       varOutput = rstAuthors.GetString(adClipString)
        ' print output
       Debug.Print "State = '" & strState & "'"
       Debug.Print "Name             Address             City" & vbCr
       Debug.Print varOutput
    Else
       Debug.Print "No rows found for state = '" & strState & "'" & vbCr
    End If

    ' clean up
    rstAuthors.Close
    Cnxn.Close
    Set rstAuthors = Nothing
    Set Cnxn = Nothing
    Exit Sub

ErrorHandler:
    ' clean up
    If Not rstAuthors Is Nothing Then
        If rstAuthors.State = adStateOpen Then rstAuthors.Close
    End If
    Set rstAuthors = Nothing

    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndGetStringVB
Show:
© 2016 Microsoft