ExecuteNonQuery(dbTransaction, stored proc, param array)

Topics: Data Access Application Block
Aug 12, 2008 at 2: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:

@atyID

int = NULL OUTPUT,

 

@atyActivityType

varchar(50),

 

@atySystemGenerated

bit,

 

@atyCannedDescription

varchar(50) = NULL

 


VB.Net VS 2008 code:

 

Dim db As SqlDatabase = Nothing

 

 

Dim sqlCommand As String

 

 

Dim dbCommand As DbCommand

 

 

Try

 

 

'Get connection to database and give it the storedproc name

 

 

db = New SqlDatabase(Me.conn)

 

sqlCommand =

"spActivityTypeInsert"

 

dbCommand = db.GetStoredProcCommand(SqlCommand)

 


db.AddInParameter(dbCommand,
"@atyActivityType", DbType.String, .ActivityType)

 

db.AddInParameter(dbCommand,

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

 

db.AddInParameter(dbCommand,

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

 

 

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

 

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

 

conn.Open()

 

'Start of the transaction

 

 

Using trans As DbTransaction = conn.BeginTransaction

 

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

db.AddOutParameter(dbCommand,

"@atyID", DbType.Int32, 8)

 

 

'Return the ID of the newly inserted record

 

trans.Commit()

 

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

 

 

End Using

 

conn.Close()

 

End Using

 

Aug 12, 2008 at 3:07 PM
Hi,

Can you provide more information about the errors you get?

Fernando
Aug 12, 2008 at 4: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 5:21 PM
That's not how parameters work in ADO.NET; you need to add the output parameter before invoking the stored procedure.

Fernando
Aug 12, 2008 at 5: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.