SQL Commands from a text box in ASP 2

[Bravogolf]

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

<%
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!

 

[Bravogolf]

Oh, I forgot to add, if it makes a difference in any way, I am querying (or rather, attempting to query) an Access database, which connects fine and all that.

And it's just plain old ASP, not any ASP. something or other.

 

[Sheco]

I don't follow...

Are you wanting to execute an SQL statement supplied by the user and then format the results?

This seems kinda dangrous... what if the user types in a DELETE statement?

 

[Bravogolf]

Yep, I want to execute an SQL statement supplied by the user Once I get this bit working, I can put a stopper in to prevent delete... but in any event it will only be for internal employees and the access database is a temporary cache of a real one

 

[steven290]

now what exactly is the your problem, couldn't figure it out from your question

 

[steven290]

first thing is this

If ("sqlbox") = "SELECT+Name+FROM+Customers"

should be this
If trim(request("sqlbox")) = "SELECT Name FROM Customers

 

[Bravogolf]

Oh ok, my any inconvenience caused.

I have a textbox for entering SQL commands. WHen the user clicks submit, the value of the textbox is passed to an SQL statement that is then executed.

When I display the results, the code assumes I know in advance what table columns to display by using the following:

Response.Write(Rs.Fields("Email").value)

and so on.

In the actual Access table, I have 3 columns. But what if the user only wanted to return the first column back to the webpage? Is there a way of knowing after the statement, has been executed (but not yet displayed back to the user), how many columns it needs to create before displaying them to the user?

 

[steven290]

i would put this as a case statement

select case request("sqlbox")

case "SELECT Name FROM Customers"

....

case "SELECT Name, Email FROM Customers"

...

etc

end select

 

[steven290]

i use the getrows method to get the records the loop through all rows and cols

<%
rows = rs.GetRows()

FRec = LBound(rows, 2)
LRec = UBound(rows, 2)
FCol = LBound(rows, 1)
Lcol = UBound(rows, 1)

%>
<table border="1">
<%

For I = FRec To LRec
	Response.Write "<tr>" & vbCrLf
	
	For J = FCol To LCol

		Response.Write vbTab & "<td>" & rows(J, I) & "</td>" & vbCrLf
	Next 
	
	Response.Write "</tr>" & vbCrLf
Next 
%>
</table>

 

[steven290]

using getrows you won't need top know the field names because it brings them in as an array

 

[Sheco]

Or you could just not worry about it and do something like this:
<%
Response.Write "<table><tr>" & vbCrLf

Dim oFld
For each oFld in rs.Fields
Response.Write "<td>" & oFld.Name & "</td>" & vbCrLf
Next

Response.Write "</tr>" & vbCrLf

Do While Not rs.EoF
Response.Write "<tr>" & vbCrLf
For each oFld in rs.Fields
Response.Write "<td>" & oFld.Value & "</td>" & vbCrLf
Next
Response.Write "</tr>" & vbCrLf
rs.MoveNext
Loop

Response.Write "</table>"
%>

PS: You must really trust your users!

 

[steven290]

nice you threw in the field names as a in the table

 

[Bravogolf]

Superb, thanks guys for each of your examples and amazing patience

 

[Sheco]

Ah, I was typing mine at the same time steven was typing his. They basically create the same output using different methods.

 

[steven290]

also with getrows you have better performance because they may be pulling many, many rows/cols

 

[Sheco]

True dat.

 

[Sheco]

Hey! You beat me outta my spot!

Congrats Steven290!

 

[steven290]

put shecos place the field names as the first row of the table

this line:

Response.Write "<tr>"& vbCrLf
For each field in rs.Fields
  Response.Write "<td>" & field.Name & "</td>" & vbCrLf
Next
Response.Write "</tr>"& vbCrLf

 

[steven290]

thank you, but i know i'll see you up there soon. Again! ;-)

 

[steven290]

sorry i meant - but sheco placed ....