An Output parameter causes 80040e21 from Access 2000

D

David M E

I am trying to use output parameters with an Access database, but
can't because I get an exception "Multi-step OLE DB operation
generated errors" (-2147217887, 80040E21).

I have been removing code to eliminate possible causes and got to the
following piece of code:

IDbCommand com= bd.GetCommand();
com.CommandText=
@" select 1= 1";
System.Data.OleDb.OleDbParameter paramIdMensaje= new
System.Data.OleDb.OleDbParameter(
"IdMensaje",
System.Data.OleDb.OleDbType.Integer);
paramIdMensaje.Direction= ParameterDirection.Output;
com.Parameters.Add( paramIdMensaje);
IDbTransaction trans= com.Transaction=
com.Connection.BeginTransaction();
bool haGrabadoBien;
try
{
com.ExecuteNonQuery(); // The exception is raised here.
// Parámetro de salida.
idMensaje= ( long) paramIdMensaje.Value;
trans.Commit();
haGrabadoBien= true;
} // try
catch( Exception ex)
{
...

I have also tried "select @IdMensaje= 1" as com.CommandText. I just
wanted to ascertain that the actual SQL is not important.
When I run this, I get the exception at the marked place ("Multi-step
OLE DB operation generated errors").
As you see, the error message is not helpful.

The strange thing is that, if I leave the parameter direction as the
default Input, the execution follows after ExecuteNonQuery.
If I use
com.CommandText= @" select @IdMensaje= 1";
and ParameterDirection.Input, I get the expected error about IdMensaje
having no value.

It's the first time I use ADO.NET on Microsoft Access (2000 (9.0.6926
SP-3)). The connection string is
"Provider=Microsoft.Jet.OLEDB.4.0;Data source=
D:\Gateway\Gateway\Gateway.mdb". I know it works because earlier in
the program I have got data from a DataReader on this base.
I don't remember having these problems with SQL Server.

The exception is:
ex {"La operación de múltiples pasos de OLE DB generó errores.
Compruebe los valores de estado de OLE DB si es posible. No se realizó
ningún trabajo." } System.Exception
+ [System.Data.OleDb.OleDbException]
{System.Data.OleDb.OleDbException}
System.Data.OleDb.OleDbException
System.Object {System.Data.OleDb.OleDbException}
System.Object
_className null string
_COMPlusExceptionCode -532459699 int
_exceptionMethod <valor no definido>
System.Reflection.MethodBase
_exceptionMethodString null string
_helpURL null string
_HResult -2147217887 int
_innerException { } System.Exception
_message "" string
_remoteStackIndex 0 int
_remoteStackTraceString null string
_source null string
+ _stackTrace {System.Array} System.Object
_stackTraceString null string
_xcode -532459699 int
_xptrs 0 int
HelpLink null string
HResult -2147217887 int
InnerException { } System.Exception
Message "La operación de múltiples pasos de OLE DB generó
errores. Compruebe los valores de estado de OLE DB si es posible. No
se realizó ningún trabajo." string
Source "Microsoft JET Database Engine" string
StackTrace " at
System.Data.OleDb.OleDbCommand.ProcessResults(Int32 hr)\r\n at
System.Data.OleDb.OleDbCommand.ApplyAccessor(Int32 count, DBBindings
bindings)\r\n at System.Data.OleDb.OleDbCommand.CreateAccessor()\r\n
at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior
behavior, Boolean throwifnotsupported)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()\r\n at
NeMI.Gateway.Datos.DatosCorreo.GrabarMensajeEntrada(Int64& idMensaje,
TipoMensaje tipo, SentidoMensaje sentido, DateTime& dbTimeStamp,
String sender, String mainRecipient, String subject, String
CuerpoCompleto, IList adjuntos, DateTime popTimeStamp, String
cabeceras, String popId) in
d:\\nemi\\gateway\\gateway\\datos\\datoscorreo.cs:line 197" string
+ TargetSite {System.Reflection.RuntimeMethodInfo}
System.Reflection.MethodBase

In the errors collection, there is only one with the same message as
the exception.

I have looked around in MSDN, Google Groups and Google and got nothing
clear.
What am I doing wrong?
Thanks in advance.
 
W

William \(Bill\) Vaughn

Access/JET does not support OUTPUT parameters. It supports QueryDefs, not
real stored procedures.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

David M E said:
I am trying to use output parameters with an Access database, but
can't because I get an exception "Multi-step OLE DB operation
generated errors" (-2147217887, 80040E21).

I have been removing code to eliminate possible causes and got to the
following piece of code:

IDbCommand com= bd.GetCommand();
com.CommandText=
@" select 1= 1";
System.Data.OleDb.OleDbParameter paramIdMensaje= new
System.Data.OleDb.OleDbParameter(
"IdMensaje",
System.Data.OleDb.OleDbType.Integer);
paramIdMensaje.Direction= ParameterDirection.Output;
com.Parameters.Add( paramIdMensaje);
IDbTransaction trans= com.Transaction=
com.Connection.BeginTransaction();
bool haGrabadoBien;
try
{
com.ExecuteNonQuery(); // The exception is raised here.
// Parámetro de salida.
idMensaje= ( long) paramIdMensaje.Value;
trans.Commit();
haGrabadoBien= true;
} // try
catch( Exception ex)
{
...

I have also tried "select @IdMensaje= 1" as com.CommandText. I just
wanted to ascertain that the actual SQL is not important.
When I run this, I get the exception at the marked place ("Multi-step
OLE DB operation generated errors").
As you see, the error message is not helpful.

The strange thing is that, if I leave the parameter direction as the
default Input, the execution follows after ExecuteNonQuery.
If I use
com.CommandText= @" select @IdMensaje= 1";
and ParameterDirection.Input, I get the expected error about IdMensaje
having no value.

It's the first time I use ADO.NET on Microsoft Access (2000 (9.0.6926
SP-3)). The connection string is
"Provider=Microsoft.Jet.OLEDB.4.0;Data source=
D:\Gateway\Gateway\Gateway.mdb". I know it works because earlier in
the program I have got data from a DataReader on this base.
I don't remember having these problems with SQL Server.

