Solved

ADO, SQL, and  Access question...

Posted on 1998-09-16
11
223 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
advertisement module in core php 4 145
Hovering effect 9 47
modify h2 4 87
How to automatically insert items to sell into Ebay.com 2 26
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

896 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

18 Experts available now in Live!

Get 1:1 Help Now