Can't get a number to return using MAX function

[sysr]

I am developing an ASP page that is connected to an ACCESS database. I need to get a maximum value for a number field, add 1 to it and use that number as a key value to start the next record.

10 Set Con = Server.CreateObject("ADODB.Connection&quot
20 Con.Open "accessDSN"

30 sqlInteger="SELECT MAX(OrgID) AS [id] FROM Region"
40 Con.Execute sqlInteger

50 Response.Write(id)
60 id=id+1
70 Response.Write(id)

I can manipulate the database without any problems, so my connection is OK. When I display id on line 50 I get no response, on line 70, I get 1.

There are 5 records in the database and the field is set to be a number. I need to see a number in line 50???

Any help would be greatly appreciated!

 

[Amiel]

The statement that begins with "SELECT ..." will return a recordset.

The value that it returns has to be assigned to a variable within that page in order for you to make use of it in the page.

On line 40, 'conn.execute' does no assignment of the value( recordset) returnd by the call. Therefore the returnd value is not available.

Try this instead:

10 Set Con = Server.CreateObject("ADODB.Connection&quot
11 Set Rst = Server.CreateObject("ADODB.Recordset&quot

20 Con.Open "accessDSN"
30 sqlInteger="SELECT MAX(OrgID) AS [id] FROM Region"
[red]31 Rst.Open sqlInteger, Con [/red]


REM 40 Con.Execute sqlInteger
REM 50 Response.Write(id)
[red]51 Response.Write rs(0) [/red]
' or
[red]52 Response.Write rs("id&quot[/red]


REM 60 id=id+1
[red]61 Dim intID[/red]
[red]62 intID = rs("id&quot + 1[/red]

REM 70 Response.Write(id)
[red]71 Response.Write(intID)[/red]


Hope this is usefull.

Amiel
amielzz@netscape.net

 

[sysr]

Thank you very much, you have actually cleared up a lot for me in your explanation. It sounds like the recordset returned is actually a mini-database (or set of records) taken from the big database and put in memory. I must then pull what I want out of this mini database (set of records - that matched the criteria).

Would you say this is correct?

And thanks again!

 

[Amiel]

Yes, conceptually your discription is on the mark.

However use the term, "database", to refer to the Access .mdb file (the Access database); and use the term, "recordset" to refer to the dynamically created DAO recordset structure that is filled with records from the Access database.... otherwise, people will talk. ;-) Amiel
amielzz@netscape.net