SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 ASP.NET: T-SQL Executioner
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 03/13/2002 :  18:56:14  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000