Solved

@@IDENTITY return after insert using access 2002

Posted on 2003-11-19
17
5,000 Views
Last Modified: 2011-09-20
This is my SP in access 2002.

ALTER PROCEDURE dbo.AddList
(@ListTypeID Varchar(255),
@IDNum Varchar(255),
@Client Varchar(255))
AS

INSERT INTO dbo.List
                      (ListTypeID, IDNum, Client)
VALUES     (@ListTypeID, @IDNum, @Client)
SELECT @@IDENTITY as IDent

To run this as a test from access i just double clicked the SP from the list, It prompts me for the info. The data is inserted into the table correctly, but i get an error--

"The stored procedure executed successfully but did not return records."

How do I get the ID of the inserted record?
0
Comment
Question by:ScottGutman
  • 5
  • 4
  • 3
  • +2
17 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9782890
ScottGutman:

> INSERT INTO dbo.List
>                       (ListTypeID, IDNum, Client)
> VALUES     (@ListTypeID, @IDNum, @Client)
> SELECT @@IDENTITY as IDent

Your code is right, but there is something minor throwing you off.  We just have to find it.  Try this:

     ALTER PROCEDURE dbo.AddList
          (@ListTypeID Varchar(255),
          @IDNum Varchar(255),
          @Client Varchar(255))
     AS
          SET NOCOUNT ON
          INSERT INTO dbo.List
          (ListTypeID, IDNum, Client)
          VALUES     (@ListTypeID, @IDNum, @Client)

          SET NOCOUNT OFF
          SELECT @@IDENTITY as IDent

Hope That Helps,
Dex*
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9783059
Try this,

ALTER PROCEDURE dbo.AddList
(@ListTypeID Varchar(255),
@IDNum Varchar(255),
@Client Varchar(255))
AS

INSERT INTO dbo.List
                      (ListTypeID, IDNum, Client)
VALUES     (@ListTypeID, @IDNum, @Client)
SELECT @@IDENTITY as IDent

RETURN IDent
0
 

Author Comment

by:ScottGutman
ID: 9783183
 namasi_navaretnam

i tried what you suggested and got this error

ADO error: Invalid column name 'IDent'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ScottGutman
ID: 9783198
DEXSTAR:

I get a null list returned.  no columns, no numbers
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9783214
Scott:

We need to figure out if the problem is with your stored procedure, or with how you are calling it.  Run the query in the Query Analyzer and see if it gives you anything there.  If it does, then it is how you are calling it.  If it doesn't, then it is with your stored procedure.

Dex*
0
 
LVL 32

Expert Comment

by:bhess1
ID: 9783373
This may seem like a stupind question, but...
What is the table layout for the List table.  It *does* have an identity field, right?
0
 

Author Comment

by:ScottGutman
ID: 9783382
pardon my stupidity, what is the query analyzer
0
 

Author Comment

by:ScottGutman
ID: 9783417
yes my list table does have an identity field, which auto increments and is working correctly
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9783521
It is a program that comes with SQL Server.  On the SQL Server computer, do this:

1) Start -> Program Files -> Microsoft SQL Server -> Query Analyzer
2) Make sure you connect to your SQL database.
3) Run this query:
          EXEC dbo.AddList 'blah', 'blah', 'blah'

And see what you get.  (You may have to change the parameters to something more appropriate).

Dex*
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9784006
Guys:

"This is my SP in access 2002."

Anthony
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 9784026
While there are more efficient ways of doing this, see if you can make the following change (if it is supported in MS Access):

ALTER PROCEDURE dbo.AddList
(@ListTypeID Varchar(255),
@IDNum Varchar(255),
@Client Varchar(255))
AS

SET NOCOUNT ON       -- Add this line
INSERT INTO dbo.List
                      (ListTypeID, IDNum, Client)
VALUES     (@ListTypeID, @IDNum, @Client)
SELECT @@IDENTITY as IDent

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9784058
It looks like you had come to the same conclusion in your other thread:
http://www.experts-exchange.com/Databases/MS_Access/Q_20803355.html

Not very sure how it is in MS Access, but in T-SQL
SET NOCOUNT ON
suppresses the number of rows affected.

So what is happening (when you do not have SET NOCOUNT ON) is that your recordset is "pointing" to the non existent result set (x number of rows affected).  To prove my point temporarily remove SET NOCOUNT ON and do the following:

Set rs = cmd.Execute   ' or however your code reads.  This line points to the message
Set rs = rs.NextRecordset     'This will give you the resultset your are looking for.

I am not suggesting you do the above.  SET NOCOUNT ON is nearly always a good idea (performance boost), but it should illustrate how it works and introduce you to the NextRecordset method.

Anthony
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9784759
Try this

ALTER PROCEDURE dbo.AddList
(@ListTypeID Varchar(255),
@IDNum Varchar(255),
@Client Varchar(255))
AS

DECLARE @Ident

INSERT INTO dbo.List
                      (ListTypeID, IDNum, Client)
VALUES     (@ListTypeID, @IDNum, @Client)
SELECT @Ident = @@IDENTITY

RETURN @IDent

0
 

Author Comment

by:ScottGutman
ID: 9792195
Thanks acperkins.  I learned a lot and appriate your help

Requested them give you some points for your help
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9793419
Thanks, I appreciate that.

Anthony
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

832 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