stored procedures - Access

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

Miha Markic

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

Guest

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

Miha Markic

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

Guest

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

Paul Clement

¤ 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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Stored Query 3
Too long Record in Insert by OleDb (Ms Access) 3
oledb date/time insert command format 5
asp.net and Access 2000 - fails to connect after awhile 2
Insert bimap into Access tables using VB.NET 0
Getting Autoincrement ID in ADO.NET 6

Top