Solved

Problem in SQL Query in ASP

Posted on 2004-10-21
150 Views
Last Modified: 2010-03-17
I m using Vbscript for asp with access database
In one page I need to fetch the records using the query

SELECT * FROM Data WHERE  CTSNo Like '%2653%'

but this query doesn't fectch the records.
when I change the condition as '%265%
it shows one record which contains 265 string but not the record contaning 2653


when i check the qurey in Access as

SELECT * FROM Data WHERE  CTSNo Like '*2653*'
it shows the records

What is the problem for Asp Page

I think the condition like 'condition%' works well
but the '%condition%' doesn't.

pls tell me urgent
0
Question by:leork2004
    8 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    Are you trying to find an arbitrary sequence of digits in the CTSNo column. Is the CTSNo column numeric? If so, and you want to reteive a range, you might be better off doing where CTSNo >= 26530 and CTSNo <= 26540. Otherwise, the information you provide is not sufficient to troubleshoot. Please provide some sample data and the data definition (column data types). Also, please tell what you are actually trying to accomplish.

    You may see differences in behavior between Access and ASP because they are actually using to different data access methodologies. If CTSNo is a character column, your query SHOULD work ..
    0
     

    Author Comment

    by:leork2004
    CTSNo is character column.
    which contains data like "265/A1,265/A2" ,"2653","265,266"

    but the query doesn't work


    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    Is the column varchar or text? Can you export a few more values from that column and post them here? This sounds really strange and might point to some hidden data problem. Have you, just for the fun of it, tried the query with * instead of %. Maybe for some strange reason ASP is using the old JET driver instead of MSDAC - which I don't really think is possible.
    0
     

    Author Comment

    by:leork2004
    It is Text(200)

    I tried for "*" , it doesn't work in asp but it works in access


    Th Data for the Column

    140
    6155, 6156
    194 37 A1/3B/11
    42/1
    248/1A/1, 261/6, 265/1, 266/1, 267/1, 284/11/1 208/10 to 19
    284
    258A, 258B, 240
    52,&1442
    2653,2623

    If I give search as '%284%'
    it returns only 1 record (5th row)
    but not the 5th & 6th row

    leork


    0
     
    LVL 8

    Accepted Solution

    by:
    leork,

    Thanks for the data, that allows me to perform a real test.  I create a test MS Access database with your data, hocked it up to ODBC, and then ran the following code:

    <%

    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "test", "admin", ""

    strSQLQuery = "SELECT * FROM Data WHERE CTSNo Like '%284%'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3

    do until rs.eof
      response.write(rs("CTSNo") & "<br>")
      rs.movenext
    loop

    rs.Close

    conn.close
    Set conn = Nothing

    %>

    I got the following output, which was as expected (both rows).

    248/1A/1, 261/6, 265/1, 266/1, 267/1, 284/11/1 208/10 to 19
    284

    Maybe there is another error in your ASP code somewhere that has nothing to do with the query.
    0
     

    Author Comment

    by:leork2004
    Thanks sigmacon
       I tried using odbc also but not the page doesn't shows 2 rows.
    I tried another way and It shows the 2 records now .
       I  added following code before do Loop
       if rs.recordcount > 0 then
             rs.MoveLast
             rs.MoveFirst
       end if

    Actually I don't know why it reqd the above code.
       Can u tell the reason why it is needed?
    I will give u full points.






    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    You are probably looping over the result set once before you are outputting it to HTML - that's the only explanantion I have for you having to do rs.MoveFirst. Another one could be the cursor type and lock type you selected. If all you do is paint the recordset to screen, without modifying it, try:

    rs.Open strSQLQuery, conn, 0, 1


    see this for a list of options:
    http://www.w3schools.com/ado/met_rs_open.asp#CursorTypeEnum
    0
     

    Author Comment

    by:leork2004
    Thanks sigmacon
     I  m giving u full points
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Convert websphere application server default chained Certificates from 1024 to 2048 keysize or higher size and also you can change signatureAlgorithm . Please make sure Websphere Application Server fixpack 7.0.0.23 or Above. The following steps a…
    Are you using email marketing software? If not, you're missing out on effortless marketing and the reaching of desired conversion rates through email marketing software.
    This video discusses moving either the default database or any database to a new volume.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now