Solved

ADO, SQL, and  Access question...

Posted on 1998-09-16
11
222 Views
Last Modified: 2013-12-25
When sending a SQL statement to an Access database to retrieve certain records (in this case, information on products including the description and price), I want Access to return the results in the order of which I send my request...  Example, my SQL statement reads:

SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("9455,3215,5666")

I want it to return 9455 first, 3215 second, and 5666 last.  However, for some reason, it organizes it by the PROD_NO.  So it returns 3215,5666,9455.  This may actually be a problem with ADO organizing the data, but I'm not sure.  Haven't been able to find anything on it for either ADO or Access.  Please help me figure this one out!  Thanks all!

---LSILes
les@livingscriptures.com

p.s.  I do have a valid reason for wanting to do this, believe it or not.  =)
0
Comment
Question by:LSILes
  • 5
  • 2
  • 2
  • +2
11 Comments
 

Author Comment

by:LSILes
ID: 1855082
Edited text of question
0
 

Author Comment

by:LSILes
ID: 1855083
Edited text of question
0
 
LVL 28

Expert Comment

by:sybe
ID: 1855084
Add an ORDER BY to your SQL statement:
SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("9455,3215,5666") ORDER BY PROD_NO DESC

(desc is for "descending")
0
 

Author Comment

by:LSILes
ID: 1855085
Sorry sybe, but this is not what I'm looking for.  This will indeed order the product numbers in descending order like, 9455,5666,3215, but what I wanted was for it to return them in sequential order as I requested them, i.e. 9455,3215,5666.  If you know that, please let me know.  Thanks!

---LSILes
les@livingscriptures.com
0
 
LVL 1

Expert Comment

by:acsmith
ID: 1855086
can't think of an easy way to do this, but the lazy way would be to use 3 different select statements.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:LSILes
ID: 1855087
I think I've got a better idea, which I'm now coding.  First, let me explain why I wanted to do this:

I've got three arrays that I use to track information in a customer's shopping cart... ProdNo, Qty, and LangForm (for language and format of video tapes)...  when I actually pull up the "view shopping cart" page, I use a recordset to get more information, such as the description of the product.  Well, this organizes the product numbers and the descriptions, but I don't have the Qty and LangForm set up to be organized.

Well, what I'm working on now, is just adding the values to all of the arrays ascending by the ProdNo array.  =)  Pain, but oh well...  what must be done, must be done!  =)

However, if anyone out there finds a way to do what I've asked, I'd still be more than happy to give you the points for it!  Good luck!

---LSILes
les@livingscriptures.com
0
 
LVL 1

Expert Comment

by:Patricia080698
ID: 1855088
SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("9455,3215,5666") why are you inserting them as CHARACTERS ? shouldn't it be :

SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN (9455,3215,5666)
0
 
LVL 1

Expert Comment

by:Patricia080698
ID: 1855089
SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("9455,3215,5666") why are you selecting them as CHARACTERS ? shouldn't it be :

SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN (9455,3215,5666)
0
 

Author Comment

by:LSILes
ID: 1855090
Well Patricia, good question...   I just checked (just to make sure) and I do have the PROD_NO field set up as a number field.   Then I checked my code, and well, I made a mistake when I was typing in the question.  I actually have it set up like:

SQLStmt="SELECT PROD_NO,PROD_DESC,PRICE_W,MEDIA_CODE FROM CPSPRODS WHERE PROD_NO IN (" & prodnos & ")

When I typed in the example, I forgot to leave off the quotes.   Sorry.  =)

---LSILes
0
 

Accepted Solution

by:
egerci earned 100 total points
ID: 1855091
You can write 3 diferrent sql string
sql1=SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("9455")
sql1=SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("3215")
sql1=SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("5666")
en then excute these string separately.. :)


0
 

Expert Comment

by:egerci
ID: 1855092
You can write 3 diferrent sql string
sql1=SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("9455")
sql2=SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("3215")
sql3=SELECT PROD_NO,PROD_DESC,PRICE_W FROM PRODS WHERE PROD_NO IN ("5666")
en then excute these string separately.. :)


0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 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

16 Experts available now in Live!

Get 1:1 Help Now