Hi

I'm trying to use stored procedures held in Access and as
soon as try to execute the command (using ExecuteReader) I
get the error message:
Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done.

My code is:

OleDbConnection myConnection = null;
OleDbDataReader myReader = null;

myConnection = new OleDbConnection
(ConfigurationSettings.AppSettings["ConnectionString"]);

OleDbCommand myCommand = new OleDbCommand
("SELECTTEST",myConnection);

myCommand.CommandType=CommandType.StoredProcedure;

OleDbParameter parameterTask=new OleDbParameter
("@TASK_NAME",OleDbType.VarChar,15);
parameterTask.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterTask);

OleDbParameter parameterDescription=new OleDbParameter
("@TASK_DESCRIPTION",OleDbType.VarChar,15);
parameterDescription.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterDescription);

OleDbParameter parameterProject=new OleDbParameter
("@PROJECT_NAME",OleDbType.VarChar,15);
parameterProject.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProject);

myConnection.Open();
myReader = myCommand.ExecuteReader();
myConnection.Close();

"SELECTTEST" is held in Access and is "SELECT
TASK.TASK_NAME,TASK.TASK_DESCRIPTION,PROJECT_NAME FROM
TASK" and the TASK table only has these fields.

Any ideas what I'm doing wrong?
Thanks in advance.

Hi rona,

You are probably talking about Access Queries which would translate to Views
in Sql server, for example.
Access them as normal tables.

myCommand.CommandType=CommandType.Text;
myCommand.CommandText = "SELECT * FROM SELECTTEST";
// no parameters needed
....

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

<(E-Mail Removed)> wrote in message
news:048e01c3b4f1$efd975f0$(E-Mail Removed)...
> Hi
>
> I'm trying to use stored procedures held in Access and as
> soon as try to execute the command (using ExecuteReader) I
> get the error message:
> Multiple-step OLE DB operation generated errors. Check
> each OLE DB status value, if available. No work was done.
>
> My code is:
>
> OleDbConnection myConnection = null;
> OleDbDataReader myReader = null;
>
> myConnection = new OleDbConnection
> (ConfigurationSettings.AppSettings["ConnectionString"]);
>
> OleDbCommand myCommand = new OleDbCommand
> ("SELECTTEST",myConnection);
>
> myCommand.CommandType=CommandType.StoredProcedure;
>
> OleDbParameter parameterTask=new OleDbParameter
> ("@TASK_NAME",OleDbType.VarChar,15);
> parameterTask.Direction = ParameterDirection.Output;
> myCommand.Parameters.Add(parameterTask);
>
> OleDbParameter parameterDescription=new OleDbParameter
> ("@TASK_DESCRIPTION",OleDbType.VarChar,15);
> parameterDescription.Direction = ParameterDirection.Output;
> myCommand.Parameters.Add(parameterDescription);
>
> OleDbParameter parameterProject=new OleDbParameter
> ("@PROJECT_NAME",OleDbType.VarChar,15);
> parameterProject.Direction = ParameterDirection.Output;
> myCommand.Parameters.Add(parameterProject);
>
> myConnection.Open();
> myReader = myCommand.ExecuteReader();
> myConnection.Close();
>
> "SELECTTEST" is held in Access and is "SELECT
> TASK.TASK_NAME,TASK.TASK_DESCRIPTION,PROJECT_NAME FROM
> TASK" and the TASK table only has these fields.
>
> Any ideas what I'm doing wrong?
> Thanks in advance.
>

Hi

Not sure I follow what you mean.

SELECTTEST is not actually a table, it is a query held in
my database.

Can I not use a "StoredProcedure" CommandType and store
this procedure/query in my database?

Thanks in advance.
>-----Original Message-----
>Hi rona,
>
>You are probably talking about Access Queries which would
translate to Views
>in Sql server, for example.
>Access them as normal tables.
>
>myCommand.CommandType=CommandType.Text;
>myCommand.CommandText = "SELECT * FROM SELECTTEST";
>// no parameters needed
>....
>
>--
>Miha Markic - RightHand .NET consulting & software
development
>miha at rthand com
>
><(E-Mail Removed)> wrote in message
>news:048e01c3b4f1$efd975f0$(E-Mail Removed)...
>> Hi
>>
>> I'm trying to use stored procedures held in Access and
as
>> soon as try to execute the command (using
ExecuteReader) I
>> get the error message:
>> Multiple-step OLE DB operation generated errors. Check
>> each OLE DB status value, if available. No work was
done.
>>
>> My code is:
>>
>> OleDbConnection myConnection = null;
>> OleDbDataReader myReader = null;
>>
>> myConnection = new OleDbConnection
>> (ConfigurationSettings.AppSettings["ConnectionString"]);
>>
>> OleDbCommand myCommand = new OleDbCommand
>> ("SELECTTEST",myConnection);
>>
>> myCommand.CommandType=CommandType.StoredProcedure;
>>
>> OleDbParameter parameterTask=new OleDbParameter
>> ("@TASK_NAME",OleDbType.VarChar,15);
>> parameterTask.Direction = ParameterDirection.Output;
>> myCommand.Parameters.Add(parameterTask);
>>
>> OleDbParameter parameterDescription=new OleDbParameter
>> ("@TASK_DESCRIPTION",OleDbType.VarChar,15);
>> parameterDescription.Direction =
ParameterDirection.Output;
>> myCommand.Parameters.Add(parameterDescription);
>>
>> OleDbParameter parameterProject=new OleDbParameter
>> ("@PROJECT_NAME",OleDbType.VarChar,15);
>> parameterProject.Direction = ParameterDirection.Output;
>> myCommand.Parameters.Add(parameterProject);
>>
>> myConnection.Open();
>> myReader = myCommand.ExecuteReader();
>> myConnection.Close();
>>
>> "SELECTTEST" is held in Access and is "SELECT
>> TASK.TASK_NAME,TASK.TASK_DESCRIPTION,PROJECT_NAME FROM
>> TASK" and the TASK table only has these fields.
>>
>> Any ideas what I'm doing wrong?
>> Thanks in advance.
>>
>
>
>.
>

