ExecuteNonQuery(dbTransaction, stored proc, param array)

Topics: Data Access Application Block
Aug 12, 2008 at 1:14 PM
I am using Enterprise Library 3.1 and SQL 2005 and I cannot seem to get this right.  This is the line giving me errors:
db.ExecuteNonQuery(trans, sqlCommand, dbCommand.Parameters).  I know it is the last parameter because I keep getting errors such as unmatching parameters, but my parameter count and values all look correct.  I have tried db, dbCommand and dbCommand.Parameters.

Stored proc signature:











varchar(50) = NULL


VB.Net VS 2008 code:


Dim db As SqlDatabase = Nothing



Dim sqlCommand As String



Dim dbCommand As DbCommand






'Get connection to database and give it the storedproc name



db = New SqlDatabase(Me.conn)


sqlCommand =



dbCommand = db.GetStoredProcCommand(SqlCommand)


"@atyActivityType", DbType.String, .ActivityType)



"@atySystemGenerated", DbType.Boolean, .SystemGenerated)



"@atyCannedDescription", DbType.String, .CannedDescription)



'Open the connection and keep it open all thru the transaction


Using conn As SqlConnection = CType(db.CreateConnection(), SqlConnection)




'Start of the transaction



Using trans As DbTransaction = conn.BeginTransaction


db.ExecuteNonQuery(trans, sqlCommand, dbCommand.Parameters)


"@atyID", DbType.Int32, 8)



'Return the ID of the newly inserted record




Return db.GetParameterValue(dbCommand, "@atyID")



End Using




End Using


Aug 12, 2008 at 2:07 PM

Can you provide more information about the errors you get?

Aug 12, 2008 at 3:19 PM
Thanks for your quick response.  The first error is

A first chance exception of type 'System.InvalidOperationException' occurred in Microsoft.Practices.EnterpriseLibrary.Data.dll

ex.message is:

The number of parameters does not match number of values for stored procedure

I counted my parms and I have the right number going in - 3.  Then I have one output parm that i want after the execute.

Aug 12, 2008 at 4:21 PM
That's not how parameters work in ADO.NET; you need to add the output parameter before invoking the stored procedure.

Aug 12, 2008 at 4:51 PM
I tried that as well and I get the same error.  So then I got to thinking and changed my signature to db.ExecuteNonQuery(dbCommand, trans) and life is good.  I thought there was only 2 ways to call ExecuteNonQuery using a transaction, but I found the 3rd way.  I just missed it when I was looking at the overrides.  All is good now and I very much appreciate your help.  I knew once I put out the word for help, someone would respond or I would find the error of my ways.  Turns out it was the latter.  Again, much appreciated.