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

Top 5 Contributors of the Month
Post New Web Links

Scope_Identity() always returns 0 and not the Identity

Posted By:      Posted Date: October 24, 2010    Points: 0   Category :ASP.Net

I have a Stored Proceedure which DID return the Scope_Identity when this was executed in SQLServer 2000.

Now I am porting this over to SQLServer 2008, and this always returns 0, and not the Scope_Identity which it should. 

Here is the Stored Proceedure which will pass null for the parameter @pProjId for an insert:

CREATE proc dbo.hip_InsUpdProj
@pProjId	int,
@pProjName	varchar(255),
@pProjDesc	varchar(1000),
@pProjNotes	varchar(8000),
@pProjDate	datetime,
@pProjType	int

	@RC	int,
	@ROWS	int

if @pProjId = null
	insert into hip_Project (ProjectName, ProjectDesc, ProjectNotes, dtCreated)
	values (@pProjName, @pProjDesc, @pProjNotes, @pProjDate)
	update hip_Project
	set ProjectName = @pProjName,
		ProjectDesc = @pProjDesc,
		ProjectNotes = @pProjNotes,
		dtCreated = @pProjDate,
		deleted = @pProjType
	where Project_Id = @pProjId

select @RC = @@error
if @RC <> 0
	return @RC


if @pProjId = null
	SELECT @pProjId as newProjectID
return 0


Here is the script which creates the hip_Project Table:



