0

VS 2017 VB.NET 4.6.1

I have created a Data Source using the standard wizard and a stored procedure to fetch the data. I have edited the data source and added an Insert Command using a Stored Procedure to perform the insert. I want to return the Scope_Identity that is generated by the stored procedure to my VB application.

I've added an extra out Output variable using the Parameters Collection Editor and the stored procedure but I'm not having any luck. I do not want to code the entire tableadapter as I have 71 parameters and I do not relish typing them all out.

Perhaps this isn't doable but it doesn't hurt to ask. If anyone has some information or insight I would be most grateful.

Thank you!

GunnerFan420
  • 198
  • 1
  • 12
  • You don't need a parameter. You can select the newly generated ID back into the PK column of the DataRow you inserted from. – jmcilhinney Feb 11 '19 at 21:54
  • @jmcilhinney I'm trying to understand what you are saying. When I run the insert from the codepage it looks like this - `Me.ApGetSingleJobTableAdapter.Insert(71 parameters, some of them transformed from base data)` which doesn't return anything that I can see. Not sure how I could select the ID back into the data row but I would love to learn how. Thank you! – GunnerFan420 Feb 11 '19 at 22:01
  • 1
    Ah, if you're using DBDirect methods then my question is not relevant. It only applies to calling `Update`. We don't want you to post code with 71 parameters either. We want an [MCVE](https://stackoverflow.com/help/mcve). – jmcilhinney Feb 11 '19 at 22:10

3 Answers3

1

Ok I got this working.

  1. Add a new parameter to my insert stored procedure: @job_order as int OUTPUT
  2. In the stored procedure, after the insert statement: SET @job_order = SCOPE_IDENTITY
  3. In the vb.net project edit your dataset, view the properties of the TableAdapter and ensure GenerateDBDirectMethods is set to True
  4. Also in the TableAdapter properties click the dropdown on the InsertCommand and select New.
  5. Change the CommandType to StoredProcedure
  6. Click the dropdown for CommandText and select your stored procedure
  7. In VS 2017 there is an issue where the Parameter list won't populate from the stored procedure. To fix this add dbo. before your procedure name in the CommandText field. e.g. change apAddJob to dbo.apAddJob. Once that change is complete the parameter list should populate.
  8. Open the Parameters collection for the InsertCommand (which should now have values) and verify that the new parameter you added in step one not only shows in the list but has a Direction of InputOutput.
  9. In the codepage for the relevent form make yourself a module level variable (at the top and not inside any methods) as follows: Dim m_NewJobOrder As Integer
  10. Perform the insert using the Insert command for the table adapter adding your new module level variable to the end as follows: Me.ApGetSingleJobTableAdapter.Insert(txtbox1.text, txtbox2.text, (all other values), m_NewJobOrder)
  11. The identity value should now be stored in m_NewJobOrder after insert. Normally I use it to refill the dataset with the newly created record.
GunnerFan420
  • 198
  • 1
  • 12
0

You should be able to return the Scope_Identity() by setting your "extra out Output variable" equal to the Scope_Identity()

Example:

SET @ID = SCOPE_IDENTITY()

Here is an example shown at another Stack Overflow Discussion:

    CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category NVARCHAR(15),
  @CategoryID INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  INSERT INTO Categories (CategoryName) VALUES (@Category)
  SET @CategoryID = SCOPE_IDENTITY()
END

Fetch Scope_Identity

LarsTech
  • 80,625
  • 14
  • 153
  • 225
Allan F. Gagnon
  • 320
  • 2
  • 7
  • Thank you very much. What you've shown above I have in place within the stored procedure but I can't figure out how to configure the tableadapter insert command to catch the returned value and how to access it once it's been caught. Sorry for being thick and I really do appreciate the help. – GunnerFan420 Feb 11 '19 at 22:45
0

Add the line at the end of your Insert Query add the Select SCOPE_IDENTITY()

and change your Execute Mode to Scalar instead of NonQuery.

Here is a really good tutorial:

Microsoft Docs Tutorial

Allan F. Gagnon
  • 320
  • 2
  • 7
  • Thank you but I am not coding the tableadapter I'm using the one in the data source editor. I'm avoiding creating one in code as I have 71 parameters that I don't want to create by hand. Thanks again. – GunnerFan420 Feb 13 '19 at 03:06