• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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?
0
TownTalk
Asked:
TownTalk
1 Solution
 
DcpKingCommented:
Try making it a stored proc:

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
0
 
deightonCommented:
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();
0
 
macarrillo1Commented:
Try:

Set NoCount On
Insert into Draw
Set REGISTER=(Select MAX(Register)+1 from Draw)
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
0
2017 Webroot Threat Report

MSPs: Get the facts you need to protect your clients.
The 2017 Webroot Threat Report provides a uniquely insightful global view into the analysis and discoveries made by the Webroot® Threat Intelligence Platform to provide insights on key trends and risks as seen by our users.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
 
TownTalkAuthor Commented:
@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.
0
 
DcpKingCommented:
I wouldn't be so sure about being able to make it work - I've come across things in Access that you'd expect to work but which don't.

As for using sprocs, I personally am all in favour of them. I find that they don't diminish readability at all (and anyhow, the comments in the code will explain stuff. Errr. You are commenting your code, aren't you? [grin]).  In addition, using sprocs insulates your user-friendly but not very efficient Access front-end from your user-unfriendly but very efficient SQL Server back-end, so if you need to change things on the server you don't necessarily have to go chasing through all the code of all the Access projects that might use that part of the DB, to see if they need fixing.
 Using sprocs means that you can test them without having to use the Access front end, too, which can speed things up a lot.
0
 
TownTalkAuthor Commented:
Ok thanks. I'll use an sp.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now