Previous | Index | Next 

[PRB] Invoking an action stored procedure via a Command object may throw an exception

ADODB provides several options when invoking a stored procedure. More specifically, you can invoke a stored procedure without specifying whether it returns a recordset, as in this example:

	Dim cmd As New Command
	Set cmd.ActiveConnection = myConn
	cmd.CommandType = adCmdStoredProc
	cmd.CommandText = "MyStoredProc"
	cmd.Execute
	

Notice that the Execute method doesn’t specify that the stored procedure does not return a recordset (or that we aren’t interested in any returned recordset).

Unfortunately, this approach can’t be easily reproduced in ADO.NET without a severe performance penalty. In fact, under ADO.NET the calling code must use either the ExecuteReader method when it expects one or more data row, or the ExecuteNonQuery method when the stored procedure doesn’t return any data row. This difference is reflected in the way the ADOLibrary works.

For this reason, starting with version 1.35 all calls to the Command.Execute method in the ADOLibrary must specify the adExecuteNoRecords flag in the options parameter if the stored procedure doesn’t return any data row:

	Dim cmd As New Command
	Set cmd.ActiveConnection = myConn
	cmd.CommandType = adCmdStoredProc
	cmd.CommandText = "MyStoredProc"
	cmd.Execute , , adExecuteNoRecords
	

All the calls to the Execute method without the adExecuteNoRecords flag are internally implemented by means of the ExecuteReader ADO.NET method and therefore throw an exception if the stored procedure doesn’t return any method. It is assumed that the developer working at the migration will notice this spurious error and will fix the VB6 code accordingly.

Notice that specifying the adExecuteNoRecords flag is a good habit even if you aren’t converting using the ADOLibrary, as explained in this KB article.

 

Previous | Index | Next