Another VB-ADO Tool to View Data from a SQL Database

Monday Dec 11th 2000 by Danny Lesandrini

Does the World Need Another ADO to SQL Lesson? Frankly, I didn't think so, but I was wrong. One of our clients recently requested information about how to connect to their SQL Server database using ADO code in Visual Basic (and/or ASP).

Does the World Need Another ADO to SQL Lesson?

Frankly, I didn't think so, but I was wrong.

One of our clients recently requested information about how to connect to their SQL Server database using ADO code in Visual Basic (and/or ASP). Having worked with ADO for a couple of years already and being so comfortable with the technology, I was surprised to receive such a request. But, what seemed like trivial code for me was difficult for my code-novice client.

I scratched out a simple VB form with text boxes to provide the necessary login arguments: Server Name, Database Name, User and Password. The form provides a sample SQL Statement for the Pubs database and attempts to format the results with headers for easy reading.

It was not the intention of this project to create a substitute for the SQL Server Query Analyzer, although it has a similar function. You may execute any SQL statement that returns a recordset (including system stored procs such as sp_Help). The real purpose was to provide a template that could be used by novice coders to create their own ad-hoc ADO to SQL Server reporting tools.

The code of most importance is reproduced below with comments interspersed and the VB project is available for download.     View Screen Shot of Form Here     Sample ASP Code

Code for Connect and Execute Buttons

'********* Connect Command Button Code **************
    Private Sub cmdConnect_Click()
    On Error Resume Next
        ' Validate user supplied login arguments
        ' ... validation code goes here
        'Create connect string from user input box values
        strConnect = "Provider=SQLOLEDB.1" _
                   & ";User ID=" & Me!txtUID _
                   & ";Password=" & Me!txtPWD _
                   & ";Initial Catalog=" & Me!txtDatabase _
                   & ";Data Source=" & Me!txtServer
        Screen.MousePointer = vbHourglass

        'Call sub to test connect string
        If TestConnectString(strConnect) = False Then
            strMsg = "Server not found or login invalid."
            MsgBox strMsg, vbExclamation, "Error"
            'Exit the routine because there was an error
            'Toggle command buttons and text boxes appropriately
            '... do stuff here
        End If
        Screen.MousePointer = vbNormal
    End Sub
'********* Test Connect String Sub **************
    Function TestConnectString(ByVal sConn As String) As Boolean
    On Error Resume Next

        Dim cnn As ADODB.Connection
        Set cnn = New ADODB.Connection
        'TestConnectString initializes to False by default
        cnn.Open sConn
        'No error means that the connect string works!
        If Err.Number = 0 Then TestConnectString = True
        ' Clean up and release resources
        Set cnn = Nothing

    End Function
'********* Execute Command Button Code **************
    Private Sub cmdExecute_Click()
    On Error Resume Next
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim fld As ADODB.Field
        Dim strMsg As String
        Dim strHeaders As String
        Dim strResults As String
        ' Simple validation that SQL statement exists
        If Len(Me!txtSQL) = 0 Then
            strMsg = "Enter a valid SQL Statement."
            MsgBox strMsg, vbExclamation, "Error"
            Exit Sub
        End If
        MousePointer = vbHourglass
        ' Instantiate Connection and Recordset objects
        Set cnn = New ADODB.Connection
        Set rst = New ADODB.Recordset
        ' Open Connection and Load Recordset
        cnn.Open strConnect
        rst.Open CStr(Me!txtSQL), cnn
        ' Create column headers
        For Each fld In rst.Fields
            strHeaders = strHeaders & UCase(fld.Name) & vbTab 
        ' Use the GetString method to retrieve recordset text
        strResults = rst.GetString(adClipString, -1, vbTab, vbCrLf)
        ' Return header and data to results pane
        Me!txtResults = strHeaders & vbCrLf & strResults
        ' If there was an error then replace the output
        ' text with the description of the error.
        If Err.Number > 0 Then Me!txtResults = Err.Description
        ' Clean up and release resources
        Set rst = Nothing
        Set cnn = Nothing
        MousePointer = vbNormal
    End Sub

Well, that's all there is to it. I've included a sample ASP page which uses the same ADO code and SQL example with the download for this article. As you can see, ADO isn't dificult to implement and works equally well from Visual Basic and ASP. Soon though, we'll be reading more about ADO's little brother, ADO.Net. In a future article I'll provide code to work with the newest of Microsoft's data access technologies.

See All Articles by Columnist Danny Lesandrini

Another VB-ADO SQL Tool to View Data from a SQL Database

Back to Article

Sample ASP Code Using ADO to Get Pubs Data


Dim sSQL
Dim dbPubs
Dim rsPubs

' Create an ADO database connection
Set dbPubs = server.createobject("adodb.connection")

' Create a record set
Set rsPubs = server.CreateObject("adodb.recordset")

' ////////////////////////////////////////////////////////////////
' This is the connection string section
' It must be edited to reflect your login params:
' Data Source=Server
' Initial Catalog=Database
' User ID=UID
' Password=PWD

' Open the connection using our ODBC file DSN

sConn = "Provider=SQLOLEDB.1;Initial Catalog=Pubs;"
sConn = sConn & "Data Source=dan;User ID=sa;Password=;"
' ////////////////////////////////////////////////////////////////

sSQL = sSQL & "SELECT au_fName + ' ' + au_lName AS FullName , "
sSQL = sSQL & " Titles.Title AS Publication "
sSQL = sSQL & "FROM Authors "
sSQL = sSQL & "INNER JOIN TitleAuthor "
sSQL = sSQL & "ON Authors.au_id = TitleAuthor.au_id "
sSQL = sSQL & "INNER JOIN Titles "
sSQL = sSQL & "ON Titles.title_id = TitleAuthor.title_id "
sSQL = sSQL & "WHERE Authors.State='CA' "

' Execute the sSQL statement
Set rsPubs = dbPubs.Execute(sSQL)

' Now, create the ASP Table

<BODY bgColor=lightyellow>

<TABLE width="100%" border=1 align=center>
<TH width="25%">Full Name</TH>
<TH width="75%">Title</TH>

' Loop through recordset, inserting rows intp ASP table
Do Until rsPubs.EOF
Response.Write("<TD>" & rsPubs("FullName") & "</TD>")
Response.Write("<TD>" & rsPubs("Publication") & "</TD>")


Set rsPubs = Nothing

Set dbPubs = Nothing

Back to Article

See All Articles by Columnist Danny Lesandrini