asked on

C# Insert Query, @@IDENTITY

What is wrong with my code below? I am attempting to use the Microsoft Enterprise Library (DAAB) with Microsoft access. My insert succeeds but I am uncertain on how to get the @@IDENTITY value back from the new record. The return value is always zero.

public int SaveTicket(string organization, int category, DateTime openDate)
        {
            var database = DatabaseFactory.CreateDatabase();
            var query = "INSERT INTO Ticket ( Organization, Category, OpenDate ) SELECT @Organization, @Category, @OpenDate";
            var command = database.GetSqlStringCommand(query);
            database.AddInParameter(command, "@Organization", DbType.String, organization);
            database.AddInParameter(command, "@Category", DbType.Int32, category);
            database.AddInParameter(command, "@OpenDate", DbType.DateTime, openDate);
         
database.ExecuteNonQuery(command);
  
            var identity = database.GetSqlStringCommand("SELECT @@IDENTITY");
            return (int)database.ExecuteScalar(identity);
        }

Open in new window

p_davis

put that at the end of your inser

.....@OpenDate; Select @@Identity";

and executeScalar on your insert.

Jarrod

try using

var identity = database.GetSqlStringCommand("SELECT SCOPE_IDENTITY()");

Open in new window

CyberUnDead

ASKER

Dear p_davis and zadeveloper I believe both methods are failing because my backend server is Microsoft Access.
Error-1.png
Error-2.png

p_davis

it looks like you need to do it the way you were attempting before but you have to have an autoincrement field in the table for it to work.

p_davis

you also need to use the same connection.

ASKER CERTIFIED SOLUTION

CyberUnDead

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

p_davis

did you try it with an autoincrement primary key? did that not work?

CyberUnDead

ASKER

p_davis:
TicketId is an autoincrement primary key in the Ticket table.

p_davis

how about just running it as a query instead of scalar?

p_davis

of course you will have to modify your return statement but you should be able to pull that new pk out.