Using SQL function MAX within ASP code

TwoCoby · October 12th, 2003, 04:36 PM

I'm trying to build a SQL statement within my ASP code that will query my Oracle database but it keeps giving me an error. I don't know what I can't use the MAX function within my SQL statement. Pretty much I want to find the maximum ID number within a column. Here's the information:

I tried the same SQL statement in Oracle SQL *Plus.. and the statement works..... meaning at least I get an answer. Here's what it looks like in SQL *Plus......... -->

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select max(sample_id_no) from C1234567.SAMPLE;

MAX(SAMPLE_ID_NO)
-----------------
 52

______________________________________:)
Now here's my code:

<%
 Option Explicit
 Dim strConnect
%>


<HTML>
<HEAD>
<TITLE>Using SQL's SELECT Command and the ADO Command Object</TITLE>
</HEAD>
<BODY>

<%
 Dim objCommand, objRS
 Set objCommand = Server.CreateObject("ADODB.Command")

 objCommand.ActiveConnection = strConnect
 objCommand.CommandText = "SELECT MAX(sample_id_no) from SAMPLE"

 Dim CommandText
 Response.Write objCommand.CommandText & " "

 'CommandType property to indicate that the command is a SQL statement.
 objCommand.CommandType = adCmdText

 Set objRS = objCommand.Execute
 Set objCommand = Nothing

'Throws an error here
Response.Write objRS("SAMPLE_ID_NO") & " "

 objRS.Close
 Set objRS = Nothing
%>

______________________________________

And here's the error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/SQLSelect.asp, line 31

------------------------------------------------
Any suggestions?

Thanks!
-Coby

urbanPuppy · December 21st, 2003, 07:23 PM

Coby,
There are 2 solutions to your problem.
There error message is the key to the problem.
The reason why it works for you in the SQL Plus is that SQL Plus is returning Exp1 which you can see and is hidden

In "SELECT MAX(sample_id_no) as max_id from SAMPLE" sample_id_no is not returned as the name of the column, therefore you dont have a name for the column so the objRS("SAMPLE_ID_NO") fails.

Solution 1 (easiest)
change the query to "SELECT MAX(sample_id_no) as max_id from SAMPLE"
and change the recordset call to objRS("max_id")
now ASP has a column name to get a handle on.

Solution 2 (faster results)
the RecordSet object can be used a number of different ways. The default way most people (myself included) use it is by column name as you have done here, this works really well when there are many columns returned and they all have unique names. The column name method allows you to change the order columns in the tables and queries without changing the code. It does however get you into some messy overheads as it has to map the name to a column in the result set.

The alternative is to use the column index. Here is a little function i use for those exact queries you are trying to do.

function ExecuteLookup(dsn,query)
dim Connection, Recordset
 On Error Resume Next
 Err.Clear
 Set Connection = Server.CreateObject("ADODB.Connection")
 Connection.Open dsn
 IF Err.Description = "" THEN
 On Error Resume Next
 Err.Clear
 set RecordSet = Connection.Execute(query)
 IF Err.Description <> "" THEN
 response.write "Execute Error "
 response.write Err.Description & " "
 response.write "Query "
 response.write query & " "
 response.write "</body></html>"
 response.end
 END IF
 returnvalue = CStr(Recordset.Fields(0).value)
 ExecuteLookup = returnvalue
 Recordset.Close
 set Recordset = nothing
 ELSE
 response.write "Connection Error "
 response.write Err.Description
 response.write "</body></html>"
 response.end
 END IF
 Connection.Close
 set Connection = nothing
end function

the key thing here is the
set RecordSet = Connection.Execute(query)
returnvalue = CStr(Recordset.Fields(0).value)
statements. These do not depend on the name of the column

Hope this helps