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




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
Else
‘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
cnn.Close
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
Next

‘ 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
rst.Close
Set rst = Nothing
cnn.Close
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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles