SQL statement to insert a row and output the identity value in C#

Question

0.00/5 (No votes)

See more:

Hi,

I have functionality where I need to insert an image into sql table and get the identity key of the row inserted. Here is the C# code block where I do my insert:

C#

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[&quot;connstring&quot;].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(&quot;INSERT INTO ImageTable(photo,imagetype,imageName) OUTPUT inserted.photoid VALUES(@photo,@type,@Imgname)&quot;, conn))
    {
        cmd.Parameters.Add("@photo", System.Data.SqlDbType.Image).Value = myData;
        cmd.Parameters.Add("@type", System.Data.SqlDbType.VarChar, 20).Value = type;
        cmd.Parameters.Add("@Imgname", System.Data.SqlDbType.VarChar, 253).Value = imageName;
        imageId = (int)cmd.ExecuteScalar();
    }
    conn.Close();
}

While I am executing this statement I am getting an error:
The target table 'ImageTable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

I have a trigger on that table in DB.
Can any one tell how I can solve this issue.
Thanks in advance.
Scorpion

Posted 31-Oct-12 5:58am

scorpion_dev

Updated 31-Oct-12 6:04am

fjdiewornncalwe

v5

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon_Whale · Answer 1 · 2012-10-31T07:27:00

Solution 2

With what Marcus has suggested I would also look at the @@IDENTITY keyword

further reading @@IDENTITY keyword[^]

--

Actually, the SCOPE_IDENTITY() function would be rather more suitable. If the trigger that blocks the first approach (though I've never seen that approach before!) does any inserts in tables with identity columns, @@IDENTITY will reflect the identity of the "globally last inserted" row - NOT the row you care about.

The SCOPE_IDENTITY() function returns the identity of the last inserted row by a statement in the same scope as the function call itself.

Posted 31-Oct-12 7:27am

Simon_Whale

Updated 1-Nov-12 3:45am

v2

Comments

fjdiewornncalwe 1-Nov-12 9:26am

Because @@IDENTITY isn't scope specific, the potential(although remote) of an invalid result being returned are greater with @@IDENTITY vs SCOPE_IDENTITY. That's my only reason for choosing the latter. +5 BTW.

Simon_Whale 1-Nov-12 13:02pm

Thanks for that Marcus, I've re-read what you have said and agree with you

Nelek 1-Nov-12 10:28am

@dojohansen
Please, don't edit solutions to add your own opinion or text. The reason of the edits is to correct or to format the text, so it is easier to read. If you own the solution or the question, then add all the content you want to, but don't write things in the name of others. If you want to say anything related... then use the "have a question or comment" widget (as the rest do)

Original answer of Simon_Whale ends with the Link, the second part was added by dojohansen

dojohansen 1-Nov-12 10:55am

> the reason of the edits is to correct or format the text

Maybe. I just saw the fat green link saying "improve solution", and I reckoned this is what CP wants users to do. So I improved it. It did feel a bit odd, which is why I chose not to modify it, but instead add to it.

> don't write things in the name of others

I didn't, and I can't. Did you notice it says "posted" by Simon_Whale and "eited" by dojohansen? If you think it matters much who wrote which bit, there's the versions link (currently reads v2) which will show you the history.

Look, I'll do this in whatever way CP wishes. But I have no idea who you are and I don't know if your word is in any way representative or authoritative in this respect. I do see that the whole voting thing may be more difficult if people can edit each other's posts, but (a) popular opinion counts for just about nothing to me, and (b) it seems to me this is true no matter *what* guidelines users adhere to.

In a case like this, what do you think I should have done? My point was and is simply that @@identity is normally to be avoided (unless somehow you can know both that no triggers are present and that none will ever be), and to explain why one should prefer scope_identity. A comment? Another solution? Something else?

Simon_Whale 1-Nov-12 11:07am

Personally I feel that the best way would of been to alert me to the mistake / incorrect answer in the form of a comment so that everyone can learn from it even me, rather than editing my answer to include the more appropriate answer.

dojohansen 1-Nov-12 11:18am

Thanks. Not sure I think this "improve solution" feature will be useful if only formatting can be improved upon, but I do see there may be a problem with it not being immediately obvious who wrote what, and that some may be mislead to thinking you wrote the things I did.

Do feel free to edit your own answer again and remove my additions to it. I now notice another user posted a comment 2 minutes before me which pointed out the same (in fewer words), so the point won't be lost.

Nelek 1-Nov-12 13:27pm

That was my point, not all use the revisions control to check who wrote was.
Other point is what Simon told, to comment an error can be more useful than to correct it directly.

Nelek 1-Nov-12 13:22pm

I am not CP stuff, just another user. I do know about the posted, edited and versions control. I just wanted to let you know that the edition is not usually used like that.

fjdiewornncalwe · Answer 2 · 2012-10-31T06:07:00

Solution 1

You have a couple of options here.
1) You could write a stored procedure that performs the insert with an OUTPUT parameter. When you have successfully inserted the value you can set the OUTPUT parameter to SCOPE_IDENTITY().
2) You could remove the OUTPUT part of the query and then run a second query after the insert succeeds to retrieve the ID. SELECT Max(ID) FROM ImageTable WHERE imageType='your value' AND imageName='your value'

Posted 31-Oct-12 6:07am

fjdiewornncalwe

Comments

scorpion_dev 31-Oct-12 12:48pm

Hi Marcus,

Thanks for the reply.

I have used the second option you provided, but it was returning null value.
But, I was able to solve it like this:

INSERT INTO ImageTable (photo,imagetype,imageName) VALUES(@photo,@type,@Imgname); SELECT SCOPE_IDENTITY();

which returns the id value as decimal, then I am converting it into int.

Thanks,

Scorpion

damodara naidu betha 1-Nov-12 2:17am

simple and useful..

dojohansen · Answer 3 · 2012-11-01T03:51:00

That is possible, but you now need to use ExecuteScalar to get hold of the value. If performance matters at all, use an output parameter instead.

Along these lines:

C#

cmd.CommandText = "declare @id int OUTPUT; insert T vaules(1, 'abc', 0); set @id = SCOPE_IDENTITY()";

var idParam = cmd.Parameters.AddWithValue("@id", 0); 
idParam.Direction = ParameterDirection.Output;

--

Also consider using stored procs; it is adviseable in many cases.

SQL statement to insert a row and output the identity value in C#

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

SQL statement to insert a row and output the identity value in C#

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us