none
Stored Procedure Not Committing When Ran Through Visual Studio RRS feed

  • Question

  • So, I am having a problem with getting a stored procedure to function properly.  The Procedure functions exactly how it is supposed to when executed in SQL Server 2005.  The basics of what it does is takes in an ID number for a table, then copies that row into a new row in the same table and returns the new row's ID number (again, works fine when run in SQL Server).  Now I go to a Dataset in Visual Studio 2005 and try to add a Query from Exisiting Stored Procdure.  It shows that it takes the ID I need it to take, it shows that it is supposed to return a new ID number.  I run the stored procedure and it returns the new ID, but when you check the SQL Server, there is no visible existing row. It has correctly incremented the ID Field for the table, but not actually inserted the row.  I am confused and frustrated as I cannot figure out why this works in SQL Server but NOT in Visual Studio.  Is VS missing a commit function or something?  The code is below.  If someone can let me know what is going on, this is driving me crazy!

     

    USE [genericDB]

    GO

     

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

     

    ALTER PROCEDURE [dbo].[sp_CopyRow_ReturnID]

     

    @RowID Int

     

    AS

     

    BEGIN TRANSACTION

     

    SET NOCOUNT ON;

     

    INSERT INTO tblGeneric1 ([Name], Column1, Column2, Column3)

    SELECT [Name]+ '###', Column1, Column2, Column3

    FROM tblGeneric1

    WHERE RowID = @RowID

     

    INSERT INTO tblGeneric2(RowID, Column1, Column2, Column3)

    SELECT IDENT_CURRENT('tblGeneric1'), Column1, Column2, Column3

    FROM tblGeneric2

    WHERE RowID= @RowID

     

    INSERT INTO tblGeneric3 (RowID, Column1, Column2)

    SELECT IDENT_CURRENT('tblGeneric1), Column1, Column2

    FROM tblGeneric3

    WHERE RowID= @RowID

     

    INSERT INTO tblGeneric4 (RowID, Column1, Column2)

    SELECT IDENT_CURRENT('tblGeneric1), Column1, Column2

    FROM tblGeneric4

    WHERE RowID= @RowID

     

    SELECT IDENT_CURRENT('tblGeneric1) As RowID

     

    COMMIT

    Monday, November 26, 2007 10:52 PM

All replies

  • If it executes and returns new ID, I am suspecting that you are executing SQL statements/SP against one database, but check another one. I believe VS.NET might create multiple copies of the database and this is where you got caught. There is no other reason why it would not work.

     

    Monday, November 26, 2007 11:49 PM
    Moderator
  • That isn't the case because I have demonstrated it is affecting the database I am checking.  I did this by throughing a "COMMIT TRANSACTION" into the end of the stored procedure with a BEGIN and END to start and end it.  This caused SQL Server to through an error after it had made the inserts, and the data showed up in the database. 

    example: (psuedocode / SQL)

     

    BEGIN

    INSERT (column, column)

    VALUES (value, value)

    COMMIT TRANSACTION  -- no other transaction, so this throws an error stating it is missing something

    END

     

     Now, when I have the stored procedure set up properly, it is looking at the exact same database, saying it is executing but not creating any new records, just incrementing the PK for the main table.  So, I can prove that it is calling to the right database, not some other database (as well as this is not the first database interaction we are using.  i have created at leat 4 or 5 datasets for this database with easily 25+ table adapters with complex SQL Queries and databindings).  This truly seems to be some strange commit issue between SQL Server and VS 2005.  If there is not a known issue, maybe someone could look into it and see if there are any settings that might cause this issue or if this is something that can be duplicated elsewhere.

     

    Tuesday, November 27, 2007 7:54 AM
  • Could you post exact error message?

    Wednesday, November 28, 2007 11:06 AM
    Moderator
  • There ISN'T an error, that is part of the problem.  When you execute the stored procedure in SQL Server (2005) it runs fine and perfectly copies the record it is meant to copy.  It even returns the new RowID for the new row it just inserted.  When you create a TableAdapter using that stored procedure and run it, it returns a new RowID (and it actually does properly increment the RowID in the database) but it doesn't copy the row it was supposed to, it doesn't create an empty row, it seems to just increment the RowID.  It doesn't through an error, and if I didn't have the program it is in trying to immediately reload with data from the new row inserted, you would not know that it didn't do what it was supposed to. 

     

    It literally looks like it creates the new Row, does all of its inserts, then somehow either rolls back all the inserts, but leaves the RowID in place, or it deletes the Row and still passes the RowID on.  The strangest thing to me is the incrementing the RowID without even putting an empty row into the database.

    Thursday, November 29, 2007 4:42 AM
  • Hi,

    Can you post the code of the sp? Also please try to execute the sp from ado.net sql commands directly (ie not though tableadapters).

     

    Charles

    Monday, December 3, 2007 6:42 PM
  • For the Stored Procedure code:  See above (that is what I posted all ready)  As to running it through SQL, if you have been reading the posts, I tried it through SQL Server, and it worked.  And now I have to figure out a way to get it working through a Table Adapter.  For work purposes, for specs, it has to be built into a three tier architecture through a Table Adapter then to the front end.

     

    Tuesday, December 4, 2007 12:14 AM
  • Sorry about the code request.  I would still advise to try directly with a sqlcommand just to see if it works.  Sometimes the TA designer does not generate exactly the right code.  Have you tried to run sql profiler to see exactly the request that the app sends to the db? Is this behavior consistant on other computers? 

    I see nothing special about the sp, and since it works when called from the db I guess the problem has to do with the application code. 

     

    Regards,

    Charles

    Tuesday, December 4, 2007 12:40 AM