Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Commands from a text box in ASP 2

Status
Not open for further replies.
Bravogolf

Bravogolf

Programmer
Nov 29, 2002
204
GB
Hi all, I am in desperate need of help. I know the method by which i'm doing this is much too convoluted than it should be but I'm afraid I'm not that great with ASP :(

Basically, I have a input type textbox with the name sqlbox which upon Submit goes to an ASP function. In the ASP function, I have to accomodate every possible command by using Nested if loops. For example, if the user only wanted the first name returned, then I create an if statement asking if that's what's in the text box and if so create the relevant cells.

Here is *some* my code
Code: 
<%
If Request("submit") = "Send" Then 
	If ("sqlbox") = "SELECT+Name+FROM+Customers" Then
		Response.Write("sqlbox")
		sSQL=Request("sqlbox")
		Set Rs = Conn.Execute(sSQL)
	
		Response.Write("<table align='center' width='80%' class='ex' cellpadding='0' cellspacing='0'>")
		Response.Write("<tr><td class='header'>Name</td></tr>")
		
		Do While NOT Rs.EOF
			Response.Write("<tr><td class='tdex'>&nbsp;&nbsp;")
			Response.Write(Rs.Fields("Name").value)
			Response.Write("</td></tr>")
			Rs.MoveNext
		Loop
		Response.Write("</table><br><br>")
	Else If ("sqlbox") = "SELECT Name, Email FROM Customers" Then
		Response.Write("Something Else")
	Else
		Response.Write("None at all")
	End If 
	End If
Else If Request("reset") = "Reset" Then
	sSQL="SELECT * FROM Customers"
	Set Rs = Conn.Execute(sSQL)
	Response.Write("Reset")
	Response.Write("<table align='center' width='80%' class='ex' cellpadding='0' cellspacing='0'>")
	Response.Write("<tr><td class='header'>Name</td><td class='header'>Email</td><td class='header'>Points</td></tr>")
	
	Do While NOT Rs.EOF
		Response.Write("<tr><td class='tdex'>&nbsp;&nbsp;")
		Response.Write(Rs.Fields("Name").value)
		Response.Write("</td><td class='tdex'>&nbsp;&nbsp")
		Response.Write(Rs.Fields("Email").value)
		Response.Write("</td><td class='tdex'>&nbsp;&nbsp")
		Response.Write(Rs.Fields("Points").value)
		Response.Write("</td></tr>")
		Rs.MoveNext
	Loop
	Response.Write("</table><br><br>")
	else
	Response.Write("Nothing...")
	sSQL="SELECT * FROM Customers"
	Set Rs = Conn.Execute(sSQL)
	
	Response.Write("<table align='center' width='80%' class='ex' cellpadding='0' cellspacing='0'>")
	Response.Write("<tr><td class='header'>Name</td><td class='header'>Email</td><td class='header'>Points</td></tr>")
	
	Do While NOT Rs.EOF
		Response.Write("<tr><td class='tdex'>&nbsp;&nbsp;")
		Response.Write(Rs.Fields("Name").value)
		Response.Write("</td><td class='tdex'>&nbsp;&nbsp")
		Response.Write(Rs.Fields("Email").value)
		Response.Write("</td><td class='tdex'>&nbsp;&nbsp")
		Response.Write(Rs.Fields("Points").value)
		Response.Write("</td></tr>")
		Rs.MoveNext
	Loop
	Response.Write("</table><br><br>")
	End If
End If
%>

Is there something like Java where I can use MetaData to predetermine how many columns are going to be needed and then create and name the columns based on that? Or a better method?

Please please help!
 
Sort by date Sort by votes
Too bad it doesn't show what time someone STARTED typing a reply. It seems I frequently need to add a followup that says "disregard my previous post" ...
 
Upvote 0 Downvote
How about this, it's old but functional.

Cleaning out my old pc, finding lots of goodies.

You may want to validate the form to ensure there is no delete or update in the sql before executing it.

