how can i move to the last record of recordset

Thread view:

ashwinijain - 04 Dec 2006 13:28 GMT

i have one table in MS. Access in which i am storing company details.
i want to create an unique company id for each company.
so i want to aceess the last row in the table to generate next id
I am using last() method but its throwing sqlexception.
rs=stat1.executeQuery("Select Company_id from Company ");

// Code
if(rs!=null)
{
rs.last();
no=rs.getInt(1) + 1;
}
else
no=1;

Reply to this Message

jcsnippets.atspace.com - 05 Dec 2006 13:18 GMT

> i have one table in MS. Access in which i am storing company details.
> i want to create an unique company id for each company.
[quoted text clipped - 10 lines]
> else
> no=1;

Hi,

Isn't that a bit overkill? You're (possibly) selecting a lot of records,
just to get the last one.

It's better to perform a "select max(company_id) from company" and work
from there - this way you'll avoid retrieving a huge resultset.

Please keep in mind that this is not a safe method: what happens when two
threads perform the same thing? They both select the same max value, add
one to said value, and then one of them will try to insert a duplicate
key!

In Oracle, I've always used sequences - Access has some sort of auto
increment field, IIRC. It's probably better to use that feature.

Best regards,

JayCee

Signature

http://jcsnippets.atspace.com/
a collection of source code, tips and tricks

Reply to this Message

Lew - 05 Dec 2006 18:51 GMT

> In Oracle, I've always used sequences - Access has some sort of auto
> increment field, IIRC. It's probably better to use that feature.

The use of auto-increment fields as artificial keys is controversial. Some DB
gurus excoriate the practice in favor of semantically valid keys.

Even if you favor it, there are considerations, such as coordinating values
through an unload and reload of the data. Be careful.

- Lew

Reply to this Message

Till - 17 Dec 2006 10:30 GMT

Am Mon, 04 Dec 2006 05:28:09 -0800 schrieb ashwinijain:

> i have one table in MS. Access in which i am storing company details.
> i want to create an unique company id for each company.
[quoted text clipped - 10 lines]
> else
> no=1;

you could use "order by company_id asc" in the sql-statement and take
the first row of the resultset.

concerning the Exception: are there any records in the resultset ?

Reply to this Message

how can i move to the last record of recordset

Free Magazines