SearchString is "string" but db field is numeric. HANGS on lookup.

[CMcC]

HI all.
Have a classic ASP page that takes a searchstring and looks in a table on the server and should bring back any records that are 'like' the searchstring.
Something is going on to where the server just 'hangs' trying to find that data. Only thing I can think of is that the searchstring is a string type and the database field is numeric.
I can search and find on any other field that is character field, but not the "customer number" field. Here is some of what I have and where I am trying to build a search string.

 Dim oConn, SecondName
    Dim oRset, parcel, RecCounter, parcelnum
    Dim sel, iPageSize, AddValue, parcelnodash
    Dim SearchString, CurrentPage
        
    SearchString = uCase(Request.QueryString("DataSearch"))
    SearchString = replace(SearchString, "'", "''")
    	If Len(SearchString) > 0 Then
        Set oConn = Server.CreateObject("ADODB.Connection")
        oConn.ConnectionTimeout = 120
        oConn.CommandTimeout = 120
        oConn.open "DSN=SCTC_TEST;"
      
		sel = "Select * from arh where cust_num like '" _
        & SearchString & "%'"

I have tried Cint(SearchString), I have also tried making the field in the DB a character string, but then the leading spaces have to be entered to find the customer number (the field has a length of 6, but most numbers have a length of 4. So, changing the field to character I can only find the number 6048 by entering '(space)(space)6048'.

Any suggestions? Thanks in advance!

 

[gmmastros]

What type of database are you using? Microsoft SQL Server, Access, Oracle, MySQL, etc....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ChrisHirst]

but then the leading spaces have to be entered to find the customer number (the field has a length of 6, but most numbers have a length of 4

If you make the column a fixed character width ie: char(6) the database will pad any input of less than 6 characters with leading spaces.
For the search pattern you than will need to concatenate a number of spaces (value = spc(6 - len(value)) to the value.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[CMcC]

Hi George.
Its actually so old school that it is a standalone .DBF 'lookup table' - in the process of converting data to SQL Server 2008.

Hi Chris -
let me try what you are suggesting and let you know the outcome! Thanks so much for both of your expertise!
CMCC