AddOutParameter from Microsoft Enterprise Library failing with no erro

[TheBugSlayer]

Hi all,

In my code I have:

...
Dim dataSource As FileConfigurationSource = New FileConfigurationSource("EntLib.config")
Dim dbFactory As DatabaseProviderFactory = New DatabaseProviderFactory(dataSource)
db = dbFactory.Create(Me.DatabaseInstance)
...
db.GetStoredProcCommand("p_Schedule_Templates_INSERT")
With db
...
   .AddInParameter(sqlCommand, "@Last_Updated_By", DbType.String, Me.NetworkUserID)
   .AddOutParameter(sqlCommand, "@Date_Of_Last_Update", DbType.DateTime, 8)
   .AddOutParameter(sqlCommand, "@New_Template_ID", DbType.Int32, 32)
   .ExecuteNonQuery(sqlCommand)
End With
...
txtLastUpdatedBy.Text = Me.NetworkUserID
txtDateOfLastUpdate.Text = CType(db.GetParameterValue(sqlCommand, "@Date_Of_Last_Update"), String)

The code fails upon executing the query, with NO error message, i.e ! I have no clue as to what the cause is. What is the correct size to use with AddOutParameter when the parameter is of date-time type as in .AddOutParameter(sqlCommand, "@Date_Of_Last_Update", DbType.DateTime, 8)? Actually, I would appreciate some explanation on how to decide the maximum size for each data type.

The stored procedure looks like this:

CREATE  PROCEDURE dbo.p_Schedule_Templates_INSERT     
	@Template_Name VARCHAR(255),                                                                    
	@Active_Record_Flag CHAR(1),                                                                    
	@Template_Code VARCHAR(100),                                                                     
	@RVU_Template CHAR(1),                                                                          
	@GAF REAL,                                                                                   
	@GAF_Template VARCHAR(100),
	@Conversion_Factor FLOAT,
        @Last_Updated_By VARCHAR(50),   
	@Date_Of_Last_Update DATETIME OUTPUT,                                                                 
	@New_Template_ID INT OUTPUT

AS
...
DECLARE	@ERRORMESSAGE VARCHAR(1024),
	@TRAPERROR INT,
	@ROWS INT
        

SET	@New_Template_ID = -1
IF	EXISTS
	(SELECT	TOP 1 1
	FROM	Schedule_Templates (NOLOCK)
	WHERE	Template_Code = @Template_Code
	)
BEGIN
        SET @ERRORMESSAGE = '*** Error: <Schedule_Templates> - INSERT Schedule_Templates rectable added '  + CAST(@ROWS AS varchar(10)) +  ' Rows.'
	RAISERROR(@ERRORMESSAGE, 16, 1) WITH SETERROR
	RETURN 
END

SET	@New_Template_ID = 0
SET     @Date_Of_Last_Update = GetDate()
 
INSERT INTO Schedule_Templates(              
	                 
	Template_Name,                  
	Active_Record_Flag,             
	Template_Code,                  
	RVU_Template,                   
	GAF,                            
	GAF_Template,
	Conversion_Factor,
	Last_Updated_By, 
        Date_Of_Last_Update                
        
)
VALUES
( 
	                   
	@Template_Name,                  
	@Active_Record_Flag,             
	@Template_Code,                  
	@RVU_Template,                   
	@GAF,                            
	@GAF_Template,     
	@Conversion_Factor,
	@Last_Updated_By, 
        @Date_Of_Last_Update                
                    
)

--The Windows app. will use @New_Template_ID only if no SqlEx was caught.
SELECT @New_Template_ID = @@IDENTITY
...
--SOME ERROR TRAPPING PERFORMED UNDER HERE

Maybe by the time I get back next week I will figure it out but it took me most of today already. The SP seems OK but I suspect I am missing something re the output parameter because it all ran fine before I added @Date_Of_Last_Update.

The table contains an identity column Template_I, which I obviously don't need to update in my code.

Thanks for your help!

 

[chrissie1]

Does the SP run in query analyzer?

BTW the dbtypes datetime and int32 don't need a length. and the one for int32 is not 32 but 32/8 being 4

Christiaan Baes
Belgium

My Blog

 

[TheBugSlayer]

Yes, the SP runs in QA. I wondered about whether 38(bits) or 4(bytes). There did not seem to be an overloaded version of AddOutParameter that did not expect size but when I get back to it I will try and let you know. Thanks in advance.

Merci monsieur Christiaan.

 

[TheBugSlayer]

.AddOutParameter(sqlCommand, "@New_Template_ID", DbType.Int32, 4)

works.

The call doesn't work without Size for Int32. I have no tried date as I don't need it yet. Will post if/when I do.