Hi,

<(E-Mail Removed)> wrote in message
news:825401c3b4f9$3a88dc10$(E-Mail Removed)...
> Hi
>
> Not sure I follow what you mean.
>
> SELECTTEST is not actually a table, it is a query held in
> my database.

I thought so.

> Can I not use a "StoredProcedure" CommandType and store
> this procedure/query in my database?

No. You should use CommandType.Text and treat it as normal table.
SELECTTEST gives you a table of records, right.
So you will query it like it: SELECT * FROM SELECTTEST.
It is not a storeprocedure, ya know. It is a some sort of readonly view.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Hi

So the stored procedures that I've seen used for SQLServer
cannot be used in Access?

Presumably what I'm proposing to do is sensible/wise,i.e.
holding the queries in Access and using CommandType.Text
to extract the query rather than holding the query inside
my code??

Thanks for your speedy replies.

>-----Original Message-----
>Hi,
>
><(E-Mail Removed)> wrote in message
>news:825401c3b4f9$3a88dc10$(E-Mail Removed)...
>> Hi
>>
>> Not sure I follow what you mean.
>>
>> SELECTTEST is not actually a table, it is a query held
in
>> my database.
>
>I thought so.
>
>> Can I not use a "StoredProcedure" CommandType and store
>> this procedure/query in my database?
>
>No. You should use CommandType.Text and treat it as
normal table.
>SELECTTEST gives you a table of records, right.
>So you will query it like it: SELECT * FROM SELECTTEST.
>It is not a storeprocedure, ya know. It is a some sort of
readonly view.
>
>--
>Miha Markic - RightHand .NET consulting & software
development
>miha at rthand com
>
>
>.
>

Holding the query in Access is faster because it is already parsed.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

<(E-Mail Removed)> wrote in message
news:007c01c3b4ff$fcfc01e0$(E-Mail Removed)...
> Hi
>
> So the stored procedures that I've seen used for SQLServer
> cannot be used in Access?
>
> Presumably what I'm proposing to do is sensible/wise,i.e.
> holding the queries in Access and using CommandType.Text
> to extract the query rather than holding the query inside
> my code??

On Thu, 27 Nov 2003 06:22:50 -0800, <(E-Mail Removed)> wrote:

¤ Hi
¤
¤ I'm trying to use stored procedures held in Access and as
¤ soon as try to execute the command (using ExecuteReader) I
¤ get the error message:
¤ Multiple-step OLE DB operation generated errors. Check
¤ each OLE DB status value, if available. No work was done.
¤
¤ My code is:
¤
¤ OleDbConnection myConnection = null;
¤ OleDbDataReader myReader = null;
¤
¤ myConnection = new OleDbConnection
¤ (ConfigurationSettings.AppSettings["ConnectionString"]);
¤
¤ OleDbCommand myCommand = new OleDbCommand
¤ ("SELECTTEST",myConnection);
¤
¤ myCommand.CommandType=CommandType.StoredProcedure;
¤
¤ OleDbParameter parameterTask=new OleDbParameter
¤ ("@TASK_NAME",OleDbType.VarChar,15);
¤ parameterTask.Direction = ParameterDirection.Output;
¤ myCommand.Parameters.Add(parameterTask);
¤
¤ OleDbParameter parameterDescription=new OleDbParameter
¤ ("@TASK_DESCRIPTION",OleDbType.VarChar,15);
¤ parameterDescription.Direction = ParameterDirection.Output;
¤ myCommand.Parameters.Add(parameterDescription);
¤
¤ OleDbParameter parameterProject=new OleDbParameter
¤ ("@PROJECT_NAME",OleDbType.VarChar,15);
¤ parameterProject.Direction = ParameterDirection.Output;
¤ myCommand.Parameters.Add(parameterProject);
¤
¤ myConnection.Open();
¤ myReader = myCommand.ExecuteReader();
¤ myConnection.Close();
¤
¤ "SELECTTEST" is held in Access and is "SELECT
¤ TASK.TASK_NAME,TASK.TASK_DESCRIPTION,PROJECT_NAME FROM
¤ TASK" and the TASK table only has these fields.
¤
¤ Any ideas what I'm doing wrong?
¤ Thanks in advance.

I don't see any output parameters defined in your Access QueryDef. From what I can see you don't
need them if you're returning a resultset to the DataReader. Otherwise the code looks OK to me.

Remove the parameter code and see if it makes any difference.


Paul ~~~ (E-Mail Removed)
Microsoft MVP (Visual Basic)