Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.1 seconds. Powered By: Snitz Forums 2000