Solved

C# Insert Query, @@IDENTITY

Posted on 2010-01-11

1 solution

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

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

Join The Community

10 Comments

LVL 22

Expert Comment

by:p_davis

ID: 262873062010-01-11

put that at the end of your inser

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

and executeScalar on your insert.

LVL 13

Expert Comment

by:zadeveloper

ID: 262873152010-01-11

try using

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

Open in new window

Author Comment

by:CyberUnDead

ID: 262959532010-01-12

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

LVL 22

Expert Comment

by:p_davis

ID: 262961382010-01-12

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.

LVL 22

Expert Comment

by:p_davis

ID: 262961522010-01-12

you also need to use the same connection.

Accepted Solution

by:CyberUnDead

CyberUnDead earned 0 total points

ID: 262972022010-01-12

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

LVL 22

Expert Comment

by:p_davis

ID: 262972152010-01-12

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

Author Comment

by:CyberUnDead

ID: 263032382010-01-13

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

LVL 22

Expert Comment

by:p_davis

ID: 263032632010-01-13

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

LVL 22

Expert Comment

by:p_davis

ID: 263032652010-01-13

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

Featured Post

Question has a verified solution.

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

Japy's C# Tutorials :: Part 1 :: Backgrounds and basics

Article by: JapyDooge

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…

Trick when using Array.Contains() C# 2.0

Article by: sonawanekiran

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…

.NET Programming

Network monitoring: how to automate fixing common network or server issues?

Video by: AdRem

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.…

Why Use Three Nodes in a Database Cluster?

Video by: Percona

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…