Ok, so I'm playing around with ASP.NET a litte... for all you people out there this will give a nice example on how to use ASP.NET along with SQLDMO ... there is a better way to do this but I didn't want to code all that so I sort of cheated a little ...I've needed one of these things for a long time... had a version that would use ADO, but I needed to see messages as well. So using SQLDMO I came up with this wacked out version.... didn't take the time to upgrade every little detail to .NET ... just enough to get it working... have fun... (RATHER LONG)<%@ Language="VB" EnableSessionState="false" Explicit="true" Strict="false" Debug="true" %><script language="VBScript" runat="Server"> Public Function IsPostBack() As Boolean IsPostBack = CBool(UCase(Request.ServerVariables("REQUEST_METHOD")) = "POST") End Function Public Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim objSqlServer As Object Dim objDatabase As Object Dim objQueryResults As Object Dim strMessages As String = "" Dim strServer As String = Request("server") Dim strUID As String = Request("uid") Dim strPWD As String = Request("pwd") Dim strTSQL As String = Request("tsql") Dim strResults As String = "" Dim strErrors As String = "" Dim intResultSet As Integer = 0 Dim intRow As Integer = 0 Dim intColumn As Integer = 0 If IsPostBack() Then objSqlServer = Server.CreateObject("SQLDMO.SqlServer2") strTSQL = Request("tsql") On Error Resume Next objSqlServer.Connect(strServer, strUID, strPWD) If Err.Number <> 0 Then strErrors = strErrors & "Connection Error: " & Err.Description & vbCrLf Err.Clear() End If objDatabase = objSqlServer.Databases.Item(CStr(Request("database"))) If Err.Number <> 0 Then strErrors = strErrors & "Database Error: " & Err.Description & vbCrLf Err.Clear() End If objQueryResults = objDatabase.ExecuteWithResultsAndMessages2(strTSQL, strMessages) If Err.Number <> 0 Then strErrors = strErrors & "Data Error: " & Err.Description & vbCrLf Err.Clear() End If strResults = "" For intResultSet = 1 To objQueryResults.ResultSets objQueryResults.CurrentResultset = intResultSet strResults = strResults & "<span id=""header"">Result Set #" & intResultSet & "</span><table id=""recordset""><tr>" For intColumn = 1 To objQueryResults.Columns strResults = strResults & "<th>" & Server.HTMLEncode(objQueryResults.ColumnName(intColumn)) & "</th>" Next strResults = strResults & "</tr>" For intRow = 1 To objQueryResults.Rows strResults = strResults & "<tr>" For intColumn = 1 To objQueryResults.Columns strResults = strResults & "<td>" & Server.HTMLEncode(objQueryResults.GetColumnString(intRow, intColumn)) & "</td>" next strResults = strResults & "</tr>" Next strResults = strResults & "</table><br/><br/>" Next If Err.Number <> 0 Then strErrors = strErrors & "HTML Error: " & Err.Description & vbCrLf Err.Clear() End If objQueryResults = Nothing objDatabase = Nothing objSqlServer.DisConnect() On Error Goto 0 If Len(strErrors) > 0 Then strErrors = "<span id=""errors"">" & strErrors & "</span>" If Len(strErrors) > 0 Then _results.Text = strErrors Else _results.text = strResults End If If Len(strMessages) < 1 Then strMessages = New String("") _messages.Text = strMessages.Replace("[Microsoft][ODBC SQL Server Driver][SQL Server]", "") End If End Sub</script><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Strict//EN"><html> <head> <title>T-SQL Executioner</title> <style type="text/css"> <!-- * { font-size: 10px; } body { background-color: #FFFFFF; color: #000000; cursor: default; font-family: Verdana, Arial; margin: 8px; } a { color: #0000CC; text-decoration: none; } a:hover { color: #CC0000; text-decoration: underline; } #container { border: 2px #CCCCCC groove; border-collapse: collapse; padding: 2px; } #label { font-weight: bold; text-align: right ! important; } #header { font-size: 12px ! important; font-weight: bold; } #errors { color: #CC0000; font-family: Arial ! important; font-weight: bold; line-height: 1.5em; } #recordset { border: 1px #CCCCCC solid; border-collapse: collapse; padding: 2px; } #recordset td { border: 1px #CCCCCC solid; font-size: 11px; } #recordset th { border: 1px #CCCCCC solid; font-size: 11px; text-align: center ! important; } input { font-family: Verdana, Arial; } input.text { width: 16em; } input.submit { background-color: #DFDFDF; border: 2px #CCCCCC groove; font-weight: bold; padding: 0px 8px; } .border-bottom { border-bottom: 2px #CCCCCC groove; } textarea { border: 1px #CCCCCC solid; font-family: "Lucida Console", "Courier New", Courier, monospace; font-size: 11px ! important; height: 20em; margin: 4px; padding: 4px; overflow: auto; text-align: left ! important; white-space: pre; } #results { font-family: "Lucida Console", "Courier New", Courier, monospace; font-size: 11px ! important; height: 20em; overflow: auto; padding: 4px; text-align: left; white-space: pre; } #messages { color: #0000CC; font-family: "Lucida Console", "Courier New", Courier, monospace; font-size: 11px ! important; height: 20em; overflow: auto; padding: 4px; text-align: left; white-space: pre; } --> </style> <script language="JavaScript" for="window" event="onload"> <!-- _form.tsql.style.width = document.all["container"][0].clientWidth - 18 + "px"; results.style.width = document.all["container"][0].clientWidth - 8 + "px"; messages.style.width = document.all["container"][0].clientWidth - 8 + "px"; showResults(); //--> </script> <script language="JavaScript" for="_form" event="onsubmit"> <!-- if (_form.server.value.replace(/^ +| +$/g, "").length < 1) { alert("Server IP is required."); _form.server.focus(); _form.server.select(); return false; } if (_form.database.value.replace(/^ +| +$/g, "").length < 1) { _form.database.value = "master"; } if (_form.uid.value.replace(/^ +| +$/g, "").length < 1) { alert("User ID is required."); _form.uid.focus(); _form.uid.select(); return false; } if (_form.pwd.value.replace(/^ +| +$/g, "").length < 1) { alert("Password is required."); _form.pwd.focus(); _form.pwd.select(); return false; } if (_form.tsql.value.replace(/^ +| +$/g, "").length < 1) { alert("T-SQL Statement is required."); _form.tsql.focus(); _form.tsql.select(); return false; } _form.submit(); //--> </script> <script language="JavaScript"> <!-- function showResults() { document.all["link_results"].style.display = "none"; document.all["link_messages"].style.display = ""; document.all["container"][2].style.display = ""; document.all["container"][3].style.display = "none"; } function showMessages() { document.all["link_results"].style.display = ""; document.all["link_messages"].style.display = "none"; document.all["container"][2].style.display = "none"; document.all["container"][3].style.display = ""; } //--> </script> </head> <body> <form name="_form" action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="post"> <table width="100%" height="100%"> <tr> <td align="center"> <table> <tr> <td> <table id="container" cellspacing="0"> <tr> <td colspan="6" class="border-bottom" align="center"><span id="header">Connection Settings</span></td> </tr> <tr> <td id="label"><span title="This is the IP address of the objSqlServer Server 2000 database or the name of the server if you want to use named pipes.">Server IP:</span></td> <td><input type="text" name="server" class="text" value="<%= Server.HTMLEncode(Request("server")) %>"/></td> <td id="label"><span title="This is the database you would like to access.">Database</span></td> <td><input type="text" name="database" class="text" value="<%= Server.HTMLEncode(Request("database")) %>"/></td> </tr> <tr> <td id="label"><span id="label" title="This is the user name you log into the database with.">User ID:</span></td> <td><input type="text" name="uid" class="text" value="<%= Server.HTMLEncode(Request("uid")) %>"/></td> <td id="label"><span id="label" title="This is the password associated with the User ID used to log into the database with.">Password:</span></td> <td><input type="password" name="pwd" class="text" value="<%= Server.HTMLEncode(Request("pwd")) %>"/></td> </tr> </table> </td> </tr> <tr> <td> </td> </tr> <tr> <td> <table id="container" cellspacing="0"> <tr> <td class="border-bottom" align="center"><span id="header">T-SQL Statement</span></td> </tr> <tr> <td> <textarea name="tsql" wrap="off"><%= Server.HTMLEncode(Request("tsql")) %></textarea> </td> </tr> <tr> <td align="right"><input type="button" value="Execute" class="submit" onclick="this.form.onsubmit()"></td> </tr> </table> </td> </tr> <tr> <td> </td> </tr> <tr> <td> <table> <tr> <td><a href="#" id="link_results" onclick="showResults(); return false;">Results</a><a href="#" id="link_messages" onclick="showMessages(); return false;">Messages</a></td> </tr> </table> </td> </tr> <tr> <td> <table id="container" cellspacing="0"> <tr> <td class="border-bottom" align="center"><span id="header">Results</span></td> </tr> <tr> <td> <div id="results"><asp:label id="_results" runat="Server"/></div> </td> </tr> </table> </td> </tr> <tr> <td> <table id="container" cellspacing="0"> <tr> <td class="border-bottom" align="center"><span id="header">Messages</span></td> </tr> <tr> <td> <div id="messages"><asp:label id="_messages" runat="Server"/></div> </td> </tr> </table> </td </tr> </table> </td> </tr> </table> </form> </body></html>Edited by - onamuji on 03/13/2002 20:41:05
ASP.NET: T-SQL Executioner
Posted - 2002-03-13 : 18:56:14