Wanna any idea to generate an ID

Hi all i want your help in this . I'm writing a code in wich the
user can regester. the user can choose to write his ID himself , but if
he forgote to write it I want the database to generate it automatecaly.
So here is what I'm asking for a code by which I can select the
Max Id and then add 1 to it.
If any one can help please reply.I'm using Ms sql server.
Thanks,
Maii


*** Sent via Developersdex http://www.developersdex.com ***

Re: Wanna any idea to generate an ID

developers wrote on 07 apr 2005 in microsoft.public.inetserver.asp.db:

> Hi all i want your help in this . I'm writing a code in wich the
> user can regester. the user can choose to write his ID himself , but if
> he forgote to write it I want the database to generate it automatecaly.
> So here is what I'm asking for a code by which I can select the
> Max Id and then add 1 to it.
> If any one can help please reply.I'm using Ms sql server.
> Thanks,
> Maii
>

"MyTempId"+MyDatabaseAutoincrementValue

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Wanna any idea to generate an ID

Let's say you have a table:

ID int primary key,
name varchar(50)
.....


SELECT MAX(ID)+1 from MyTable



wrote in message news:OzJDJUvOFHA.580@TK2MSFTNGP15.phx.gbl...
>
>
> Hi all i want your help in this . I'm writing a code in wich the
> user can regester. the user can choose to write his ID himself , but if
> he forgote to write it I want the database to generate it automatecaly.
> So here is what I'm asking for a code by which I can select the
> Max Id and then add 1 to it.
> If any one can help please reply.I'm using Ms sql server.
> Thanks,
> Maii
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Re: Wanna any idea to generate an ID

Agoston Bejo wrote:
> Let's say you have a table:
>
> ID int primary key,
> name varchar(50)
> ....
>
>
> SELECT MAX(ID)+1 from MyTable
>
>
Which whill work great until two users run this query simultaneously ....
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Wanna any idea to generate an ID

Bob Barrows [MVP] wrote on 07 apr 2005 in
microsoft.public.inetserver.asp.db:

> Agoston Bejo wrote:
>> Let's say you have a table:
>>
>> ID int primary key,
>> name varchar(50)
>> ....
>>
>>
>> SELECT MAX(ID)+1 from MyTable
>>
>>
> Which whill work great until two users run this query simultaneously ....

Chances are?
[for a website with say 20 visitors a day? 200 a day? 2000 a day?]

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Wanna any idea to generate an ID

Evertjan. wrote:
> Bob Barrows [MVP] wrote on 07 apr 2005 in
> microsoft.public.inetserver.asp.db:
>
>> Agoston Bejo wrote:
>>> Let's say you have a table:
>>>
>>> ID int primary key,
>>> name varchar(50)
>>> ....
>>>
>>>
>>> SELECT MAX(ID)+1 from MyTable
>>>
>>>
>> Which whill work great until two users run this query simultaneously
>> ....
>
> Chances are?
> [for a website with say 20 visitors a day? 200 a day? 2000 a day?]
>
It only has to happen once ... :-)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Wanna any idea to generate an ID

On Thu, 7 Apr 2005 12:24:33 -0400, "Bob Barrows [MVP]"
wrote:

>Agoston Bejo wrote:
>> Let's say you have a table:
>>
>> ID int primary key,
>> name varchar(50)
>> ....
>>
>>
>> SELECT MAX(ID)+1 from MyTable
>>
>>
>Which whill work great until two users run this query simultaneously ....

Which is why when you do the actual insert, you use an If Exists to
check the ID is still valid. :)

Okay, lame. I prefer to use a random ID and check on insert, but you
need a large range of ID's to choose from. Better is never to use an
ID that's autogenerated unless it's done by the database, as in
defining the field as an AutoNumber.

Jeff

Re: Wanna any idea to generate an ID

Jeff Cochran wrote:
> On Thu, 7 Apr 2005 12:24:33 -0400, "Bob Barrows [MVP]"
> wrote:
>
>> Agoston Bejo wrote:
>>> Let's say you have a table:
>>>
>>> ID int primary key,
>>> name varchar(50)
>>> ....
>>>
>>>
>>> SELECT MAX(ID)+1 from MyTable
>>>
>>>
>> Which whill work great until two users run this query simultaneously
>> ....
>
> Which is why when you do the actual insert, you use an If Exists to
> check the ID is still valid. :)
>
> Okay, lame. I prefer to use a random ID and check on insert, but you
> need a large range of ID's to choose from. Better is never to use an
> ID that's autogenerated unless it's done by the database, as in
> defining the field as an AutoNumber.
>
Alternatively, if an autonumber field is out of the question, a
single-record, single-field table to generate the ID's can be used. Since
it's Access, a recordset with a pessimistic lock (this is pretty much the
only situation in which I recommend the use of a recordset to update data in
ASP) can be used to retrieve and increment the value:

In Access, do this:
CREATE TABLE NextID (ID Long)
Then, still in Access:
Insert Into NextID Values (1)


In ASP, do this:

set rs=createobject("adodb.recordset")
rs.cursorlocation=2 'server
rs.cursortype=2 'dynamic
rs.locktype=2 'pessimistic
rs.open "select id from nextid", cn,,,1
newid = rs(0).value
rs(0).value=newid + 1
rs.update
rs.close:set rs=nothing

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Wanna any idea to generate an ID

if you wanna join you are welcome



Thank u all for replaying ,I'm very greatfull.You have been a great help
to me .
Thanx,
Maii

*** Sent via Developersdex http://www.developersdex.com ***