@@IDENTITY return after insert using access 2002

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?
ScottGutmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DexstarCommented:
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
namasi_navaretnamCommented:
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
ScottGutmanAuthor Commented:
 namasi_navaretnam

i tried what you suggested and got this error

ADO error: Invalid column name 'IDent'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ScottGutmanAuthor Commented:
DEXSTAR:

I get a null list returned.  no columns, no numbers
0
DexstarCommented:
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
Brendt HessSenior DBACommented:
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
ScottGutmanAuthor Commented:
pardon my stupidity, what is the query analyzer
0
ScottGutmanAuthor Commented:
yes my list table does have an identity field, which auto increments and is working correctly
0
DexstarCommented:
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
Anthony PerkinsCommented:
Guys:

"This is my SP in access 2002."

Anthony
0
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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
namasi_navaretnamCommented:
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
ScottGutmanAuthor Commented:
Thanks acperkins.  I learned a lot and appriate your help

Requested them give you some points for your help
0
Anthony PerkinsCommented:
Thanks, I appreciate that.

Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.