Solved

Query Syntax problem

Posted on 2012-03-22
7
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 40

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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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