Solved

ADO, SQL, and  Access question...

Posted on 1998-09-16
11
226 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
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.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

830 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