Click here to Skip to main content
15,565,894 members
Please Sign up or sign in to vote.
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["connstring"].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO ImageTable(photo,imagetype,imageName) OUTPUT inserted.photoid VALUES(@photo,@type,@Imgname)", 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
Updated 31-Oct-12 7:04am
v5

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.
 
Share this answer
 
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.
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'
 
Share this answer
 
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..
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900