SQL provides an easy to use method of determining the largest, or maximum, number in a database. This can get you the largest salary, next available ID number, and much more.

The function is called MAX and it can be used on any numeric field. Let's say you have a table that has one column called writer_id and another column called writer_name. You want to insert a new writer into that table, but you need to get a new ID # for this new writer. So you first need to know the current highest writer's ID number and increment that by one. You would do a SQL query saying

select max(writer_id) as maxid from writers;

The reason that you use the "as maxid" in your SQL syntax is that when you then go to do something with the result, you need a name by which to refer to this value. If you had just said

select writer_id from writers;

you could then say

WriterID = objRec3("writer_id")

but if you're using a function, it's not so easy. You don't have just a column name you want to do something with - you have a result of a function. So you have to give it a name.

So in our above case, using

select max(writer_id) as maxid from writers;

you would then say

WriterID = objRec3("maxid")

and then you'd say

NewID = WriterID + 1

and voila! You would have the next ID to do your new insert with.

Introduction to ASP Ebook Download this ebook to get everything you need to know about learning ASP - from a step by step tutorial to function lists, sample code, common errors and solutions, and much more! 101 pages.