none
Problems with ExecuteNonQuery RRS feed

  • Question

  • Hi Everyone,

    I am using my own wrapper class for data access layer. The dataaccess class is throwig expception on ExecuteNonQuery. The sql exception message is empty. The data gets properly inserted in database. But after excuting the Stored procedure it throws this exception which has no details on it. Since the "exception.Message" is empty i dont know what to post here for further analysis. Some of the details i have captured are given below. I am using VS2008 with SQL 2008. This is the first time i am re-using the data access class with SQL 2008 and Framework 3.5. I have used it before with SQL 2000 and SQL 2005 and never faced this issue.

    Error Code -2146232060 Integer

    Stack Trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at BulkUpload.HelperSqlConnection.OnExecuteNonQuery(String strQuery) in D:\BulkUpload\DBClass\HelperSqlConnection.vb:line 146

    Thanks

    Thursday, April 2, 2009 7:52 PM

All replies

  • Hi,
    Can you try to look into "InnerException" from Exception object? You might get helpful info from there.


    Hopefully it helps. 
    • Proposed as answer by joejou - MSFTEditor Thursday, April 2, 2009 8:15 PM
    • Unproposed as answer by OmniAdi Thursday, April 2, 2009 8:20 PM
    Thursday, April 2, 2009 8:15 PM
    Answerer
  • Inner Exception is nothing.
    Thursday, April 2, 2009 8:16 PM
  • Where is your stack trace coming from? Did you use try catch to get it? If not, would it be possible you add try catch block for your executenonquery call and then from catch you do something like Console.WriteLine(e.ToString()). Once you get detailed stack, can you paste it on the board?
    If this is what you get from e.Tostring() in the catch block, it would be better to provide your code here and a bit of more detailed scenario vs. environments.

    Friday, April 3, 2009 6:20 AM
    Answerer
  • You are right. I have a try and catch around my executenonquery call. This stack is from that catch block. This is all the details at that catch block.
    System.Data.SqlClient.SqlException was caught
      Class=16
      ErrorCode=-2146232060
      LineNumber=423
      Message=" "
      Number=50000
      Procedure="SP_InsertAllTables"
      Server="XXX"
      Source=".Net SqlClient Data Provider"
      State=2
      StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
           at BulkUpload.HelperSqlConnection.OnExecuteNonQuery(String strQuery) in D:\BulkUpload\DBClass\HelperSqlConnection.vb:line 146
      InnerException:

    Here is my code at the line number 146.
    Protected Overrides Function OnExecuteNonQuery(ByVal strQuery As String) As Integer
            Try
                Dim oCmd As SqlCommand = PrepareCommand(strQuery)
                Return oCmd.ExecuteNonQuery()
            Catch oErr As Exception
                Throw oErr
            End Try
        End Function
    Friday, April 3, 2009 1:38 PM
  • The ErrorCode is general info that couldn't help for finding the root cause here.
    Have you checked EventLog on a server to see if database server log any error related your call? You may also try to use SQL Server Profiler to trace your application to see where it's starting failing. You should get some clue from those approaches.
    On the other hand, relying on error numbers to build logic of your application is not the best way, since error numbers could change. I would suggest catching specific types of exceptions and handling them properly.

    Friday, April 3, 2009 4:36 PM
    Answerer
  • Thanks for the advise. I did exactly that and i got an error while executing the SP. The message is empty. Line number pointed is blank in the SP. This is the message. I think just a little more help and i should be able to get rid of this error. I am giving a condensed form of my SP for further analysis. Line number 423 corresponds to line number 167 here

    Msg 50000, Level 16, State 2, Procedure SP_InsertAllTables, Line 423

    1    SET ANSI_NULLS ON
    2    GO
    3    SET QUOTED_IDENTIFIER ON
    4    GO
    5    
    6    ALTER  PROCEDURE [dbo].[SP_InsertAllTables]
    7    		
    8    		@in_ProcessXML        	ntext,
    9    		@in_ProcessGroupXML		ntext,
    10   		@in_RoleXML				ntext = NULL			
    11   AS
    12   
    13   BEGIN
    14   
    15   SET NOCOUNT ON
    16   /* DECLARE VARIABLES */
    17   
    18   DECLARE
    19   	@TranName varchar(50),
    20    	@Error int,
    21   	@ErrorMsg varchar(50)
    22   
    23   /* ASSIGN VALUES */ 
    24   SELECT 
    25   	@TranName = 'Insert values into tables'
    26   
    27   /* MAIN QUERY */
    28   BEGIN TRANSACTION @TranName
    29   	DECLARE		@hDoc_Process				int
    30   	DECLARE		@hDoc_ProcessGroup			int
    31   		----------------------------Insert Process----------------------------
    32   	EXEC sp_xml_preparedocument @hDoc_Process OUTPUT, @in_ProcessXML
    33   
    34   	INSERT INTO dbo.Process 
    35   
    36   	SELECT 
    37   		A.Version_ID,
    38   		A.Process_ID,
    39   		A.ProcessName,
    40   		A.ProcessDescription,
    41   		A.MethodType_ID,
    42   		A.ParentProcess,
    43   		A.ProcessLevel,
    44   		A.Node1,
    45   		A.Node2,
    46   		A.Node3,
    47   		A.Node4,
    48   		A.Node5,
    49   		A.Node6,
    50   		GETDATE() AS [DateTime],
    51   		A.ProcessTrigger,
    52   		A.ControlPoint,
    53   		A.ControlDescription
    54   	FROM OPENXML(@hDoc_Process, 'ROOT/INPUT', 2)
    55   	WITH
    56   	(
    57   		Version_ID	nvarchar(50),
    58   		Process_ID	nvarchar(50),
    59   		ProcessName	nvarchar(50),
    60   		ProcessDescription nvarchar(max),
    61   		MethodType_ID	nvarchar(50),
    62   		ParentProcess	nvarchar(50),
    63   		ProcessLevel	int,
    64   		Node1	nvarchar(7),
    65   		Node2	nvarchar(7),
    66   		Node3	nvarchar(7),
    67   		Node4	nvarchar(7),
    68   		Node5	nvarchar(7),
    69   		Node6	nvarchar(7),
    70   		ProcessTrigger	nvarchar(50),
    71   		ControlPoint	nvarchar(50),
    72   		ControlDescription	nvarchar(max)
    73   		
    74   	) AS A
    75   
    76   	     
    77   	SET @Error=@@ERROR
    78   
    79   	IF @Error <> 0
    80   		BEGIN
    81   		SET @ErrorMsg='ERROR WHILE INSERTING PROCESS DETAILS'
    82   		 GOTO ErrorHandler
    83   		END   
    84   	EXEC sp_xml_removedocument @hDoc_Process
    85   
    86   	----------------------------Insert Process Group--------------------------
    87   	EXEC sp_xml_preparedocument @hDoc_ProcessGroup OUTPUT, @in_ProcessGroupXML
    88   	INSERT INTO dbo.ProcessGroup 
    89   
    90   	SELECT 
    91   		A.Version_ID,
    92   		A.Process_ID,
    93   		A.Group_ID,
    94   		A.ProcessGroupStatus_ID,
    95   		A.GroupVolume,
    96   		A.GroupVolumeType_ID,
    97   		A.GroupDuration,
    98   		A.GroupDurationType_ID,
    99   		GETDATE() AS [DateTime],
    100  		A.FrequencyType
    101  
    102  	FROM OPENXML(@hDoc_ProcessGroup, 'ROOT/INPUT', 2)
    103  	WITH
    104  	(
    105  		Version_ID	nvarchar(50),
    106  		Process_ID	nvarchar(50),
    107  		Group_ID	nvarchar(50),
    108  		ProcessGroupStatus_ID nvarchar(50),
    109  		GroupVolume	int,
    110  		GroupVolumeType_ID	nvarchar(50),
    111  		GroupDuration	int,
    112  		GroupDurationType_ID	nvarchar(50),
    113  		FrequencyType	nvarchar(50)
    114  		
    115  	) AS A
    116  
    117  	     
    118  	SET @Error=@@ERROR
    119  
    120  	IF @Error <> 0
    121  		BEGIN
    122  		SET @ErrorMsg='ERROR WHILE INSERTING PROCESS GROUP DETAILS'
    123  		 GOTO ErrorHandler
    124  		END   
    125  
    126  	EXEC sp_xml_removedocument @hDoc_ProcessGroup
    127  	------------------------Insert Roles------------------------
    128  	IF (@in_RoleXML IS NOT NULL)
    129  		BEGIN
    130  			DECLARE		@hDoc_Role			int
    131  			EXEC sp_xml_preparedocument @hDoc_Role OUTPUT, @in_RoleXML
    132  			INSERT INTO dbo.[Role]
    133  
    134  			SELECT 
    135  				A.Version_ID,
    136  				A.Role_ID,
    137  				A.RoleDescription,
    138  				GETDATE() AS [DateTime]
    139  
    140  			FROM OPENXML(@hDoc_Role, 'ROOT/INPUT', 2)
    141  			WITH
    142  			(
    143  				Version_ID	nvarchar(50),
    144  				Role_ID	nvarchar(50),
    145  				RoleDescription	nvarchar(max)
    146  			) AS A
    147  
    148  			     
    149  			SET @Error=@@ERROR
    150  
    151  			IF @Error <> 0
    152  			BEGIN
    153  			SET @ErrorMsg='ERROR WHILE INSERTING ROLE DETAILS'
    154  			 GOTO ErrorHandler
    155  			END   
    156  
    157  			EXEC sp_xml_removedocument @hDoc_Role
    158  		END
    159  		
    160  		
    161  COMMIT TRANSACTION @TranName
    162  
    163  END
    164  
    165  			
    166  			/* ERROR HANDLER */
    167  			
    168  	     ErrorHandler: 
    169  			IF (@@TRANCOUNT > 0)
    170  			BEGIN
    171  		        	ROLLBACK TRANSACTION @TranName
    172  			END		
    173      		SELECT @ErrorMsg = @ErrorMsg + ' ' + convert(char(6),@Error)
    174  			RAISERROR (@ErrorMsg,16,2)
    
    Friday, April 3, 2009 5:24 PM