I could use a hand.  I'm brand new to ASP, but I got the ASP code below 
from the Microsoft Knowledge Base (Q173308, if you're interested).  I've 
modified it to accept parameters, and I think I'm close, but I'm stumped 
by the following:

I'm trying to pass parameters from an HTML page to an ASP page.  The ASP 
uses these parameters to build an SQL query and send it to the server.

Here is the Form from my HTML page:

<FORM METHOD="POST" ACTION="/testresults/showimg.asp">
	<INPUT TYPE="HIDDEN" NAME="Field" VALUE="Extraneous_IM"/>
	<INPUT TYPE="HIDDEN" NAME="Table" VALUE="Extraneous_T"/>
	<INPUT TYPE="HIDDEN" NAME="FieldID" VALUE="Extraneous_ID"/>
	<INPUT TYPE="HIDDEN" NAME="FieldIDValue" VALUE="{@Extraneous_ID}"/>
	<INPUT TYPE="SUBMIT" VALUE="Image {@Extraneous_ID}"/>
</FORM>


And here is the ASP code:

<%@ LANGUAGE="VBSCRIPT" %>
<% Dim FieldName,TableName,Field_ID,Field_ID_value
FieldName = Request.Form("Field")
Table = Request.Form("Table")
Field_ID = Request.Form("FieldID")
Field_ID_value = Request.Form("FieldIDvalue")
%>
<%
' Clear out the existing HTTP header information
Response.Expires = 0
Response.Buffer = TRUE
Response.Clear

' Change the HTTP header to reflect that an image is being passed.
Response.ContentType = "image/gif"

Set cn = Server.CreateObject("ADODB.Connection")
' The following open line assumes you have set up a System DataSource
' by the name of myDSN.
cn.Open "DSN=Results;UID=sa;PWD=password;DATABASE=TestResults"
Set rs = cn.Execute("SELECT FieldName FROM Table WHERE 
Field_ID='Field_ID_value'")
Response.BinaryWrite rs("FieldName")
Response.End
%>

When I launch this in IE6, I get the error message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the 
keyword 'Table'.
/testresults/showimg.asp, line 21


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) 

Page:
POST 75 bytes to /testresults/showimg.asp

POST Data:
Field=Extraneous_IM&Table=Extraneous_T&FieldID=Extraneous_ID&FieldIDValue=1

Looking at the POST Data, it appears that I am getting the proper 
information passed through.  But what is causing the syntax error?

Any help would be much appreciated.

Ken

You need to change the SQL statement to use the actual table and fieldnames
you have in your database.

"SELECT FieldName FROM Table WHERE
Field_ID='Field_ID_value'"

If you actually have a Table called "Table", then I suggest you change it.
Using words that are "reserved" as names for your objects is not
recommended. Change it to "Images" or something.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ken" <asp@k...>
Subject: [asp_databases] Syntax error when executing SQL query via ASP


: I could use a hand.  I'm brand new to ASP, but I got the ASP code below
: from the Microsoft Knowledge Base (Q173308, if you're interested).  I've
: modified it to accept parameters, and I think I'm close, but I'm stumped
: by the following:
:
: I'm trying to pass parameters from an HTML page to an ASP page.  The ASP
: uses these parameters to build an SQL query and send it to the server.
:
: Here is the Form from my HTML page:
:
: <FORM METHOD="POST" ACTION="/testresults/showimg.asp">
: <INPUT TYPE="HIDDEN" NAME="Field" VALUE="Extraneous_IM"/>
: <INPUT TYPE="HIDDEN" NAME="Table" VALUE="Extraneous_T"/>
: <INPUT TYPE="HIDDEN" NAME="FieldID" VALUE="Extraneous_ID"/>
: <INPUT TYPE="HIDDEN" NAME="FieldIDValue" VALUE="{@Extraneous_ID}"/>
: <INPUT TYPE="SUBMIT" VALUE="Image {@Extraneous_ID}"/>
: </FORM>
:
:
: And here is the ASP code:
:
: <%@ LANGUAGE="VBSCRIPT" %>
: <% Dim FieldName,TableName,Field_ID,Field_ID_value
: FieldName = Request.Form("Field")
: Table = Request.Form("Table")
: Field_ID = Request.Form("FieldID")
: Field_ID_value = Request.Form("FieldIDvalue")
: %>
: <%
: ' Clear out the existing HTTP header information
: Response.Expires = 0
: Response.Buffer = TRUE
: Response.Clear
:
: ' Change the HTTP header to reflect that an image is being passed.
: Response.ContentType = "image/gif"
:
: Set cn = Server.CreateObject("ADODB.Connection")
: ' The following open line assumes you have set up a System DataSource
: ' by the name of myDSN.
: cn.Open "DSN=Results;UID=sa;PWD=password;DATABASE=TestResults"
: Set rs = cn.Execute("SELECT FieldName FROM Table WHERE
: Field_ID='Field_ID_value'")
: Response.BinaryWrite rs("FieldName")
: Response.End
: %>
:
: When I launch this in IE6, I get the error message:
:
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
: keyword 'Table'.
: /testresults/showimg.asp, line 21
:
:
: Browser Type:
: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
:
: Page:
: POST 75 bytes to /testresults/showimg.asp
:
: POST Data:
:
Field=Extraneous_IM&Table=Extraneous_T&FieldID=Extraneous_ID&FieldIDValue=1
:
: Looking at the POST Data, it appears that I am getting the proper
: information passed through.  But what is causing the syntax error?
:
: Any help would be much appreciated.
:
: Ken

Thanks, Ken.

I don't have a column named Table.  I dimensioned that as a variable, to 
be replaced by the value being passed from the Form.  At least that's my 
intent.  I originally had named the variable "TableName," but that yielded 
a different error, "Page not found."  By changing it to "Table," I got a 
more descriptive error, which I took to mean I was making progress.

Thanks again.

Ken



> You need to change the SQL statement to use the actual table and 
fieldnames
you have in your database.

"SELECT FieldName FROM Table WHERE
Field_ID='Field_ID_value'"

If you actually have a Table called "Table", then I suggest you change it.
Using words that are "reserved" as names for your objects is not
recommended. Change it to "Images" or something.

Cheers
Ken

At the moment your code is doing this:

Set rs = cn.Execute("SELECT FieldName FROM Table WHERE
Field_ID='Field_ID_value'")

The stuff between the " " marks is literal text, and it is being sent to the
DBMS (SQL Server) to be processed as an SQL statement. The DBMS is saying
"there is no table in the database called 'table'"

If you have a variable which holds the name of the table, then you need to
concantenate that with the literal text to build the whole string, to send
to the database. The following might be an example which is a bit easier to
follow:

<%
Dim strSQL                ' as string: our SQL statement
Dim strTableName      ' as string: the name of our table in the database
Dim intImageID           ' as integer: the ID of the record we need to get

' Get ImageID from user (from HTML text input called "txtImageID")
intImageID = Request.Form("txtImageID")

' Variable holding our tablename
strTableName = "Images"

' Now concatentate the whole thing together
strSQL = _
    "SELECT ImageData FROM " & strTableName & " WHERE ImageID = " &
intImageID

' Now send SQL statement to database, and put results into Recordset:
Set objRS = objConn.Execute(strSQL)
%>

Cheers
Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ken" <asp@k...>
Subject: [asp_databases] Re: Syntax error when executing SQL query via ASP


: Thanks, Ken.
:
: I don't have a column named Table.  I dimensioned that as a variable, to
: be replaced by the value being passed from the Form.  At least that's my
: intent.  I originally had named the variable "TableName," but that yielded
: a different error, "Page not found."  By changing it to "Table," I got a
: more descriptive error, which I took to mean I was making progress.
:
: Thanks again.
:
: Ken
:
:
:
: > You need to change the SQL statement to use the actual table and
: fieldnames
: you have in your database.
:
: "SELECT FieldName FROM Table WHERE
: Field_ID='Field_ID_value'"
:
: If you actually have a Table called "Table", then I suggest you change it.
: Using words that are "reserved" as names for your objects is not
: recommended. Change it to "Images" or something.
:
: Cheers
: Ken

Okay, I think I see it now.  I'll give it a go tomorrow.

I really appreciate your time.  I've been thrust into this project, and 
I've had to ham-and-egg my way through XML, XSLT, T-SQL, and, as of today, 
ASP to get this project done.  Chris Auld called it "JIT," or "Just-In-
Time" training.

Thanks again,
Ken

> At the moment your code is doing this:

Set rs = cn.Execute("SELECT FieldName FROM Table WHERE
Field_ID='Field_ID_value'")

The stuff between the " " marks is literal text, and it is being sent to 
the
DBMS (SQL Server) to be processed as an SQL statement. The DBMS is saying
"there is no table in the database called 'table'"

If you have a variable which holds the name of the table, then you need to
concantenate that with the literal text to build the whole string, to send
to the database. The following might be an example which is a bit easier to
follow:

<%
Dim strSQL                ' as string: our SQL statement
Dim strTableName      ' as string: the name of our table in the database
Dim intImageID           ' as integer: the ID of the record we need to get

' Get ImageID from user (from HTML text input called "txtImageID")
intImageID = Request.Form("txtImageID")

' Variable holding our tablename
strTableName = "Images"

' Now concatentate the whole thing together
strSQL = _
    "SELECT ImageData FROM " & strTableName & " WHERE ImageID = " &
intImageID

' Now send SQL statement to database, and put results into Recordset:
Set objRS = objConn.Execute(strSQL)
%>

Cheers
Ken

Ken,

YOU'RE A GENIUS (and you've got a great name :o)!  Your example gave me 
the key I needed.  So, just for my own clarification, the problem was 
that, while the correct data was being passed into the ASP file, the ASP 
file was not correctly building the SQL query because I was giving it 
literal names rather than variables (by enclosing everything in quotation 
marks).

Here's the SQL statement that finally worked:

Set rs = cn.Execute("SELECT " & strFieldName & " FROM " & strTableName & " 
WHERE " & intField_ID " = '" &intField_ID_value & "'")

Thanks again!

Ken