Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO, SQL, and  Access question...

Posted on 1998-09-16
11
Medium Priority
?
233 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
[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
  • 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 

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
 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
The viewer will learn how to dynamically set the form action using jQuery.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

730 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