none
Unable to retrive an Identity value using Strongly Typed DataSet RRS feed

  • Question

  • Hi,
       I'm new to Visual studio 2005. I'm developing a small c# application to store data into database (SQL Server 2005 Express Edition). I used Strongly Typed DataSet for data access. Every thing is going right except retrieving the Identity value.

    Partially I succeeded in retrieving the Identity value. I changed a line in the autogenerated code for the Dataset.Xsd file. Below is the Insert function automatically-generated. I replace the RED BOLD line as BLUE BOLD line.

    [System.Diagnostics.DebuggerNonUserCodeAttribute()]
            [System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
            [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
            public virtual int Insert(string ChiefComplaint, string Description) {
                if ((ChiefComplaint == null)) {
                    throw new System.ArgumentNullException("ChiefComplaint");
                }
                else {
                    this.Adapter.InsertCommand.Parameters[0].Value = ((string)(ChiefComplaint));
                }
                if ((Description == null)) {
                    this.Adapter.InsertCommand.Parameters[1].Value = System.DBNull.Value;
                }
                else {
                    this.Adapter.InsertCommand.Parameters[1].Value = ((string)(Description));
                }
                System.Data.ConnectionState previousConnectionState = this.Adapter.InsertCommand.Connection.State;
                if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
                            != System.Data.ConnectionState.Open)) {
                    this.Adapter.InsertCommand.Connection.Open();
                }
                try {
                    int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
                    return returnValue;
                }
                finally {
                    if ((previousConnectionState == System.Data.ConnectionState.Closed)) {
                        this.Adapter.InsertCommand.Connection.Close();
                    }
                }
            }

    int returnValue = System.Convert.ToInt32(this.Adapter.InsertCommand.ExecuteScalar());

    it is working for some time. But I don't know for somehow reason it is changing to its original line.

    If anybody knows about this plz. let me know.
    camsrs@gmail.com,msrs_it@hotmail.com
    Saturday, January 26, 2008 4:04 PM

Answers

  • Hi Robert,
         Nice to see a reply from you. I didn't change anything else except the line in red color. And I had identified my mistake. Everytime I tried to change the autogenerated GetDBDirectMethod for INSERT command. Next I had wrote a new SQL Query for with SCOPE_IDENTITY() and I set ExecuteMode to Scalar.

    !!! it's working...

    Any how thanks a lot for your response.
    Saturday, February 2, 2008 12:05 PM

All replies

  • If you modify the auto-generated code, you're in for trouble, because VS regenerates this code whenever you make changes in the designer.

     

    But the auto-generated code ought to be working, as long as the stored procedure that it's using for the InsertCommand is properly returning the identity value.  Is it?  Are you using the auto-generated stored procedures?  Have you modified them?

    Sunday, January 27, 2008 3:15 AM
  • Hi Robert,
         Nice to see a reply from you. I didn't change anything else except the line in red color. And I had identified my mistake. Everytime I tried to change the autogenerated GetDBDirectMethod for INSERT command. Next I had wrote a new SQL Query for with SCOPE_IDENTITY() and I set ExecuteMode to Scalar.

    !!! it's working...

    Any how thanks a lot for your response.
    Saturday, February 2, 2008 12:05 PM