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

search in asp 1

Status
Not open for further replies.
stpmtp

stpmtp

MIS
Jan 6, 2006
67
US
Hello,

I have a form that consists of check boxes, and radio buttons. I am trying to make this into a search, but I don't know how to go about it. I am kind of new at asp. I know how to connect to an access db but I am not too sure about the asp and sql logic
could anybody give me a hand with this? I have pasted the form that I created







Code: 
<table width="800" border="0" cellspacing="0" cellpadding="1">





</table>

<table width="800" border="0" cellspacing="0" cellpadding="0">
<tr> 
	<td height="25" >
		<span class="smallertext">
		</span>
	</td>
</tr>
</table>
<table width="800" border="0" cellpadding="1" cellspacing="0" class="outerBorder">
<tr> 
	<td> 
		<table width="100%" border="0" cellpadding="0" cellspacing="1" class="innerTableBG">
		<tr> 
			<td colspan="100%" class="tableTitle">&nbsp;
			</td>
		</tr>
		
		
		<tr>
		<td colspan="100%">
		<center>
		</center>
		</td>
		</tr>
		
		<tr>
			<td height="30" class="bodytextBold">
				<table width="100%" border="0" cellpadding="0" cellspacing="0" class="innerTableBG">
				<form name="PSearchForm" method="Get" action="/cat253/psearch.do">
				<INPUT TYPE="hidden" name="searchType" 
					value="1"/>
				<input type="hidden" name="categoryId" value="152">
				<input type="hidden" name="orgId" value="davies">

				 
			
				
				<TR>
					
					
					
					<td class="tableOddRow" rowspan="3"><strong>Insert</strong></td>
					
					<TD nowrap align="right" height="30" class="tableOddRow">
						<strong>
						Type
						
						
						</strong>
					</TD>
					<td class="tableOddRow"><img src="../../images/trans.gif" width="6" height="8"></td>
					<td class="bodytext"><img src="../../images/trans.gif" width="6" height="8"></td>
					<TD nowrap height="30" class="bodytext">
											
					
						
					
						
					
						
						

						
						
							
							
								
							
						

							
						
							
							
							
							
							
							
								<input type="radio" name="P_406" 
									value='Brass Bushing' 
									
								/>
								
								Brass Bushing
							
							
						
							
							
							
							
							
							
								<input type="radio" name="P_406" 
									value='Threaded Insert' 
									
								/>
								
								Threaded Insert
							
							
						
							
							
							
							
							
							
								<input type="radio" name="P_406" 
									value='Molded Thread' 
									
								/>
								
								Molded Thread
							
							
						
							
							
							
							
							
							
								<input type="radio" name="P_406" 
									value='Projecting Stud' 
									
								/>
								
								Projecting Stud
							
							
						
							
							
								
								<input type="radio" name="P_406" value="any" 
									checked
								/>- No Preference -
							

						
					
						
					
						
					</TD>
					<td><img src="../../images/trans.gif" width="5" height="1"></td>
				</TR>
				<tr><td class="lineBtwRow" colspan="100%"><img src="../../images/trans.gif" width="100%" height="1" border="0"></td></tr>
				
				<TR>
					
					
					
					<TD nowrap align="right" height="30" class="tableOddRow">
						<strong>
						Material
						
						
						</strong>
					</TD>
					<td class="tableOddRow"><img src="../../images/trans.gif" width="6" height="8"></td>
					<td class="bodytext"><img src="../../images/trans.gif" width="6" height="8"></td>
					<TD nowrap height="30" class="bodytext">
											
					
						
					
						
					
						
						

						
						
							
							
								
							
						

							
						
							
							
							
							
								<input type="checkbox" name="P_407" 
									value='Aluminum' 
									
								/>
								
								Aluminum
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_407" 
									value='Brass' 
									
								/>
								
								Brass
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_407" 
									value='Stainless Steel' 
									
								/>
								
								Stainless Steel
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_407" 
									value='Steel' 
									
								/>
								
								Steel
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_407" 
									value='Zinc Plated' 
									
								/>
								
								Zinc Plated
							
							
							
							
						
							
							

						
					
						
					
						
					</TD>
					<td><img src="../../images/trans.gif" width="5" height="1"></td>
				</TR>
				<tr><td class="lineBtwRow" colspan="100%"><img src="../../images/trans.gif" width="100%" height="1" border="0"></td></tr>
				
			
				 
			
				
				<TR>
					
					
					
					<td class="tableOddRow" rowspan="5"><strong>Dimensions</strong></td>
					
					<TD nowrap align="right" height="30" class="tableOddRow">
						<strong>
						A
						
						(inch)
						</strong>
					</TD>
					<td class="tableOddRow"><img src="../../images/trans.gif" width="6" height="8"></td>
					<td class="bodytext"><img src="../../images/trans.gif" width="6" height="8"></td>
					<TD nowrap height="30" class="bodytext">
											
					
						
					
						
					
						
						

						
						
							
							
								
							
						

							
						
							
							
							
							
								<input type="checkbox" name="P_410" 
									value='25/32' 
									
								/>
								
								25/32
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_410" 
									value='1' 
									
								/>
								
								1
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_410" 
									value='1-1/4' 
									
								/>
								
								1-1/4
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_410" 
									value='1-13/32' 
									
								/>
								
								1-13/32
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_410" 
									value='1-5/8' 
									
								/>
								
								1-5/8
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_410" 
									value='2' 
									
								/>
								
								2
							
							
							
							
						
							
							

						
					
						
					
						
					</TD>
					<td><img src="../../images/trans.gif" width="5" height="1"></td>
				</TR>
				<tr><td class="lineBtwRow" colspan="100%"><img src="../../images/trans.gif" width="100%" height="1" border="0"></td></tr>
				
				<TR>
					
					
					
					<TD nowrap align="right" height="30" class="tableOddRow">
						<strong>
						B
						
						(inch)
						</strong>
					</TD>
					<td class="tableOddRow"><img src="../../images/trans.gif" width="6" height="8"></td>
					<td class="bodytext"><img src="../../images/trans.gif" width="6" height="8"></td>
					<TD nowrap height="30" class="bodytext">
											
					
						
					
						
					
						
						

						
						
							
							
								
							
						

							
						
							
							
							
							
								<input type="checkbox" name="P_411" 
									value='47/64' 
									
								/>
								
								47/64
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_411" 
									value='29/32' 
									
								/>
								
								29/32
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_411" 
									value='1-3/16' 
									
								/>
								
								1-3/16
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_411" 
									value='1-3/8' 
									
								/>
								
								1-3/8
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_411" 
									value='1-1/2' 
									
								/>
								
								1-1/2
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_411" 
									value='1-27/32' 
									
								/>
								
								1-27/32
							
							
							
							
						
							
							

						
					
						
					
						
					</TD>
					<td><img src="../../images/trans.gif" width="5" height="1"></td>
				</TR>
				<tr><td class="lineBtwRow" colspan="100%"><img src="../../images/trans.gif" width="100%" height="1" border="0"></td></tr>
				
				<TR>
					
					
					
					<TD nowrap align="right" height="30" class="tableOddRow">
						<strong>
						C
						
						(inch)
						</strong>
					</TD>
					<td class="tableOddRow"><img src="../../images/trans.gif" width="6" height="8"></td>
					<td class="bodytext"><img src="../../images/trans.gif" width="6" height="8"></td>
					<TD nowrap height="30" class="bodytext">
											
					
						
					
						
					
						
						

						
						
							
							
								
							
						

							
						
							
							
							
							
								<input type="checkbox" name="P_412" 
									value='3/8' 
									
								/>
								
								3/8
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_412" 
									value='37/64' 
									
								/>
								
								37/64
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_412" 
									value='39/64' 
									
								/>
								
								39/64
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_412" 
									value='5/8' 
									
								/>
								
								5/8
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_412" 
									value='7/8' 
									
								/>
								
								7/8
							
							
							
							
						
							
							
							
							
								<input type="checkbox" name="P_412" 
									value='1-1/16' 
									
								/>
								
								1-1/16
							
							
							
							
						
							
							

						
					
						
					
						
					</TD>
					<td><img src="../../images/trans.gif" width="5" height="1"></td>
				</TR>
				<tr><td class="lineBtwRow" colspan="100%"><img src="../../images/trans.gif" width="100%" height="1" border="0"></td></tr>
				
			
				

				<tr class="buttonRow"> 
					<td colspan="3"><img src="../../images/trans.gif" width="16" height="8"></td>
					<td colspan="4"> 
						<input type="submit" value="Search" class="button">
						<input type="button" class="button" value="Clear" onclick="toReset()"/>
						<input type="button" class="button" value="Cancel" onclick="toCancel()"/>
					<td>
				</tr>
				</form>
				</table>
			</td>
		</tr>
		</table>
	</td>
