Accessing SQL Server Through an ASP Page
Written by Eric Smith, Northstar Computer Systems LLC
At some of my client sites, the SQL Enterprise Manager TCP/IP port (typically 1203) is blocked by a firewall. Since getting a firewall port opened nearly requires an act of Congress, I created a simple interface to provide access to an external SQL Server database that gives me a web page to enter my SQL and run queries. Here's the code:
Const ACTION = "a"
Const ACTION_VALIDATE = "v"
Const ACTION_EXECUTE = "e"
Const ACTION_QUERY = "q"
Call Main
Function BuildValidateURL()
BuildValidateURL = "<form action=""" & Request.ServerVariables("SCRIPT_NAME") _
& "?" & ACTION & "=" & ACTION_VALIDATE & """ method=POST>"
End Function
Sub WriteLine(strText)
Response.Write strText & vbCrLf
End Sub
Function OpenDatabase()
Dim dcnDB ' As ADODB.Connection
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.Open "Provider=SQLOLEDB.1;" _
& "Data Source=DBSource;" _
& "Initial Catalog=DBName;" _
& "User ID=userid;" _
& "Password=password;"
Set OpenDatabase = dcnDB
End Function
Sub Main
If Request(ACTION) = ACTION_VALIDATE Then
Dim dcnDB
Set dcnDB = OpenDatabase()
If Request("optAction") = ACTION_EXECUTE Then
dcnDB.Execute Request("query")
dcnDB.Close
Set dcnDB = Nothing
Response.Redirect Request.ServerVariables("SCRIPT_NAME")
Else
Dim rsData
Dim rsField
Set rsData = dcnDB.Execute(Request("query"))
WriteLine "<table><tr>"
For Each rsField in rsData.Fields
WriteLine "<th>" & rsField.Name & "</th>"
Next
WriteLine "</tr>"
Do Until rsData.EOF
WriteLine "<tr>"
For Each rsField in rsData.Fields
WriteLine "<td>" & rsField.Value & "</td>"
Next
rsData.MoveNext
WriteLine "</tr>"
Loop
WriteLine "</table>"
rsData.Close
Set rsData = Nothing
dcnDB.Close
Set dcnDB = Nothing
End If
Else
WriteLine BuildValidateURL()
WriteLine "<input type=radio name=optAction value=" _
& ACTION_EXECUTE & " CHECKED>Execute<br>"
WriteLine "<input type=radio name=optAction value=" _
& ACTION_QUERY & ">Query<br>"
WriteLine "<textarea name=query rows=10 cols=50 wrap=virtual></textarea><br>"
WriteLine "<input type=submit name=cmdSubmit value=Submit><br>"
WriteLine "</form>"
End If
End Sub
Your form will have two option buttons, a text area, and a command button. Based on the option you pick, your SQL statement will either be executed, or you'll get the results back from the query. Some of the functions at the top of the file are accessory functions that I use frequently – feel free to combine them into the code or to use the functions yourself.
Keywords: [
Active Data Objects
|
SQL
]
Publication Date: 1/1/2000, Last Update: 2/12/2010
|