.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

SCOPE_IDENTITY() returns null after paramerized INSERT

Posted By:      Posted Date: September 19, 2010    Points: 0   Category :Sql Server

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?

View Complete Post

More Related Resource Links

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.

Linq to SQL returns null for DateTime fields.


I have a linq to sql entity class in which I declared some DateTime fileds like this:

[Column(DbType = "smalldatetime")]
public DateTime? StartDate { get; set; }

And this is how I fetch data form repository:

return repository.Logs.OrderByDescending(l => l.LogID).FirstOrDefault();

All non DateTime fields are filled properly but all datetimes are null.

[Column(DbType = "smalldatetime")]

        public DateTime? StartDate { get; set; }

SQL Null Insert fails

I am developing an SSIS package and trying to update an existing table with info from new columns.  In other words, this table already has records for my input CSV file.  But this CSV file has additional columns for these same records.  So I need to keep existing columns in table.  How can I achieve this? I began by using Import/Export Wizard and it did most of hard work for me.  But one of the columns previously defined in this table doesn't allow NULLs.  So although I want these new rows to map into existing table, it seems like this will just overwrite the table values and I get the error below.  How can I fix this? I have a Flat File CSV source, Data Conversoin, and DestinationConnectionOLEDB Error: 0xC0202009 at Data Flow Task 1, Destination - j5c_ALL_DATA2 [138]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'timestamp', table 'berkeley.dbo.j5c_ALL_DATA2'; column does not allow nulls. INSERT fails.". Error: 0xC0209029 at Data Flow Task 1, Destinati

BuildManager.GetType returns null until application pool recycles

Our application uses the Enterprise Library's PropertyProxyValidator class to validate user input (v4.1 - Oct 2008).  On occassion, the code will raise an exception saying the source type cannot be found.  This error persists until the application pools recycle at night, at which point everything works again.  It tends to only affect one of the web front ends at a time (there are two). I tracked down where the exception was being raised, and this is the section of code:Type IValidationIntegrationProxy.ValidatedType { get { if (string.IsNullOrEmpty(this.sourceTypeName)) { throw new InvalidOperationException(Resources.ExceptionNullSourceTypeName); } Type validatedType = BuildManager.GetType(this.SourceTypeName, false, false); if (validatedType == null) { throw new InvalidOperationException( string.Format(CultureInfo.CurrentUICulture, Resources.ExceptionInvalidSourceTypeName, this.sourceTypeName)); } return validatedType; } } (As you can see, the value for this.SourceTypeName is shown in the exception and I've checked that it matches exactly.) We have never been able to reproduce this exception and at this point don't have any ideas left as far as causes.  For a while, my suspicion was that it was related to the Temporary ASP.NET Files folder.  The last

how to insert null value

hi, All   hear is my statement i am trying to insert null value through statement if (txtBankAcNo.Text == "") { sSqlDbCommand.Parameters["@BankAcNo"].Value = null; }                         else { sSqlDbCommand.Parameters["@BankAcNo"].Value = txtBankAcNo.Text; } but is throwing Error: expects the parameter '@BankAcNo' which is not supplied   Regards!   Hitesh    

how to in Insert database NULL if drop down list item is not selected ?

hi,   on my aspx I have the below DDL   <asp:DropDownList ID="ColorDDL" runat="server" DataSourceID="ColorObj"                                                 DataTextField="Size" DataValueField="BustId" Width="150px"                                                 AppendDataBoundItems="True">                                                    <asp:ListItem Value="-1">(Optional)</asp:ListItem>                             &n

Registry OpenSubKey returns null

Hi,It seemed to be the right place to post to.My problem is that when I try to read registry key, I get null value, even if it exists (regedit shows it exists).Application is windows service with Log On As Local System and registry key im trying to read is in HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSIDOn my development machine (x64 win7) it works fine. But in live server (x64 windows server 2008 r2) I get NULL. These operating systems should have similar policies or does Local System on server 2008 have any restrictions i should know about?RegardsMax

Replication Error - Can't Insert Null into MSSubscription_agents.allow_subscription_copy

In attempting to set up a transactional replication subscription via the UI, I keep getting the error that I cannot insert a Null into the MSSubscription_agents table.  Originally, this was on the allow_subscription_copy column.  One resource suggested altering the column to allow Nulls but that only moved the issue to the attach_state and, later, attach_version columns.  My immediate issue was resolved with these changes BUT I'm really concerned that I need to alter a system table in order to allow a fairly simple replication subscription to be configured.  Has anyone run into this issue and is there a better work around available? I'm running SQL 2008 Enterprise Edition SP1 in a 64-bit environment on both the Publisher/Distributor and Subscriber servers.

