Improve company productivity with a Business Account.Sign Up

x
?
Solved

Query Syntax problem

Posted on 2012-03-22
7
Medium Priority
?
207 Views
Last Modified: 2012-03-23
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
Comment
Question by:TownTalk
7 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 37752442
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
 
LVL 18

Expert Comment

by:deighton
ID: 37752465
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
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37752474
Try:

Set NoCount On
Insert into Draw
Set REGISTER=(Select MAX(Register)+1 from Draw)
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37752477
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
 

Author Comment

by:TownTalk
ID: 37754815
@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
 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 total points
ID: 37755504
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
 

Author Closing Comment

by:TownTalk
ID: 37758058
Ok thanks. I'll use an sp.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question