?
Solved

ADO, SQL, and  Access question...

Posted on 1998-09-16
11
Medium Priority
?
236 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
The viewer will learn how to dynamically set the form action using jQuery.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses
Course of the Month15 days, 11 hours left to enroll

850 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