Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What is the query syntax in ASP code?

Posted on 2006-07-04
6
Medium Priority
?
539 Views
Last Modified: 2011-09-20
In the section of ASP code below, I want to modify the query text: "SELECT * FROM qrySearchSerialNumberList WHERE (User =  '::txtUser::' AND Item =  '::txtItem::')" to find the record with the greatest/maximum AsOf Date but am unsure of the syntax.  The current query works but returns all records where the User, Item, and Serial Number (SN) match.

Thus I need:

1. A reference or tutorial for ASP query syntax so I can understand the section of ASP code below.
2. To modify the ASP query text to obtain the record with the greatest AsOf  (it's a Date) given the User (Customer Number), Item (Model Number), and Serial Number (of the item/Model).

If I were writing the query in VBA for Microsoft Access, it would look like:

SELECT DISTINCT qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN, Max(qrySearchForm.AsOf) AS MaxOfAsOf
FROM qrySearchForm
GROUP BY qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN
HAVING (((qrySearchForm.User)=[txtUser]) AND ((qrySearchForm.Item)=[txtItem]) AND ((qrySearchForm.SN)=[txtSN]))

Thanks, the ASP code follows:

<%
fp_sQry="SELECT * FROM qrySearchSerialNumberList WHERE (User =  '::txtUser::' AND Item =  '::txtItem::')"
fp_sDefault="txtUser=&txtItem="
fp_sNoRecords="No Serial Numbers available for Item."
fp_sDataConn="Database1"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="SN"
fp_sMenuValue="SN"
fp_sColTypes="&User=202&Item=202&SN=202&"
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
0
Comment
Question by:bobbat
[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
6 Comments
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 1600 total points
ID: 17038681
You need to also add the SN match but here is the asof piece:
fp_sQry="SELECT TOP 1 * FROM qrySearchSerialNumberList WHERE (User =  '::txtUser::' AND Item =  '::txtItem::') ORDER BY AsOf DESC"
0
 
LVL 23

Assisted Solution

by:Saqib Khan
Saqib Khan earned 200 total points
ID: 17038779
My example is much faster and better then using Order by Clause..

if you use order by then your query have to loop through the Table, which slows down the performance.

SELECT DISTINCT qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN, Max(qrySearchForm.AsOf) AS MaxOfAsOf
FROM qrySearchForm
where qrySearchForm.AsOf = Max(qrySearchForm.AsOf)
GROUP BY qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN
HAVING (((qrySearchForm.User)=[txtUser]) AND ((qrySearchForm.Item)=[txtItem]) AND ((qrySearchForm.SN)=[txtSN]))


For the Reference head to w3schools.com or Google ASP SQL to find tutorials.
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17039962
adilkhan, very humble I see.

Let's see, doing a group by on 3 fields on a table containing say 1mil rows and then narrowing it down to a few less with user, item and SN vs narrowing down to start with and then from that list, picking up the latest row...

Assuming no indexes anywhere, or an index on User at least, I bet the simplest query is faster.
Besides, how can you teach someone SQL with queries like that?

adilkhan, please explain why you think it's faster and/or better.

Thanks.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 17046749
>>If I were writing the query in VBA for Microsoft Access<<
Can we then assume then that this is for an MS Access database and the the SQL syntax is the MS Access dialect?

>>
SELECT DISTINCT qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN, Max(qrySearchForm.AsOf) AS MaxOfAsOf
FROM qrySearchForm
GROUP BY qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN
HAVING (((qrySearchForm.User)=[txtUser]) AND ((qrySearchForm.Item)=[txtItem]) AND ((qrySearchForm.SN)=[txtSN]))
<<
There is simply no point in doing a DISTINCT as well as using a GROUP BY clause.

"User" is a JET (and MS SQL Server) reserved keyword and needs to be wrapped is square brackets, as in qrySearchForm.[User]

>>To modify the ASP query text to obtain the record with the greatest AsOf  (it's a Date) given the User (Customer Number), Item (Model Number), and Serial Number (of the item/Model).<<
Do you need:
A. The greatest AsOf for each User, Item and Serial Number ?  (resulting in one or more rows for each User, Item, Serial Number)
B. The greatest AsOf for all? (just one row total).

adilkhan,
>>My example is much faster and better then using Order by Clause..<<
Please elaborate and/or take the time to re-read the EE Guidelines and specifically this section:
Avoid criticizing
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi64

Thanks.
0
 

Author Comment

by:bobbat
ID: 17084771
Question 1: Can we then assume then that this is for an MS Access database and the the SQL syntax is the MS Access dialect?
Answer: Yes

Question 2:
Do you need:
A. The greatest AsOf for each User, Item and Serial Number ?  (resulting in one or more rows for each User, Item, Serial Number)
B. The greatest AsOf for all? (just one row total).
Answer: I need the greatest AsOf for the User (its actually a Number like "U1234" assigned to an Organization--sometimes they change their name) given the Item (Model Number) and Serial Number.

Details on the semantic nightmare I inherited:  The User (an Organization) is determined in the login.asp page after a person enters their UserNumber and Password.  The next page, SearchFormItem.asp, now knows the User's (an organization) ID number.  This page is where a person selects an Item (Model Number) that the User sent in for repair.  After a person selects an Item (Model Number),  the SearchFormSN.asp, opens.  This form now knows the User's (an organization) ID number and the Item (model number) they sent in for repair.  This page is where a person selects the Serial Number of an Item sent in for repair.  Since many items have been sent in more than once, the page lists the latest repair data.  This repair data is the transaction with the latest AsOf date.  A transaction may be the event when it was received for repair or the event when it was shipped back to the User (an organization).  After a person selects a serial number, the SearchFormList.asp page appears and lists information like the date, comments,  FedEx tracking number, etc.

The following line of code works fine.:
fp_sQry="SELECT TOP 1 * FROM qrySearchSerialNumberList WHERE (User =  '::txtUser::' AND Item =  '::txtItem::') ORDER BY AsOf DESC"



0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17095688
>>The following line of code works fine.<<
Great! Since you have received a solution, please close the question.  Here's how:
How do I accept a comment?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi68
0

Featured Post

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.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

604 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