none
Visual stidio 2013 with stored procedure and return value via table adapter RRS feed

  • Question

  • I am a novice on VS 2013. For my learning I make a windows client that communicates with a SQL Server database. The database has stored procedures. When I call a procedure that do an insert I would like to get the primary key back to the windows application, but I don't.

    I have used a tableadapter in Visual Studio 2013. Insert works very well in the database. I manage to call and execute the stored  procedure, but I don't get any primary key data back. I have used this procedure: http://msdn.microsoft.com/en-us/library/37hwc7kt.aspx

    Please tell me if I need any additional code in the tableadapter, and where I should put it.

    Regards


    Tore

    Thursday, March 20, 2014 10:58 PM

All replies

  • The typical solution is to add this line after your INSERT statement in the stored procedure:

        RETURN SCOPE_IDENTITY()

    or

        SELECT SCOPE_IDENTITY() AS TheNewID

    It depends on details.

    Friday, March 21, 2014 6:29 AM
  • private void buttonNyttBilag_Click(object sender, EventArgs e) {

    DataSet1TableAdapters.M_VouchersTableAdapter TA = new DataSet1TableAdapters.M_VouchersTableAdapter(); int @return_value; @return_value = (int) TA.MSP_InsertVoucher(OWNERID, YEARID); }

    Does not work for me. I get a nullReferenceException on the last line of code. My problem is not with the stored procedure itself. I don't think I use the tableadapter correctly.

    Regards


    Tore

    Friday, March 21, 2014 8:09 AM
  • Hi Tore,

    I am moving your thread into the ADO.NET Managed Providers Forum for dedicated support. Thanks for your understanding.

    Have a nice weekend,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 21, 2014 8:48 AM
  • Hello Tore572,

    Could you please share your store procedure? Within the link, there is not a store procedure code.

    And for the error, it may because that the TA is null. You can do check before using it.

    Regards.

    Friday, March 21, 2014 9:41 AM
  • ALTER                 PROCEDURE [dbo].[MSP_InsertVoucher] (@OwnerID as Int, @YearID as int) 
    AS
    declare @Maxno as bigint
    BEGIN
    	set nocount on
        set @Maxno = (select max(VoucherNo) from M_vouchers where ownerid = @ownerid and yearid = @yearid)
    	set @Maxno = isnull(@maxno,0) + 1 /* Necessary for first voucher in new fiscal year*/
    	insert M_Vouchers (ownerid, yearid, voucherno) values (@ownerid, @yearid, @Maxno)
        Return(scope_identity())
    END

    This is what my procedure looks like at the moment.

    Regards


    Tore

    Friday, March 21, 2014 12:43 PM
  • in VS2013 I changed the Dataset table Properties for the Query that inserts into table. I changed ExecuteMode from Scalar to NonQuery and the nullReferenceException mentioned in the beginning of this thread disappeared. But the returned value is still wrong. I get -1 in stead of the inserted primary key.

    Regards


    Tore

    Friday, March 21, 2014 2:40 PM
  • >> I changed ExecuteMode from Scalar to NonQuery and the nullReferenceException mentioned in the beginning of this thread disappeared. But the returned value is still wrong. I get  1 in stead of the inserted primary key.

    Please do not do that, it is already designed to be return the effected row numbers. With the DataSet control, we cannot do what you want.

    Monday, March 24, 2014 11:33 AM