HELP: INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...) - Doesnt work

I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it
INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)
I could swear i have done it before. Please help.
LitoOriginally posted by lito
I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

I could swear i have done it before. Please help.

Lito

how about the use of a variable...

declare @.maxid int
select @.maxid = max(myid)+1 from ...

insert (...myID,...) values(...@.maxid...)

might be worth a try|||Is there a reason you are not using an identity field?

If so, then you will need to post your entire insert statement, not just the scrap you have supplied. We need more information about what you are trying to do.

blindman|||rocket39:
thanks for the sugestion, I never tought of that, it works great!.

blindman:
there is a reason why I am not using an Identity filed and that reason is because this database is aprox. 10 old was originaly a DBase. Also I am not the database administrator for this client and not authorised to make any significant changes to it. Plus this is a web based app. and is getting to be huge any change to db means hours of recoding. It is very stupid and frustrating at times.

Thanks for the help.

Lito|||look into setting a transaction lock around the pair of sql statements to fetch the max and then use it to insert a new number

without such a lock you are only asking for trouble|||Originally posted by r937
look into setting a transaction lock around the pair of sql statements to fetch the max and then use it to insert a new number

without such a lock you are only asking for trouble

do you think that TABLOCK would be sufficient?...

SET NOCOUNT ON;
DECLARE @.newID INT;
SELECT @.newID = (MAX(elementID)+1) FROM tblCattle TABLOCK; <<<<<< table LOCKED
INSERT INTO table1 (...values..., myID, ... values)
VALUES (...values..., @.newID, ...values);
INSERT INTO table2 (...values..., myID, ... values)
VALUES (...diff. values..., @.newID, ...diff values);
SET NOCOUNT OFF;|||i don't think that's enough

look into BEGIN TRANSACTION

rudy|||Originally posted by lito
I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

I could swear i have done it before. Please help.

Lito

What about this?

INSERT (... myID, ...) select ...,(select MAX(myID)+1 from ... ), ...|||Originally posted by snail
What about this?

INSERT (... myID, ...) select ...,(select MAX(myID)+1 from ... ), ...
It gives me an error, That I can't have a Select statement in that context... who knows why.