asked on

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

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 ..

leork2004

ASKER

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

but the query doesn't work

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.

leork2004

ASKER

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

ASKER CERTIFIED SOLUTION

sigmacon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leork2004

ASKER

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.

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

leork2004

ASKER

Thanks sigmacon
I m giving u full points