Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query Syntax problem

Posted on 2012-03-22
7
Medium Priority
?
201 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how the fundamental information of how to create a table.

971 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