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