stored procedures - Access

Discussion in 'Microsoft ADO .NET' started by Guest, Nov 27, 2003.

  1. Guest

    Guest 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.
     
    Guest, Nov 27, 2003
    #1
    1. Advertisements

  2. Guest

    Miha Markic Guest

    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

    <> wrote in message
    news:048e01c3b4f1$efd975f0$...
    > 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.
    >
     
    Miha Markic, Nov 27, 2003
    #2
    1. Advertisements

  3. Guest

    Guest 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
    >
    ><> wrote in message
    >news:048e01c3b4f1$efd975f0$...
    >> 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.
    >>

    >
    >
    >.
    >
     
    Guest, Nov 27, 2003
    #3
  4. Guest

    Miha Markic Guest

    Hi,

    <> wrote in message
    news:825401c3b4f9$3a88dc10$...
    > 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
     
    Miha Markic, Nov 27, 2003
    #4
  5. Guest

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

    >-----Original Message-----
    >Hi,
    >
    ><> wrote in message
    >news:825401c3b4f9$3a88dc10$...
    >> 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
    >
    >
    >.
    >
     
    Guest, Nov 27, 2003
    #5
  6. Guest

    Miha Markic Guest

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

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

    <> wrote in message
    news:007c01c3b4ff$fcfc01e0$...
    > 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??
     
    Miha Markic, Nov 27, 2003
    #6
  7. Guest

    Paul Clement Guest

    On Thu, 27 Nov 2003 06:22:50 -0800, <> 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 ~~~
    Microsoft MVP (Visual Basic)
     
    Paul Clement, Dec 1, 2003
    #7
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mono

    Re: Your opinion about stored procedures

    mono, Jul 4, 2003, in forum: Microsoft ADO .NET
    Replies:
    1
    Views:
    513
    David Waz...
    Jul 4, 2003
  2. Guest

    Access Stored Procedures

    Guest, Mar 12, 2004, in forum: Microsoft ADO .NET
    Replies:
    7
    Views:
    199
    William Ryan eMVP
    Mar 13, 2004
  3. Guest
    Replies:
    1
    Views:
    365
    W.G. Ryan eMVP
    Feb 4, 2005
  4. Replies:
    2
    Views:
    461
    Robbe Morris [C# MVP]
    Mar 17, 2005
  5. Guest
    Replies:
    1
    Views:
    280
    Guest
    May 21, 2007
Loading...

Share This Page