.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Using SCOPE_IDENTITY() to retrieve the id of last SQL Insert

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :ASP.Net


I need to get the id of a record created so that I can store that ifnormation in another SQL table.

A snipet of my c#code is as follows:

string insertSql = "INSERT INTO [Group] ([userid], [groupname]) VALUES (@userid, @groupname); SET @groupid = SCOPE_IDENTITY()";
            using (SqlConnection myConnection = new SqlConnection(connectionString))
                SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                myCommand.Parameters.AddWithValue("@userid", currentUserId);
                myCommand.Parameters.AddWithValue("@groupname", groupTextBox1.Text);
                SqlParameter groupidParameter = ne

View Complete Post

More Related Resource Links

SCOPE_IDENTITY() returns null after paramerized INSERT


I have a very simple database, without any triggers at this point. One of my tbales has a auto increment key (IDENTITY field). As the record has a string attribute, which valeu is received from the user, I insert a new records with a parametized INSERT statement. After insertion I try to fetch the new record's ID with the sttament SELECT SCOPE_IDENTITY(), but ti return null - while SELECT @IDENTITY and SELECT IDENT_CURRENT returns the valid values.

Moreover, if - for testing purposes - I execute the same insert without parameters, (i.e. providing the string value directly in the INSERT statement), then even SCOPE_IDENTITY() returns the correct value.

I didn't find any reference in the documentation regarding such restriction. Is is a known bug or the result of some misunderstading?

How do I retrieve a new row ID immediately upon insert with LINQ?


Hi everyone-

How can I retrieve the row ID immediately as I insert a new record with LINQ? 

