?
Solved

C# Insert Query, @@IDENTITY

Posted on 2010-01-11
10
Medium Priority
?
798 Views
Last Modified: 2012-05-08
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
Comment
Question by:CyberUnDead
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 22

Expert Comment

by:p_davis
ID: 26287306
put that at the end of your inser

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

and executeScalar on your insert.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26287315
try using

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

Open in new window

0
 

Author Comment

by:CyberUnDead
ID: 26295953
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
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

 
LVL 22

Expert Comment

by:p_davis
ID: 26296138
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
 
LVL 22

Expert Comment

by:p_davis
ID: 26296152
you also need to use the same connection.
0
 

Accepted Solution

by:
CyberUnDead earned 0 total points
ID: 26297202
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
 
LVL 22

Expert Comment

by:p_davis
ID: 26297215
did you try it with an autoincrement primary key? did that not work?
0
 

Author Comment

by:CyberUnDead
ID: 26303238
p_davis:
  TicketId is an autoincrement primary key in the Ticket table.
0
 
LVL 22

Expert Comment

by:p_davis
ID: 26303263
how about just running it as a query instead of scalar?
0
 
LVL 22

Expert Comment

by:p_davis
ID: 26303265
of course you will have to modify your return statement but you should be able to pull that new pk out.
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

770 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