CREATE TABLE [dbo].[hip_Project](
	[Project_Id] [int] IDENTITY(200,1) NOT NULL,
	[ProjectName] [nvarchar](

View Complete Post

More Related Resource Links

Scope_Identity() always returns 0 and not the Identity in SQLServer 2008


I have a Stored Proceedure which DID return the Scope_Identity when this was executed in SQLServer 2000.

Now I am porting this over to SQLServer 2008, and this always returns 0, and not the Scope_Identity which it should. 

Here is the Stored Proceedure which will pass null for the parameter @pProjId for an insert:

CREATE proc dbo.hip_InsUpdProj
@pProjId	     int,
@pProjName	varchar(255),
@pProjDesc	varchar(1000),
@pProjNotes	varchar(8000),
@pProjDate	datetime,
@pProjType	int

	@RC	int,
	@ROWS	int

if @pProjId = null
	insert into hip_Project (ProjectName, ProjectDesc, ProjectNotes, dtCreated)
	values (@pProjName, @pProjDesc, @pProjNotes, @pProjDate)

User.Identity.Name returns blank

Hello, I have a strange problem when trying to get the user name. When I run the asp.net page from local machie it works, but when I run it from the server it doesn't. It's very strange, I have tryied with the following functions: Page.User.Identity.Name System.Web.HttpContext.Current.User.Identity.Name My.User.Name System.Threading.Thread.CurrentPrincipal.Identity.Name User.Identity.Name   All options returns blank :s when I run it in the server Anyone have had the same problem, that could help me please? Thanks in advance.    Also I have the following in web.config <identity impersonate="false"/>    <authentication mode="Windows"/>    <authorization>      <allow users="*" />      <deny users="?" />    </authorization>  and in the IIS enabled  the anonymus authentication and enabled windows authentication  

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?

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

User.Identity.Name returns app pool user instead of domain user


2 server web farm windows 2008 r2 nlb.  In IIS I enable windows authentication all other authentication is disabled.


code behind

       protected void Page_Load(object sender, EventArgs e)
            if (!Page.IsPostBack)
                Response.Write("Auth user: " + Request.ServerVariables["Auth_User"]);
                Response.Write("Logon user: " + Request.ServerVariables["LOGON_USER"]);


        <authentication mode="Windows" />        
            <deny users="?" />            

        <compilation debug="true" targetFramework="4.0" />

The code returns the application pool custom account e.g. DOMAIN\WebApp_user.  What I want is the logged in user's username.  How do I get this? 

Retreiving @@IDENTITY from SQL Server CE returns DBNULL



  I want to get the value for Identity column from the inserted session.

Right now Im using SELECT @@IDENTITY

Can somebody help me out to get the Identity value of the inserted record?

Here is what I am doing..

 int id = -1;



                bool getIdentity = true;

                var db = DatabaseFactory.CreateDatabase(EnvironmentManager.CurrentEnvironmentKey);

                using (var dbCommand = db.GetSqlStringCommand(query))


                    dbCommand.CommandType = CommandType.Text;

                    foreach (var param in dbParams)


                        if (param.ParameterName.ToLower() == "@id")


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.

Pass ASP.NET membership identity object across domain?


I am trying to figure a way to SSO with ASP.NET membership and role model.

I can implement custom membership provider which consume web services hosting on a server.

With encryption of data. No problem.

But my question is, If I sign on a website with my provider, got my identity object on that application,

could I pass it to another website on another domain which use the same provider and by doing so, do not need to login again?

If this is possible, I am going to implement this solution. Please tell me what's your take on this.

Federated Identity: Passive Authentication for ASP.NET with WIF


The goal of federated security is to provide a mechanism for establishing trust relationships between domains. Platform tools like Windows Identity Foundation (WIF) make it much easier to support this type of identity federation. We show you how.

Michele Leroux Bustamante

MSDN Magazine August 2010

AD FS 2.0 in Identity Solutions: Using Active Directory Federation Services 2.0 in Identity Solution


This article explains how you can use Active Directory Federation Services (AD FS) 2.0 to claims-enable Windows Communication Foundation (WCF) services and browser-based applications. The focus is on the token issuance functionality in AD FS 2.0. You'll find out how to use AD FS 2.0 as an identity provider; set up an AD FS 2.0 security token service (STS) to interact with WCF; federate AD FS 2.0 with your custom STS or another AD FS 2.0; enable Web single sign-on and federation with WS-Federation and SAML 2.0 protocols; and externalize authentication logic through Visual Studio. You'll come away appreciating how AD FS 2.0 and Windows Identity Foundation make programming identity solutions in Windows less of a chore.

Zulfiqar Ahmed

MSDN Magazine November 2009

Security Briefs: Exploring Claims-Based Identity


Keith Brown introduces you to the new identity model in the Microsoft .NET Framework 3.0.

Keith Brown

MSDN Magazine September 2007

Identity: Secure Your ASP.NET Apps And WCF Services With Windows CardSpace


Windows CardSpace replaces traditional authentication with a more consistent and streamlined login process and improves trust between end-users, applications and services. Michèle Leroux Bustamante explains.

Michele Leroux Bustamante

MSDN Magazine April 2007

sqldatasource's filter returns too few records


I have a puzzling issue.  This is in asp.net 2.0, using vb.  I have a gridview that is bound to a sqldatasource.  Here is the datasource code:

<asp:SqlDataSource ID="fellowsSqlDataSource" runat="server"
        ConnectionString="<%$ ConnectionStrings:PFPFellowsConnectionString %>"
        ProviderName="<%$ ConnectionStrings:PFPFellowsConnectionString.ProviderName %>"
        FilterExpression="{0}"  >
            <asp:SessionParameter Name="psuaccessid" SessionField="username" />         

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; }

Adding carriage returns between 2 dynamically created controls in an HtmlTableCell


The title pretty much explains it. Here is what i'm trying to do...

 Private Sub LoadGrid_CommentsField(ByVal Row As HtmlTableRow, ByVal BookingNumber As String, ByVal CommentsText As String)
  Dim Txt As TextBox
  Dim Cmd As ClickOnceButton.ClickOnceButton

  Txt = New TextBox
  Txt.ID = "txtComment_" & BookingNumber
  Txt.CssClass = "TFAppControls"
  Txt.TextMode = TextBoxMode.MultiLine
  Txt.MaxLength = 500
  Txt.Width = New Unit(95, UnitType.Percentage)
  Txt.Text = CommentsText

  Row.Cells(cComments).InnerHtml &= "<br/>"

  Cmd = New ClickOnceButton.ClickOnceButton
  Cmd.DisableAfterClick = True
  Cmd.Text = "Save Comments"
  Cmd.ID = "cmdSaveComments_" & BookingNumber
  Cmd.CssClass = "TFAppButtons"
  Cmd.EnableViewState = False
 End Sub

On the line where i try and update the InnerHtml, i get the following error; "Cannot get inner content of  because the contents are not literal."

Picker returns nothing for BDC column in custom list


Playing with the BDC for the first time, running into some issues adding a Business Data column to a custom list.

I believe my ADF file is solid. I've been able to import that just fine and can use the application to drive various BDC-specific webparts (a Business Data List or Business Data Item, for example). It also seems to work when I manually enter information in my NewForm.aspx field and click the Check Names button (it verifies my entry and underlines it, creates link to profile when I view the list data).


If I try to use the picker, I get nothing. Enter a name I know is valid or a % and click the magnifying glass icon and I very briefly get the "Please Wait" message, then just an empty set with "Type into the search box above then press "Enter" to start your search" where my results should be.

Any pointers? Thanks.

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