Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleExecute ad hoc SQL scripts with ADO
Keywordsbuild database, DB, ADO, SQL, ad hoc, query, script, query
CategoriesDatabase
 
The cmdExecute_Click event handler splits a SQL script into its semi-colon delimited commands. For each command, the program removes carriage returns, line feeds, and spaces to see if the command is blank.

If the command is not blank, the code looks for the string SELECT in the command. If the command contains the word SELECT, the program calls subroutine ExecuteQuery to execute it and retrieve the results. If the command does not contain the word SELECT, the program calls subroutine ExecuteNonQuery to execute it without retrieving any results.

Subroutine ExecuteNonQuery simply calls the Connection object's Execute method to run a non-SELECT statement.

Subroutine ExecuteQuery calls the Connection object's Execute method to run a SELECT statement. The result is a Recordset object. The routine loops through the Recordset's columns listing them. It then loops through the Recordset's records. For each record, the program displays the record's field values.

My book Visual Basic .NET Database Programming shows how to build a more powerful tool using VB .NET. This tool can correctly connect to SQL Server or MSDE databases, aligns query results in nice columns, etc.

 
' Execute the SQL script.
Private Sub cmdExecute_Click()
Dim commands As Variant
Dim cmd As String
Dim i As Integer
Dim results As String

    ' Break the script into semi-colon
    ' delimited commands.
    commands = Split(txtScript.Text, ";")

    ' Execute each command.
    On Error Resume Next
    For i = LBound(commands) To UBound(commands)
        ' Clean up the command.
        cmd = commands(i)
        cmd = Replace(cmd, vbCr, " ")
        cmd = Replace(cmd, vbLf, " ")
        cmd = Trim$(cmd)

        ' Execute only non-blank commands.
        If Len(cmd) > 0 Then
            ' Display the command.
            results = results & commands(i) & vbCrLf
            txtResults.Text = results
            txtResults.SelStart = Len(results)
            txtResults.Refresh

            ' See if this is a SELECT command.
            If InStr(UCase$(commands(i)), "SELECT") Then
                ' Execute the query.
                results = results & _
                    ExecuteQuery(commands(i))
            Else
                ' Execute the non-query command.
                results = results & _
                    ExecuteNonQuery(commands(i))
            End If

            results = results & vbCrLf & "==========" & _
                vbCrLf
            txtResults.Text = results
            txtResults.SelStart = Len(results)
            txtResults.Refresh
        End If
    Next i

    On Error GoTo 0
    results = results & "Done" & vbCrLf
End Sub

' Execute a non-query command and return
' a success or failure string.
Private Function ExecuteNonQuery(ByVal cmd As String) As _
    String
    ' Execute the command.
    On Error GoTo ExecuteNonQueryError
    m_DBConnection.Execute cmd, , adCmdText

    ExecuteNonQuery = "> Ok"
    Exit Function

ExecuteNonQueryError:
    ExecuteNonQuery = _
        "*** Error executing command ***" & vbCrLf & _
        Err.Description
    Exit Function
End Function

' Execute a query command and return
' the results or failure string.
Private Function ExecuteQuery(ByVal cmd As String) As String
Dim rs As ADODB.Recordset
Dim i As Integer
Dim row As String
Dim txt As String

    ' Execute the command.
    On Error GoTo ExecuteQueryError
    Set rs = m_DBConnection.Execute(cmd, , adCmdText)

    ' Display the column names.
    For i = 0 To rs.Fields.Count - 1
        row = row & ", " & rs.Fields(i).Name
    Next i
    txt = txt & "-----" & vbCrLf & _
        Mid$(row, 3) & vbCrLf & "-----" & vbCrLf

    ' Display the results.
    Do While Not rs.EOF
        row = ""
        For i = 0 To rs.Fields.Count - 1
            row = row & ", " & rs.Fields(i).Value
        Next i
        txt = txt & Mid$(row, 3) & vbCrLf
        rs.MoveNext
    Loop
    rs.Close

    ExecuteQuery = txt
    Exit Function

ExecuteQueryError:
    ExecuteQuery = _
        "*** Error executing SELECT statement ***" & vbCrLf _
            & _
        Err.Description
    Exit Function
End Function
 
Note that this example requires references to the ADO and ADOX object libraries.

See my book Visual Basic .NET Database Programming for information on database programming in VB .NET.

 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated