• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 810
  • Last Modified:

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

0
CyberUnDead
Asked:
CyberUnDead
  • 6
  • 3
1 Solution
 
p_davisCommented:
put that at the end of your inser

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

and executeScalar on your insert.
0
 
zadeveloperCommented:
try using

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

Open in new window

0
 
CyberUnDeadAuthor Commented:
Dear p_davis and zadeveloper I believe both methods are failing because my backend server is Microsoft Access.
Error-1.png
Error-2.png
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
p_davisCommented:
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.
0
 
p_davisCommented:
you also need to use the same connection.
0
 
CyberUnDeadAuthor Commented:
This is hack that works, though I would prefer using the @@IDENTITY function.
        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);

            //query = "SELECT @@IDENTITY";
            query = "SELECT TOP 1 Ticket.TicketId FROM Ticket ORDER BY Ticket.TicketId DESC";

            command = database.GetSqlStringCommand(query);
            return (int)database.ExecuteScalar(command);
        }

Open in new window

0
 
p_davisCommented:
did you try it with an autoincrement primary key? did that not work?
0
 
CyberUnDeadAuthor Commented:
p_davis:
  TicketId is an autoincrement primary key in the Ticket table.
0
 
p_davisCommented:
how about just running it as a query instead of scalar?
0
 
p_davisCommented:
of course you will have to modify your return statement but you should be able to pull that new pk out.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now