Solved

@@IDENTITY return after insert using access 2002

Posted on 2003-11-19
17
4,977 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
 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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 …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now