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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pass Param from HTML Form to ASP SQL Query??

Status
Not open for further replies.
dkmidi

dkmidi

Technical User
Mar 13, 2002
47
CA
Hi,

I want to be able to select which fields the SQL statement will query data from. So the user would choose a few drop down boxes and pass the params directly into the SQL statement. Is this possible? I can't seem to figure it out.

Below is an example of what I have. Basically if I wanted to replace one of the hard coded fields, ie. Employees.[FirstName], with a selection from my criteria.html form of Employees.[FirstName] instead, how would it work?

Thanks for any help!

Derek

ps. sorry if I'm not making things clear.

Criteria.html Form
<html>
<body>
Select criteria:<p>
<form method=&quot;post&quot; action=&quot;result.asp&quot;>
Choose a field to query:
<select name = &quot;field1&quot;>
<option selected>Employees.[Title]
<option>Employees.[FirstName]
<option>Employees.[HireDate]
</select><p>

<input type = &quot;submit&quot; value = &quot;Search&quot;>
<input type = &quot;reset&quot; value = &quot;Reset Form&quot;>
</form>
</body>
</html>

result.asp

<%Option Explicit%>
<!--METADATA TYPE=&quot;TypeLib&quot;
FILE=&quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->
<html>
<head>
<Title>Northwind Customers</Title>
<link rel = &quot;stylesheet&quot; type = &quot;text/css&quot; href = &quot;myStyle.css&quot;>
</head>

<body>

<h1 align=&quot;center&quot;>Northwind Employees</h1>
<div align = &quot;center&quot;>

<table border = &quot;1&quot; bordercolor = &quot;navy&quot; cellpadding = &quot;5&quot; cellspacing = &quot;0&quot; bgcolor = &quot;lightyellow&quot;>

<!-- Begin ASP code -->
<%
Dim myConn, rsEmployee, strEmployees, fielda
Set myConn = Server.CreateObject(&quot;ADODB.Connection&quot;)

'Use a DSN to connect to northwind.mdb
myConn.Open &quot;DSN=Northwind&quot;,&quot;Admin&quot;,&quot;&quot;

Set rsEmployee = Server.CreateObject(&quot;ADODB.Recordset&quot;)

'Create the big Select statement
strEmployees = &quot;SELECT Employees.[FirstName], Employees.[HireDate], Employees.[Title] &quot; _
& &quot;FROM Employees &quot; _
& &quot;ORDER BY Employees.[FirstName]&quot;

rsEmployee.Open strEmployees,myConn,adOpenForwardOnly,adLockReadOnly

Dim Fname,Lname,region,hireDate,Title

If not rsEmployee.BOF then

Response.Write(&quot;&quot; _
& &quot;<tr bgcolor = &quot;&quot;pink&quot;&quot;>&quot; _
& &quot;<td>&quot; _
& &quot;First Name&quot; _
& &quot;</td>&quot; _
& &quot;<td>&quot; _
& &quot;Title&quot; _
& &quot;</td>&quot; _
& &quot;<td>&quot; _
& &quot;Hire Date&quot; _
& &quot;</td>&quot; _
& &quot;</tr>&quot;)

Do While Not rsEmployee.EOF

Fname = rsEmployee.Fields(&quot;FirstName&quot;)
Lname = rsEmployee.Fields(&quot;LastName&quot;)
hireDate = rsEmployee.Fields(&quot;HireDate&quot;)
Title = rsEmployee.Fields(&quot;Title&quot;)

'Output content data from fields Fname, hireDate and Title

Response.Write(&quot;&quot; _
& &quot;<tr>&quot; _
& &quot;<td>&quot; _
& Lname & &quot;, &quot; & Fname _
& &quot;</td>&quot; _
& &quot;<td>&quot; _
& Title _
& &quot;</td>&quot; _
& &quot;<td>&quot; _
& hireDate _
& &quot;</td>&quot; _
& &quot;</tr>&quot;)

rsEmployee.MoveNext
Loop
Else
Response.Write(&quot;<tr><td colspan = &quot;&quot;3&quot;&quot;>There were no orders</td></tr>&quot;)
End If

Response.Write(&quot;</table>&quot;)

rsEmployee.Close
Set rsEmployee = Nothing

myConn.Close
Set myConn = Nothing
%>
</div>
</table>
</body>
</html>
 
Sort by date Sort by votes
You don't appear to have given values to your drop down boxes. Set these with the SQL you want to pass across to your next page.

You could then set up a few variables equal to these values using request.form.

Then build your SQL statement from all these variables.

If you want some sample code, let me know.
 
Upvote 0 Downvote
Hi SpencerTaylor,

Yes please! Some sample code would be very helpful. I can't figure out how to incorporate the variables into the SQL.

strSQL = &quot;SELECT&quot; & myVariable & &quot;, Employees.[Title] ...

I've tried the above and it doesn't work, I just can't get the syntax right.

Plus can you have a set of ASP tags within another?

ie. <% code <%=Request.Form(&quot;field1&quot;)%> more code %>?

Thanks!
 
Upvote 0 Downvote
>>Plus can you have a set of ASP tags within another?

>> ie. <% code <%=Request.Form(&quot;field1&quot;)%> more code %>?

No. Plus you don't need them, you already have asp tags. You can refer to request.form(&quot;field1&quot;) as long as you are within one set of asp tags.

Some people would define it with a variable early in the code, your_field=request.form(&quot;field1&quot;), then refer to it as your_field in all future references.
 
Upvote 0 Downvote
Here's an example of what I mean



Dim recordset, sqlString, orderBy, searchvalue, searchfield, searchstring
'Set up the vars

orderBy=request.querystring(&quot;OrderBy&quot;)
searchvalue=request.querystring(&quot;SearchValue&quot;)
searchfield=request.querystring(&quot;SearchField&quot;)
'Get values from page, these could just as easy be request.form

if searchvalue<>&quot;&quot; AND searchfield<>&quot;&quot; then

if searchfield=&quot;TableName.DateFieldName&quot; then
searchstring=&quot;AND TableName.DateFieldName LIKE &quot; & CDate(searchvalue) & &quot;))&quot;
end if

searchstring=&quot;AND &quot; & searchfield & &quot; LIKE '%&quot; & searchvalue & &quot;%'&quot;
end if
'Set up a default searchstring if passed parameters are blank. The values checked for are coming from a different recordset open before we get this far. Function and syntax is based on SQL Server.



if OrderBy=&quot;&quot; then
OrderBy=&quot;TableName.FieldName ASC&quot;
end if
'Set up a default sort


if recordset(&quot;FieldName&quot;)>1 then
sqlString=&quot;SELECT &quot; & coststring & &quot;, TableName.OtherField FROM TableName &quot; & searchstring & &quot; ORDER BY &quot; & OrderBy
else
sqlString=Some other select statement
end if

'response.write sqlstring
'Use the line above to test the syntax of your passed SQL statement

recordset.open sqlString, strConnect, 3, 3
'Open the recordset

if not request.querystring(&quot;FilterBy&quot;)=&quot;&quot; then
recordset.filter=request.querystring(&quot;FilterBy&quot;) & &quot;='&quot; & request.querystring(&quot;FilterValue&quot;) & &quot;'&quot;
end if
'Apply a filter if you wish


This should hopefully give you some ideas and some answers. If this doesn't make sense let me know and I'll try and clarify anything I may have forgotten to.

Regards

Spence
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
521
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
514
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
430
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
936
GriffMG
GriffMG
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
845
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top