I  need to refer back to it to update the row since it will be the only unique data in this particular table ( it's the primary key and the ID  number is auto generated)


How to retrieve ID after Insert command when using DataSet (xsd) ?



I have created a Dataset object (XSD file)  whithin it there is a procedure called InsertWebsite.

In my code, I call this like this:

return Convert.ToInt32(Adapter.InsertWebsite(txtWebsite, txtWebsiteURL, txtWebsiteDescription, txtWebsiteURL2, txtWebsiteURL2Text, txtWebsiteURL3, txtWebsiteURL3Text, DateTime.Now, intLinkTypeID, bitIsLive, txtRecipURL, bitHasPaid, txtSubmitterName, txtSubmitterEmail, txtSubmitterIP, intCategoryID, guidUserID));

The query in the XSD dataset file is:

INSERT INTO [dbo].[tblWebsites] ([txtWebsite], [txtWebsiteURL], [txtWebsiteDescription], [txtWebsiteURL2], [txtWebsiteURL2Text], [txtWebsiteURL3], [txtWebsiteURL3Text], [dteSubmitted], [intLinkTypeID], [bitIsLive], [txtRecipURL], [bitHasPaid], [txtSubmitterName], [txtSubmitterEmail], [txtSubmitterIP], [intCategoryID],[guidUserID]) VALUES (@txtWebsite, @txtWebsiteURL, @txtWebsiteDescription, @txtWebsiteURL2, @txtWebsiteURL2Text, @txtWebsiteURL3, @txtWebsiteURL3Text, @dteSubmitted, @intLinkTypeID, @bitIsLive, @txtRecipURL, @bitHasPaid, @txtSubmitterName, @txtSubmitterEmail, @txtSubmitterIP, @intCategoryID,@guidUserID);SELECT SCOPE_IDENTITY()


When I execute the commans it retrievs value "1" which is not the actual ID genereted by Insert event.....


Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Insert Rows with a GridView

In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Insert Error not helpful



I am using VS2010

When I try to updated or insert a record in my User table I am getting an error that does not make sense to me and I cannot figure out what is wrong.

Also, when I try to insert a new record I get a validation error saying that the Id is required. However it is an identity field in the db so it should not be necessary to send it to the server.

Could you please shed some light?


An error has occurred.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Web.UI.WebControls.EntityOperationException: An error has occurred.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[EntityOperationException: An error has occurred.]
   Microsoft.Web.UI.WebControls.DomainDataSourceView.PerformCudOperation(ChangeSetEntry operation, Action`1 operationPerformingEvent, Action`1 operationPerformedEvent) +331
   Microsoft.Web.UI.WebControls.DomainDataSourceView.UpdateObject(Object oldEntity, O

Detecting Edit or Insert Page


I am coding a .cs file that handles the metadata for a particular table. I am putting a custom validation that allows me to update or insert only if the PubMedID is not already in the database. This would be easy except that the PMID should also be allowed to be null as some of the references in the database were not from PubMed. So i perform a query on the database to see if there is already an identical PMID in the database. This works great when using Insert. However, when using Edit, it does not. The PMID is already in the database because the one currently being edited has the same PMID!!! So I know exactly how to change my query etc. However, I do not know how to tell from inside this Edit/Insert agnostic page, which one it is being called from. Thanks for your help! 

How to retrieve data from SqlDataSource and pass to a variable?


Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />

how To Insert the single quotes in sqlcommand in asp.net


Hi all

    i am developed on simple user registeration application in that i gave  address field .when i am trying to enter something like this (1st main,b'lore) but wont take while inserting.

my query is

sqlcommand cmd=new sqlcommand("insert into registeration_form(name,address)values('"+txtname.text+"','"+txt_address+"')",con);

how can i pass the  quotes also to my query please if any one know reply me.


G Dinakaran

Insert data to Excel using OLEDB



I am trying to insert data to Excel Document using OLEDB as follows:-

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 
values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";
myCommand.CommandText = sql;

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 

values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";

myCommand.CommandText = sql;


But, the Title1 ,Title2, Title3 are in the 3rd row of the Excel Document. So, the data is not inserted.

How to insert Image (jpeg) into MS Word Programmatically using VB.NET


Hi there,

Can someone help me on How to insert Image (jpeg) into MS Word Programmatically using VB.NET..not C#.NET??? Sad

I could hardly find any articles for the coding. Preferably using Bookmark...can i have the sample code.



Win32 Resources: Using C++ to Programmatically Retrieve a Global Cursor's Shape and ID


Getting global cursor information is useful when developing software that drives or gathers information about other UI-based applications, including information about a remote machine. This article describes a way to programmatically identify the current cursor's ID and bitmap at any point in time. The first technique described is based on polling for information and shows how to get the handle of the current global cursor. This handle will then allow you to get information about the cursor. You can also monitor WinEvents for changes to the global cursor.

Dmitri Klementiev

MSDN Magazine October 2001

How do you insert a google map into an ajax updatepanel?


I have built a site that I am using google maps on.  The default page has a google map with info windows containing links and all works great... until a user clicks on the link in the info window.  Doing so takes them to a community page that I have built.  On this community page, I have an ajax update panel that has 2 panels nested inside of it with the visibility set for one to be on and the other off.  The default panel shows community photos, and the second panel I want to show the google map and common directions to the community.  In testing, my buttons to switch the visibility of the panels works great as it should without postback, but when I try to add the map to its panel, it does not show up.  I am new to ajax as well as google maps and I'm not sure why the same code would work fine on my default page but not the community page.  The only obvious thing I can assume is because the community page map is in an update panel.  

How do you insert a google map into and ajax updatepanel and have it work correctly?  Many thanks in advance. 

insert the data in database using gridview jquery



I want to use gridview and bind the data and after i want insert the data in database using jquery,

below the link i got for delete


but i want the sample example for insert  row from gridivew and add the database.


How to retrieve image from DB with Linq and diplay in Image Control


Hi all-


I'm using the file upload to let the user upload a single file.  Here's the code I'm using to then upload to the DB.  How do I reverse this process and dowload the image into the control for it to display on a different page?

Here's my code for the upload - which I think works but can't confirm until I can see the download since all I see in the DB is "binary data"

'update DB with new Image 
        If FileUpload1.HasFile AndAlso FileUpload1.PostedFile.ContentLength > 0 Then
            'Read the file in to a byte Array.
            Dim filebyte As Byte() = FileUpload1.FileBytes
            Dim fileBinary As New System.Data.Linq.Binary(filebyte)
                Dim c3 = (From c In dc.ProductImages _
                          Where c.userID = userGuid _
                          Select c).FirstOrDefault

                c3.productImage = fileBinary

            Catch ex As Exception
                Throw ex
            End Try
Here's my attempt at the retrieval, which isn't working:
                    Dim currentImage = F

How to store and retrieve multiple values in a single session variable .


I want to store employee name,designation and department in session variable and
retrieve in another page how to do this.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend