INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

CODE

 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!
 

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

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

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

Quote:

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close