stored procedures - Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
....
 
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

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.
Click to expand...


.
Click to expand...
 
Hi,

Hi

Not sure I follow what you mean.

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

I thought so.
Can I not use a "StoredProcedure" CommandType and store
this procedure/query in my database?
Click to expand...

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.
 
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.
 
¤ 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 address removed)
Microsoft MVP (Visual Basic)
 
Back
Top