Code: 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DBUTils</title>
<style type="text/css">
.QueryTable 
{
	BORDER: silver 1px solid;
	COLOR: black;
	BACKGROUND-COLOR: white
}
.ResultsRow
	{
	Font: Arial;
	Font-Size: 11px;
	Border: black 1px solid;
}
</style>
</head>

<body>
<form name="frmDBUtils" Method="Post">
<table Border="0" Width="100%">
	<tr>
		<td>
			<textarea name="frmQuery" cols=60 rows=10 style="font-family:arial;font-size:11px;font-weight:bold"><%= Request.Form("frmQuery") %></textarea>
		</td>
	</tr>
	<tr>
		<td valign="top">
			<input type="reset">&nbsp;<input type="submit" name="btnSubmit" Value="Query">
		</td>
	</tr>
</table>

</form>
<%
	Dim sRMethod, sQuery
	sRMethod = uCase(Request.ServerVariables("Request_Method"))
	
	IF sRMethod = "POST" Then
		sQuery = Request.Form("frmQuery")

      
		
		Set dbUtils = New clsDBUtils
			Call dbUtils.Query(sQuery)
		Set dbUtils = Nothing
	End IF
%>

</body>
</html>
<%
  
Class clsDBUtils
	
	Public Sub Query(byVal sSQL)
		Dim sConnect, sValue
		Dim oRS
		Dim bHeaderBuilt
		Dim iMod
		
		bHeaderBuilt = false
		
		sConnect = "Provider=SQLOLEDB;Server=Test01;Database=YourDB;User Id=sa;Password=password;"
		
		On Error Resume Next
		Set oRS = Server.CreateObject("ADODB.RecordSet")
		oRS.Open sSQL, sConnect, 3, 3
		
		IF Err.number > 0 Then
			EchoB("<b>" & err.Description & "</b>")
			Response.End
		End IF
		
		Echo("<table border=""0"" width=""100%"" CellPadding=""3"" CellSpacing=""0"" Class=""QueryTable"">")
		
		
		Do While Not oRS.EOF
			iMod = iMod + 1
			IF bheaderBuilt = false Then
				'Build Header
				Echo("<tr bgcolor='skyBlue'>")
				For x = 0 To oRS.Fields.Count - 1
					Echo("<td valign=top style='border-width:1px;border-style:solid;border-color:black;'><b>" & oRS.Fields(x).name & "</b></td>")
				Next
				Echo("</tr>")
				bHeaderBuilt = true
			End IF
			
			IF (iMod Mod 2) = 0 Then
				sBGColor = "silver"
			Else
				sBGColor = "white"
			End IF
			
			Echo("<tr bgcolor=" & sBGColor & " onMouseOver=" & Chr(34) & "this.style.background='orange';return false;" & Chr(34) _
				& "onMouseOut=" & Chr(34) & "this.style.background='" & sBGColor & "';return false" & Chr(34) & ">")
			
			For i = 0 To oRS.Fields.Count -1
				sValue = oRS.Fields(i).value
				IF sValue = "" Then
					sValue = "&nbsp;"
				End IF
				Echo("<td valign=top class=ResultsRow>" & sValue  & "</td>")
			Next
			Echo("</tr>")
			oRS.MoveNext
		Loop
		
		oRS.Close
		Set oRS = Nothing
		
		Echo("</table>")
		
	End Sub
	
	Private Sub EchoB(byVal str)
		Response.Write(str & "<br>" & vbCrLf)
	End Sub
	
	Private Sub Echo(byVal str)
		Response.Write(str & vbCrLf)
	End Sub
		
		
End Class

%>
 
Upvote 0 Downvote
Sorry, that code is over 5 years old. I was pretty sloppy then. But it should still be a good template for you or anyone else that needs this functionality.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
550
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
564
leifoet
leifoet
leifoet
  • Locked
  • Question
Code does not run in Microsoft Edge - it does in Google Chrome
Replies
3
Views
764
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
456
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
READ TEXT FILE BASED ON VARIABLE 1
Replies
2
Views
450
guitarzan
guitarzan

Part and Inventory Search

Sponsor

Back
Top