@@IDENTITY return after insert using access 2002

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*

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

 namasi_navaretnam

i tried what you suggested and got this error

ADO error: Invalid column name 'IDent'

DEXSTAR:

I get a null list returned.  no columns, no numbers

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*

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

pardon my stupidity, what is the query analyzer

yes my list table does have an identity field, which auto increments and is working correctly

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*

Guys:

"This is my SP in access 2002."

Anthony

It looks like you had come to the same conclusion in your other thread:
https://www.experts-exchange.com/questions/20803355/IDENTITY-return-after-insert-using-access-2002.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

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

Thanks acperkins.  I learned a lot and appriate your help

Requested them give you some points for your help

Thanks, I appreciate that.

Anthony