Problem in SQL Query in ASP

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
leork2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sigmaconCommented:
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
leork2004Author Commented:
CTSNo is character column.
which contains data like "265/A1,265/A2" ,"2653","265,266"

but the query doesn't work


0
sigmaconCommented:
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
The Lifecycle Approach to Managing Security Policy

Managing application connectivity and security policies can be achieved more effectively when following a framework that automates repeatable processes and ensures that the right activities are performed in the right order.

leork2004Author Commented:
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
sigmaconCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leork2004Author Commented:
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
sigmaconCommented:
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
leork2004Author Commented:
Thanks sigmacon
 I  m giving u full points
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Application Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.