Query Syntax problem
I have a table called Draw. It has an Identity KeyField called DrawingID. One of the other fields is called Register. Somtimes I want to create a new record with a value in the Register field which is 1 higher than the previous maximum.
I can execute the following statement in the a Management Studio Query and get a value returned:
Set NoCount On
Insert into Draw (REGISTER) Select MAX(Register)+1 from Draw
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
But when I try to open an ADODB recordset in MS Access with that same statement, the recordset will not open. I've opened recordsets previously containing compound statements. What is the problem with this one?
I can execute the following statement in the a Management Studio Query and get a value returned:
Set NoCount On
Insert into Draw (REGISTER) Select MAX(Register)+1 from Draw
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
But when I try to open an ADODB recordset in MS Access with that same statement, the recordset will not open. I've opened recordsets previously containing compound statements. What is the problem with this one?
do you not need a semi-colon
Insert into Draw (REGISTER) Select MAX(Register)+1 from Draw;
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
Insert into Draw (REGISTER) Select MAX(Register)+1 from Draw;
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
Try:
Set NoCount On
Insert into Draw
Set REGISTER=(Select MAX(Register)+1 from Draw)
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
Set NoCount On
Insert into Draw
Set REGISTER=(Select MAX(Register)+1 from Draw)
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
MAX(Register)+1 is the WRONG way to go about this.
Setup an identity of (1,1) on register, insert it without specifying and get the number back.
You can run into all sorts of issues (race time conditions) that will leave your program in an unstable state.
More on Identity:
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
Setup an identity of (1,1) on register, insert it without specifying and get the number back.
You can run into all sorts of issues (race time conditions) that will leave your program in an unstable state.
More on Identity:
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
ASKER
@Ged325: This field only sometimes has data in it. I cannot make it an identity field.
@deighton: No it doesn't work with semicolons either
@macarillo1: Your suggestion doesn't work. It says: Incorrect syntax near the keyword 'Set'
@Depking: As a last resort i'll make it a stored procedure, but that reduces the readability of my MsAccess code.
Like I said in my original post, i've done these compound statements previously in MsAccess. It should be possible to make it work.
@deighton: No it doesn't work with semicolons either
@macarillo1: Your suggestion doesn't work. It says: Incorrect syntax near the keyword 'Set'
@Depking: As a last resort i'll make it a stored procedure, but that reduces the readability of my MsAccess code.
Like I said in my original post, i've done these compound statements previously in MsAccess. It should be possible to make it work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok thanks. I'll use an sp.
create procedure dbo.AddNewDrawRecord
as
Begin
set nocount on
insert into dbo.Draw(Register)
select max(Register) + 1 from dbo.Draw
select Register from Draw where DrawingID = scope_identity()
End