Redirecting a page

[DaveRook]

Hi

I am currently using a page redirect which is the wrong thing to do (I think).

I am trying to be clever and work on a search algorythm (if that's the correct term).

We have a series of part numbers that start with 4 - 6 numbers, then the rest are customer specifiable. A good (made up) example could be the part number
Honda-4dr-Dsl-Auto
This is probably quite obvious - A honda, 4 door, Diesel and Automatic.
An example of my part numbers is
SHTSHC-2 - 20 - D - 100 - 04 - G - XX - X - LF

The first part (SHTSHC-2) is in the database and cannot alter. A user can search for just SHTSHC-2 or include more of the information (eg, SHTSHC-2-20-D-100), and my code has to display the correct part.

The only way around this I could see was to count how many characters were in the search query and then try it's initial search. If there are no matches, remove the last letter of the search and try again. And again.

To do this, I am using redirects, which must be the wrong thing to do. I hope I have explained this well enough. I am connecting to an Access db. Is there a better way to refresh the database with out the use of the redirect?

Here is my code

 
<%
'Create and define variables 
Dim rsPCB__dataSearchResult, int_QueryLength
rsPCB__dataSearchResult = "%"
int_QueryLength=0
 
'Request the users query and convert it into variable
If (request.QueryString("data")<>"") Then 
rsPCB__dataSearchResult = request.QueryString("data")
 
		'Removes white space and XX and XXX from search query
	 rsPCB__dataSearchResult = (Replace(rsPCB__dataSearchResult, " ",""))
	 rsPCB__dataSearchResult = (Replace(rsPCB__dataSearchResult, "XXX",""))
	 rsPCB__dataSearchResult = (Replace(rsPCB__dataSearchResult, "XX",""))	 
 
'work out how many characters the variable is and assign to new variable
	int_QueryLength = Len(rsPCB__dataSearchResult)
 
 
'connect to database and send SQL
Dim rsPCB
Dim rsPCB_numRows
 
Set rsPCB = Server.CreateObject("ADODB.Recordset")
rsPCB.ActiveConnection = MM_DataBase_STRING
rsPCB.Source = "SELECT * FROM pcb WHERE ProductNumber like '" + Replace(rsPCB__dataSearchResult, "'", "''") + "' ORDER BY ProductNumber ASC"
rsPCB.CursorType = 0
rsPCB.CursorLocation = 2
rsPCB.LockType = 1
rsPCB.Open()
rsPCB_numRows = 0
 
'if the query doesn't match, remove the last digit of their query until it matches, or until there are no characters in the query left to search
 
		Do while (rsPCB.EOF and rsPCB.BOF or int_QueryLength = 0)
		int_QueryLength = int_QueryLength - 1
		rsPCB__dataSearchResult = Left((rsPCB__dataSearchResult),int_QueryLength)
 
' I don't know how to resend the query with out refreshing the page		Response.Redirect("http://www.xxxxxxx.com/searchresults02.asp?data="& rsPCB__dataSearchResult)
		Loop 
End If
 
%>

Thank you again for your help.

[DrBunchman]

Hi Dave,

You could retrieve a recordset based on the fixed part of your search string. This recordset would contain all records which matched the first, fixed part.Then iterate through the recordset comparing each ProductNumber value with your search string. You can do this as many times as required, reducing the length of the search string each time until you find a record that matches. Doing it this way means you only need to open query the database once which will reduce your overheads.

This could probably be optimised further but off the top of my head I think this would work fine.

Does this make sense?

Dr B