ADODB Question

[luvgreen]

Greetings! I am new to ADODB area. If I used the SQL statement with MAX
below, the strResult = oRS("DATE_COMPLETED") gives me an error saying "Run
Time Error 3625: Item can't be found in the collection corresponding to the
requested name or ordinal"

sSQL = "SELECT MAX(DATE_COMPLETED) FROM PATRNHIST WHERE EMPLOYEE=7031 AND
(COURSE='PROC-PRP' Or COURSE='PROC-PEREV' Or COURSE='PROC-PRFI')"

BUT, if I removed MAX, it worked fine with following code. I do need MAX,
how should I fix it. Thanks.



Sub try()

Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim myArray
Dim i As Integer
Dim strResult As String

i = 0

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DSN=sralaw;UID=pmguser;PWD=pmgprod"

Set oRS = CreateObject("ADODB.Recordset")
sSQL = "SELECT DATE_COMPLETED FROM PATRNHIST WHERE EMPLOYEE=7031 AND
(COURSE='PROC-PRP' Or COURSE='PROC-PEREV' Or COURSE='PROC-PRFI')"
oRS.Open sSQL, oConn
Do While (Not (oRS.EOF))
strResult = oRS("DATE_COMPLETED")
Worksheets("Sheet1").Range("A" & i + 1).Value = strResult
i = i + 1
oRS.MoveNext
Loop

oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Sub

 

[Harald Staff]

Hi

Max is pretty dedicated non-standard SQL. It works in Access and, I believe,
SQLserver, but for few other standards.

What I usually do is search all (or several, if I can limit the search),
order descending and use the first record only. Like
ORDER BY DATE_COMPLETED DESC

HTH. Best wishes Harald

 

[luvgreen]

Thank you so much for answering my question, Harald.

How can I just take the first record in the code? Thanks.

 

[luvgreen]

Thank you for your answer. Please ignore my question about how to get the
first record. I should have tried before I ask. Thanks. Great help!!!

 

[Tim Williams]

When using aggregate functions the corresponding field in your
recordset will be named (eg) "MAX(DATE_COMPLETED)" and not
"DATE_COMPLETED". That's why ADO is compaining it can't find the
field you requested.

You can either alias your field in the SQL query:
SELECT MAX(DATE_COMPLETED) as MAX_DATE FROM .....

and then use
oRS("MAX_DATE ")

or just access the field by its numeric index
oRS.Fields(0)

Tim.

 

[Jamie Collins]

[MAX] works in Access and, I believe,
SQLserver, but for few other standards.

Jet (MS Access) and T-SQL (SQL Server) are implementations, not
standards. The standards are determined by the independent ANSI
organization, not the product providers. The recognized ANSI standards
are SQL-92 (previous), SQL-99 (current) and SQL-2003 (future),
although there is a strong case to for SQL-92 to still be considered
the current standard.

Max is pretty dedicated non-standard SQL.

Sorry to appear blunt but this is just plain wrong. MAX is certainly
part of the ANSI SQL standards and is fundamental to any SQL
implementation I can think of. See this link to the core SQL-99 set
functions:

http://developer.mimer.com/validator/parser99/core-sql-99.tml

Look for E091-03 MAX.

The earlier standards (e.g. SQL-92) are harder to get for without
having to pay. You can run some simple SQL that uses MAX through
Miner's standard SQL checker (at above linked site) for confirmation
of SQL-92 compliance. For a quick corroboration, see this link to an
article about Jet 3.0 (the most recent implementation is Jet 4.0)
compliance with the SQL-92 standard:

http://www.emu.edu.tr/english/facil...sual Basic 4, Second Edition/ddg05.htm#E69E81

Look for 'The standard SQL-92 aggregate functions are described in the
following list' that includes MAX.

What I usually do is search all (or several, if I can limit the search),
order descending and use the first record only. Like
ORDER BY DATE_COMPLETED DESC

Using ORDER BY to find the first/last/next/previous row is applying a
procedural language mental model (a cursor) to a set-based language
(SQL). The best advice is to use the set functions inherent to the SQL
language, such as MAX. You advised the complete opposite!

Jamie.

--

 

[Rob van Gelder]

You need a column alias.

You're referencing a column (field) assuming it's called "DATE_COMPLETED".
That is not the case when you wrap a MAX around it.
So it can't find that field name in the field collection.

The automatic column label becomes whatever the database chooses (probably
something like "max(date_completed)")

Suggest you tune the SQL string:
SELECT MAX(DATE_COMPLETED) datecomp FROM PATRNHIST

then reference it as:
strResult = oRS("datecomp")

 

[Harald Staff]

Using ORDER BY to find the first/last/next/previous row is applying a
procedural language mental model (a cursor) to a set-based language
(SQL). The best advice is to use the set functions inherent to the SQL
language, such as MAX.

I'm not sure that "the best advice" is found in a list or in a theory at all
times. How old are you ?

You advised the complete opposite!

No. I shared a workaround, what I usually do, in fact /always/ do, to get a
similar result. No need to shout.

HTH. Best wishes Harald

 

[Jamie Collins]

I'm not sure that "the best advice" is found in a list or in a theory at all
times. How old are you ?

Maybe something has been lost in translation (divided by a common
language and all that). For my region and generation, 'best advice' is
the prevailing wisdom rather than something formalised and prescribed.

As an example, have you ever found yourself advising someone in this
ng to avoid unnecessary use of Select and Activate methods? Most of
the regulars would agree and this is what I would consider to be 'best
advice'. Say you saw a post recommending the OP to add a large number
of unnecessary Active and Select methods where the OP had used none.
Sure, it still works, but wouldn't you want to challenge the advice
given?

No need to shout.

Not shouting (that would be FULL CAPS), just expressing incredulity.
Apologies if you thought me impolite. I've assumed that your 'How old
are you?' comment was meant in a non-aggressive way and that your
'best wishes' were sincere.

Jamie.

--