Truncating a request.from string if dataset empty

help with asp and sql server

[disgustipated]

Pre-emptive super thanks to anyone that can help me get a grip on this. I'm creating a simple part number search for a website, the numbers in the part list column are all along the lines of CM1093 and CM30458K, stuff like that. I have the user submit the query into a txtSearch field, it then gets past to a results.asp page which uses a recordset rsSearch to test it against the database. When the users types in CM it will pull up all in the table. When they type in CM1 its pulls up all that start with CM1. However whenever they overshoot a number, it wont find the closest match and it will return an empty dataset. Now here is my question:

How can I code the site so that if an empty dataset is returned it will truncate the query string of numbers and letters they submitted by one place and resubmit that new string until a dataset is returned with a match. I'm pretty sure id speak your glory to all future generations if you could help me with this. Thanks for any help.

[emsaw]

This is a snippet of code from a test i wrote a long long time ago.

it's C# ASP.Net, but I think you should have a pretty easy time converting the basic logic..

Have fun!

-Mark

if(txtSearchCriteria.Text.Trim().Length > 0)
{
int searchLen = 0;
string searchCriteria = txtSearchCriteria.Text.Trim();
string queryBase = "SELECT * FROM Authors WHERE au_lname like ";

SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["Site.Default.ConnectionString"]);
SqlCommand cmd = new SqlCommand(queryBase + "'" + searchCriteria + "%'", con);
SqlDataReader dr;

con.Open();
dr = cmd.ExecuteReader();

searchLen = searchCriteria.Length - 1;
while( (! dr.HasRows) && searchCriteria.Length > 0 )
{
Response.Write(searchCriteria + "<BR>");
Response.Flush();
con.Close();
con.Open();
dr = cmd.ExecuteReader();
searchCriteria = searchCriteria.Substring(0, searchLen);
cmd.CommandText = queryBase + "'" + searchCriteria + "%'";
searchLen -= 1;
}

grdSearchResults.DataSource = dr;
grdSearchResults.DataBind();
con.Close();

}

[aspdaddy]

No built in way of doing that I know of.

You need to test for empty resultset, then trim 1 character off the search string :
newSearch = left(strSearch,len(strSearch)-1)

then repeat until a match is found or the search string is 0 characters long.

This logic would be better handled by an SQL stored procedure to reduce trips to and from the server.