PC Review


Reply
Thread Tools Rate Thread

stored procedures - Access

 
 
Guest
Posts: n/a
 
      27th Nov 2003
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.

 
Reply With Quote
 
 
 
 
Miha Markic
Guest
Posts: n/a
 
      27th Nov 2003
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.
>



 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      27th Nov 2003
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.
>>

>
>
>.
>

 
Reply With Quote
 
Miha Markic
Guest
Posts: n/a
 
      27th Nov 2003
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


 
Reply With Quote
 
Guest
Posts: n/a
 
      27th Nov 2003
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
>
>
>.
>

 
Reply With Quote
 
Miha Markic
Guest
Posts: n/a
 
      27th Nov 2003
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??



 
Reply With Quote
 
Paul Clement
Guest
Posts: n/a
 
      1st Dec 2003
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)
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i convert stored procedures in sql to stored queries in ac =?Utf-8?B?YWRz?= Microsoft Access Queries 1 28th Aug 2006 01:49 PM
calling standard module procedures from event procedures =?Utf-8?B?b3NzaWFu?= Microsoft Access VBA Modules 2 9th Feb 2006 02:26 PM
Map Stored Procedure dependencies from ASP pages through methods to stored procedures dwilliams@newportgroup.com Microsoft Dot NET 6 18th Mar 2005 04:02 AM
Map Stored Procedure dependencies from ASP pages through methods to stored procedures dwilliams@newportgroup.com Microsoft ADO .NET 2 17th Mar 2005 01:47 PM
Error: Executing Stored Procedures-- Cannot Pass TimeStamp Values From sqlCommand Object Parameter to A SQL Stored Procedure =?Utf-8?B?VGVjaE1E?= Microsoft ADO .NET 3 17th Mar 2004 05:03 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:55 PM.