none
Stored Procedure- Return @@IDENTIY, Typed DataSet RRS feed

  • Question

  • Hey everyone,
      I am updating a database by calling a stored procedure that makes a new row and returns the identity of that row.  I let microsoft perform the typed dataset for the database and the stored procedure so all i had to do was call a method.  However everytime I execute the method I get the number 2, not the identity.  Through a lot of debugging I have discovered that the 2 being returned is actually the number of rows affected.  I figured out how to debug the typed dataset code(Just my COde option) and saw that when it creates the command for the SQL it is executing it using command.ExecuteNonQuery() which returns the rows affected.  Now I have had a lot of bad experiences changing code that is generated by the microsoft but I decided to change it anyways to ExecutesScalar() which I thought would just return the return value of the procedure.  Unfortunately I get an exception that says: "Object reference not set to an instance of an object". Which I take to be some sort of null reference problem
      So basically I'm guessing that when I called ExecuteScalar() it was not really defined by the dataset so it would not work.  Does anyone know what I can do so that I can make microsoft generate the typed dataset so that it will return the return value from the stored procedure.  Here is some of the code that is geneared in the typed dataset.


    This is microsoft generated typed dataset code

    Code Snippet

    nt returnValue;
                try {
                    returnValue = (int)command.ExecuteNonQuery();
                   
                   
                }
                finally {
                    if ((previousConnectionState == System.Data.ConnectionState.Closed)) {
                        command.Connection.Close();
                    }
                }
                return returnValue;


    Here is my code calling this code eventually..i is supposed to hold the return value wich i want to be @@IDENTITY.

    Code Snippet

    else
                         i = this.queriesTableAdapter1.CreateScriptureSlide(name, userID, sciptureTypeID, testamentID, bookID, chapterID, verseID);
                     MessageBox.Show(i.ToString());

    Here is the Database code:

    Code Snippet

    ALTER PROCEDURE dbo.CreateScriptureSlide
       
        @Name varchar(25),
        @User    int,
        @ScriptureTypeID int,
        @TestamentID int,
        @BookID int,
        @ChapterID int,
        @VerseID    int
           
    AS
    declare @SlideID int
    exec @SlideID = dbo.CreateSlide @Name,1,@User

    INSERT INTO Scripture
                             (SlideID, ScriptureTypeID, TestamentID, BookID, ChapterID, VerseID)
    VALUES        (@SlideID,@ScriptureTypeID,@TestamentID,@BookID,@ChapterID,@VerseID)
       
        /* SET NOCOUNT ON */
       
        RETURN @@IDENTITY



    Thank you for taking the time to listen.








    Sunday, September 9, 2007 4:04 PM

All replies