The exception is:
ex {"La operación de múltiples pasos de OLE DB generó errores.
Compruebe los valores de estado de OLE DB si es posible. No se realizó
ningún trabajo." } System.Exception
+ [System.Data.OleDb.OleDbException]
{System.Data.OleDb.OleDbException}
System.Data.OleDb.OleDbException
System.Object {System.Data.OleDb.OleDbException}
System.Object
_className null string
_COMPlusExceptionCode -532459699 int
_exceptionMethod <valor no definido>
System.Reflection.MethodBase
_exceptionMethodString null string
_helpURL null string
_HResult -2147217887 int
_innerException { } System.Exception
_message "" string
_remoteStackIndex 0 int
_remoteStackTraceString null string
_source null string
+ _stackTrace {System.Array} System.Object
_stackTraceString null string
_xcode -532459699 int
_xptrs 0 int
HelpLink null string
HResult -2147217887 int
InnerException { } System.Exception
Message "La operación de múltiples pasos de OLE DB generó
errores. Compruebe los valores de estado de OLE DB si es posible. No
se realizó ningún trabajo." string
Source "Microsoft JET Database Engine" string
StackTrace " at
System.Data.OleDb.OleDbCommand.ProcessResults(Int32 hr)\r\n at
System.Data.OleDb.OleDbCommand.ApplyAccessor(Int32 count, DBBindings
bindings)\r\n at System.Data.OleDb.OleDbCommand.CreateAccessor()\r\n
at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior
behavior, Boolean throwifnotsupported)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()\r\n at
NeMI.Gateway.Datos.DatosCorreo.GrabarMensajeEntrada(Int64& idMensaje,
TipoMensaje tipo, SentidoMensaje sentido, DateTime& dbTimeStamp,
String sender, String mainRecipient, String subject, String
CuerpoCompleto, IList adjuntos, DateTime popTimeStamp, String
cabeceras, String popId) in
d:\\nemi\\gateway\\gateway\\datos\\datoscorreo.cs:line 197" string
+ TargetSite {System.Reflection.RuntimeMethodInfo}
System.Reflection.MethodBase

In the errors collection, there is only one with the same message as
the exception.

I have looked around in MSDN, Google Groups and Google and got nothing
clear.
What am I doing wrong?
Thanks in advance.
Click to expand...
 
D

David M E

"William (Bill) Vaughn" escribió:
Access/JET does not support OUTPUT parameters. It supports QueryDefs, not
real stored procedures.
Click to expand...

Thanks for the answer.
But I am not using a stored procedure. The SQL is in the command text.
Can't I get output parameters in that case as well?
 
W

William \(Bill\) Vaughn

Nope. What is it that you're trying to do? Are you trying to return an
integer or other value instead of a rowset? JET can only return rowsets.
Only SQL Server or other more sophisticated DBMS can return RETURN values
and OUTPUT parameters, PRINT statements, RAISERROR messages and numbers
etal.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"William (Bill) Vaughn" escribió:
Access/JET does not support OUTPUT parameters. It supports QueryDefs, not
real stored procedures.
Click to expand...

Thanks for the answer.
But I am not using a stored procedure. The SQL is in the command text.
Can't I get output parameters in that case as well?
 
D

David M E

"William (Bill) Vaughn" escribió:
Nope. What is it that you're trying to do? Are you trying to return an
integer or other value instead of a rowset?
Click to expand...

Yes, actually I want to insert in a table, get the @@IDENTITY as an
output parameter and use it as an external key for insertion in other
tables. There is a page on MSDN about it but I didn't want to use sets.
JET can only return rowsets.
:(

Only SQL Server or other more sophisticated DBMS can return RETURN values
and OUTPUT parameters, PRINT statements, RAISERROR messages and numbers
etal.
Click to expand...

No SET? I wanted to do two inserts in one ExecuteNonQuery and I wanted
to transfer the @@IDENTITY from the first table to the second.

Thank you for your help.
 
W

William \(Bill\) Vaughn

If you're using Access/JET, you'll have to execute a second query to get the
@@identity back from JET post INSERT.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"William (Bill) Vaughn" escribió:
Nope. What is it that you're trying to do? Are you trying to return an
integer or other value instead of a rowset?
Click to expand...

Yes, actually I want to insert in a table, get the @@IDENTITY as an
output parameter and use it as an external key for insertion in other
tables. There is a page on MSDN about it but I didn't want to use sets.
JET can only return rowsets.
:(

Only SQL Server or other more sophisticated DBMS can return RETURN values
and OUTPUT parameters, PRINT statements, RAISERROR messages and numbers
etal.
Click to expand...

No SET? I wanted to do two inserts in one ExecuteNonQuery and I wanted
to transfer the @@IDENTITY from the first table to the second.

Thank you for your help.
 
Top