FindControl returns null

I am trying to fetch two Labels within a Repeater element to attach data to; which Label is assigned which Data Field depends on the result of a Radio Button List, hence why I need to fetch them in the code behind. This is the ASP.NET code: <asp:View ID="vwSportType" runat="server"> <table> <tr> <td rowspan="2"> <span class="boldtext">Select type of sport:</span><br /><br /> <asp:ListBox ID="lbType" runat="server" AutoPostBack="true" OnSelectedIndexChanged="lbType_OnSelectedIndexChanged" Height="300px" Width="12em"> </asp:ListBox> </td> <td width="70%"> <span class="boldtext">Show by: <asp:RadioButtonList ID="rbSportType" runat="server" RepeatDirection="Horizontal"> <asp:ListItem Text="Date, Sport" Selected="True" Value="0"></asp:ListItem> <asp:ListItem Text="Sport,

how to insert null or empty string to datetime var ?

hi i get data from xml file and sometime the date is empty. i have this code:     try { TimeTo = Convert.ToDateTime(R[15].ToString()); }         catch { TimeTo = null ; } but i got error because i cant insert null to datetime var what i can do ? thak's in advance

Why this returns null

hi friends, i have a table AppUser.UsersUserRole that has the following structure: idx auto ur_id int -- user role id id int    -- user id that has the following data:  From Drop Box  when i ran query to returns a csv: declare @csv nvarchar(max) select @csv = @csv + coalesce( convert(nvarchar(max), ur_id) + ',','' ) from AppUser.UsersUserRoles where id = 28 select @csv it returns:  From Drop Box  but there are three records for 28, why it displays null, how do i fix this? thanks

Downloading a document from a sharepoint site to local file system, copy web service returns null

Hi Guys,
               I am trying to get this done for the last 2 days and I am still banging my head to the wall.
All I need to do is to download files from my sharepoint site library that uses integrated windows authentication. I was using copy webservice : getItem method to accomplish this.

CopyService.GetItem(copySource, out myFieldInfoArray, out myByteArray);

Problem : myByteArray is always returned as NULL

Note: I have tried the same with another site that uses forms authentication but the result was the same (NULL !!)

This is my code:


Copy copy = new CopyService.Copy();


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



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

Error at Infopath after deleting a listfield - Cannot insert the value NULL into column 'tp_DocId'



after deleting an unused calculated field id_2 at my sharepoint list I get the following error when creating a new item with my Infopath 2010 form (update item still works):
"The form cannot be submitted.
Exception from HRESULT: 0x80131904"

The joblog tells me:
09/28/2010 13:24:46.96  w3wp.exe (0x093C)                        0x0C7C SharePoint Foundation          Logging Correlation Data       xmnv Medium   Name=Request (POST:http://test-project.xx.intranet:80/project/ideas/_layouts/Postback.FormServer.aspx) f8ffa9c3-f81c-429b-91de-3f76739e446a
09/28/2010 13:24:47.03  w3wp.exe (0x093C)                        0x0C7C SharePoint Foundation          Logging Correlation Data       xmnv Medium   Site=/project f8ffa9c3-f81c-429b-91de-3f76739e446a
09/28/2010 13:24:47.19  w3wp.exe (0x093C)               &nbs

Session returns null


on page load im Assigning a table to session["Tb"]

on changing dropdown i hv to change gridview but thr session["Tb"] returns null...

Could anyone help me to solve tis 


SCOPE_IDENTITY() always returns 1


in my table the PK is RespondentID (auto incremental integer)

 I added the query below into a SQLDataSource control's Inser Statement. 

INSERT INTO [Respondent] ([DateTimeStarted])
VALUES (@DateTimeStarted);

 When I open it in query builder in design view it says "Unable to Parse Query Text". When I run it in code behind (see the code below) the output value for SqlDataSource1.Inser() is always "1".

Where did I do wrong? please help

thanks in advance

Why it returns null (GridView)TemplateContainer.FindControl("gridView")?



I created a new custom field definition.

But on CreateChildControls event it always return null in my components. Where is error? What should I do to make work it properly?


protected override void CreateChildControls()


if (this.Field == null) return;


if (this.Field != null && this.ControlMode!= SPControlMode.Display)

this.MyPrefix = (Label)TemplateContainer.FindControl("MyPrefix");
if (this.MyPrefix == null)
    throw new ArgumentException("Corrupted PermissionControl template - missing ProofHQUserPermissionPrefix.");
this.gridView = (GridView)TemplateContainer.FindControl("gridView");
if (this.gridView == null)
    throw new ArgumentException("Corrupted PermissionControl template - missing GridView.");



All components are defined in template PermissionControl.

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