Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle ASP
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Oracle ASP Using ASP with Oracle databases. For Oracle discussions not specific to ASP, please see the Oracle forum. For more ASP discussions, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 12th, 2003, 04:36 PM
Registered User
 
Join Date: Oct 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to TwoCoby
Default Using SQL function MAX within ASP code

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

<!-- METADATA TYPE="typelib"
              FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<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 & "<br>"

  '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") & "<br>"

  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

  #2 (permalink)  
Old December 21st, 2003, 07:23 PM
Registered User
 
Join Date: Dec 2003
Location: Brisbane, Qld, Australia.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 "<b>Execute Error</b><br>"
               response.write Err.Description & "<br>"
               response.write "<b>Query</b><br>"
               response.write query & "<br>"
            response.write "</body></html>"
            response.end
        END IF
        returnvalue = CStr(Recordset.Fields(0).value)
        ExecuteLookup = returnvalue
        Recordset.Close
        set Recordset = nothing
    ELSE
           response.write "<b>Connection Error</b><br>"
           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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Max function to return single rows. beerOne SQL Language 1 November 6th, 2008 04:52 PM
Help writing SQL Statement/ .net code for function carswelljr Classic ASP Databases 2 August 24th, 2006 03:31 PM
Aggregate function MAX creating problem..? Manu SQL Language 4 May 30th, 2006 04:41 AM
Aggregate Function MAX, SUM Manu SQL Language 2 May 30th, 2006 04:40 AM
Problems using the MAX Function Trojan_uk SQL Server ASP 0 November 26th, 2003 11:21 AM



All times are GMT -4. The time now is 06:14 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.