</tr>
</table>
</body>
</html>


</body>
</html>
 
Sort by date Sort by votes
I would have thought the best way to do it would be to build a SQL string a bit at a time based on what parts of the form are selected then execute it at the end:

strSQL=""
strSQL=strSQL & "SELECT * FROM MyTABLE WHERE "

If Request.Form("P_406")="Brass Bushing" Then
strSQL=strSQL & "TYPE='BRASS BUSHING' OR "
End If

If Request.Form("P_406")="Threaded Insert" Then
strSQL=strSQL & "TYPE='Threaded Insert' OR "
End If


You then need to repeat for the rest of your form elements and at the end execute a command like:

rs.Open strSQL, adoConn

This will execute the SQL query which you have built and load this into your recordset object. However you are likely to run into some problems getting the syntax of the query correct because you are building it on the fly and there is a high chance that if you are not careful you are likely to end up missing 'WHERE's, 'AND's and 'OR's. It is doable but will require very careful thought - hopefully this enough to get started with though.

If this is the wrong approach someone else please say so!

cheers

Ed
 
Upvote 0 Downvote
Not to imply that the above approach is wrong, but I would do it a little differantly.

Basically I would still build a SQL statement as above, but I would build it using some IN statements to take advantage of the fact that they may make multiple selections from checkboxes with the same name. Something like:
Code: 
Dim strSQL
strSQL = "SELECT field1, field2, field3, etc FROM tablename etc WHERE 1=1 "

'do the 406's - radio buttons, only one selection
If Request.Form("P_406") <> "" And Request.Form("P_406") <> "any" Then
   strSQL = strSQL " AND someField = '" & Request.Form("P_406") & "'"
End If

'do the 407's - checkboxes - 0 to multiple selections
If Request.Form("P_407") <> "" Then
   'multiple checboxes comes in as a comma delimited list, so put quotes around the outside and replace the commas with quote-comma-quote to make it a list of quoted strings
   strSQL = strSQL & " AND someOtherField IN ('" & Replace(Request.Form("P_407"), ",", "','") & "')"
End If

'etc

Now two more comments:
1) If you want to use the values from those hidden fields in your SQL statement, i would take out the 1=1 and go ahead andput those portions of the WHERE stmt in at the top.
2) You should always be distrustful of posted data and run at least a Replace(str,"'","''") in case someone spoofed your form and is trying to force a sql injection by entering bad data

 
Upvote 0 Downvote
Tarwn,
Thank you so very, very much for the advice. I tried it and it work. sorry it took so long to thank you for the fabulous advice
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
288
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
265
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
218
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
533
GriffMG
GriffMG
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
197
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top