Trouble creating search form for a database.

[bless]

Hello all i hope i can find some help here.I did some looking and couldn't really find anything, this could be either my eyes didn't see it or i wasn't searching proper context. anyways ive made a database with MSDS's (material saftey data sheets), it consists of a Name, a location link, and an info space. all i need to do is create a search bar so a user can type in the product they want i.e Mr. Clean for example and my database will give them results. whether it parses 3 or 4 letters doesn't matter. if any one knows a good reference or can help me on this it would be greatly appreciated. oh and im using microsoft access and trying to stay with asp the college im working for tends to stick to what they are used to. im more of a designer/css kinda guy so i need the help i can getthankscarlo

[justsomeguy]

So you already have the database, and you need the search function?What you probably want to do is get what they type into the search bar, split the words up by spaces, and create a SQL statement to check both the name and info fields in the database for each of the keywords. Of course, this will require that you enter appropriate keywords for each chemical. You might want an extra 'keywords' field in the database for this purpose. So the chemicals that are a part of Mr. Clean or Formula 409 or whatever would have the product names in either their info or keywords fields.If you will be writing the ASP using Javascript, I can probably give you some code examples to help you out, but if you are using VBscript then I wouldn't be much help with the code. I'm sure someone else can though.

[bless]

So you already have the database, and you need the search function?What you probably want to do is get what they type into the search bar, split the words up by spaces, and create a SQL statement to check both the name and info fields in the database for each of the keywords. Of course, this will require that you enter appropriate keywords for each chemical. You might want an extra 'keywords' field in the database for this purpose. So the chemicals that are a part of Mr. Clean or Formula 409 or whatever would have the product names in either their info or keywords fields.If you will be writing the ASP using Javascript, I can probably give you some code examples to help you out, but if you are using VBscript then I wouldn't be much help with the code. I'm sure someone else can though.

thanks for the reply, yes i already have the database set up, i like your sugesstion on the extra field for keywords, and i would prefer to use javascript rather than VB. So i'll probably only be checking the name field because whoever is going to be using this will know what they are looking for its a matter of them being able to type there chemical correctly in the search, and give them there result. If you could send some example it would be greatly appreciated. thankscdeagazi@confederationc.on.caif anyone else can help i could still use it.

[bsheridan]

Try...

<tr>		  <td class="bodyText"><p>Use the form to search for a client by <b><font color=green>first name, second name or eMail</font></b>...</td>			<%			'u_input is the varible for user input			' the trim function removes any blanks around the the use input			u_input = trim(request.form("u_input"))			'the dropdown varible for user inputted search field			u_field=request.form("u_field")			' If the user entered a value query the db			if u_input <> "" then				' name of the access db				accessdb="client_database"				' name of the table within the access db				table_name="CLIENT"				set rs = server.createobject("ADODB.Recordset")				sql = "select * from "& table_name &" where " & u_field & " like '%%" & u_input & "%%' "				rs.Open sql, objConn				if rs.eof or rs.bof then					response.write "<font size=4pt color=red>No results found...</font>"					observations=0				else				observations=1				'end check for observations				end if			end if 'end check for user input			%>			<%			'If there are observations then display them			if observations > 0 then			%>			<table border="1" width=600>			<tr>			<TD width="1000" class="bluenewshead"><b>First Name</B></TD>			<td width="1000" class="bluenewshead"><b>Last Name</B></TD>			<td width="1500" class="bluenewshead"><b>E-Mail Address</B></TD>			<td width="1000" class="bluenewshead"><b>Select</B></TD>			<tr>			<%			'Write the values			do while not rs.eof			%>			</td>			<td width="150" class="body"><%= rs("firstName") %></td>			<td width="150" class="body"><%= rs("lastName") %></td>			<td width="200" class="body"><%= rs("eMailAddress") %></td>			</tr>			<%			rs.movenext			loop			%>			<%			'end of check of obs for display			end if			%>			<input type="text" name="u_input" value="<%= u_input %>">			<select name="u_field" size="1">			<option <% ' write out all the search fields and select			if u_field = "firstName" or u_field = "" then			response.write "selected "			end if			%>value="firstName">First Name</option>			<option <%			if u_field = "lastName" then			response.write "selected "			end if			%>value="lastName">Second Name</option>			<option <%			if u_field = "eMailAddress" then			response.write "selected "			end if			%>value="eMailAddress">eMail Address</option>			</select>			<input type="submit" value="Submit" id=submit1 name=submit1>			</form>

[justsomeguy]

OK, an example in Javascript might be something like this. I'm not very familiar with Access, so the SQL might be a little different.

<%search_string = Request.form("keywords"); // use whatever field name you use on the search form instead of "keywords"search_terms = search_string.split(" ");sql = "";for (i = 0; i < search_terms.length; i++){  if (sql != "")	sql += " OR ";  sql += "name LIKE '*" + search_terms[i].split("'").join("''") + "*'";}sql = "SELECT * FROM msds WHERE " + sql;if (search_terms.length > 0){  var sqlcon = Server.CreateObject("adodb.recordset");  sqlcon.ActiveConnection = sql_dbcon_string; // check connection-strings.com for an example of a connection string for Access  sqlcon.open(sql);    while (!sqlcon.eof)  {	//display the results	Response.write(sqlcon.fields.item("chem_name").value + "<br>");  //substitute your own field name, show whatever info you want	sqlcon.movenext();  }}%>

[bless]

thanks guys got it going....cheers