Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem in SQL Query in ASP

Posted on 2004-10-21
8
Medium Priority
?
151 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
Comment
Question by:leork2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 8

Expert Comment

by:sigmacon
ID: 12379597
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
ID: 12380118
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
ID: 12380236
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:leork2004
ID: 12387976
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:
sigmacon earned 1500 total points
ID: 12389555
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
ID: 12417855
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
ID: 12425708
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
ID: 12432530
Thanks sigmacon
 I  m giving u full points
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

610 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