locked
Create Stored Procedure Access 2007 RRS feed

  • Question

  • User-285805751 posted

    Hi,

    i am developing asp.net project my backend tool using ms access 2007 database.i need how to create procedure in access.pls help me

    Wednesday, May 30, 2012 2:09 AM

Answers

  • User-821857111 posted

    You can either do this using Access itself (search for an Access tutorial) or you can use DDL to create a procedure programmatically eg:

    var sql = "CREATE PROCEDURE GetProductsByCategory AS SELECT * FROM Products WHERE CategoryId = @categoryId";
    using (var conn = new OleDbConnection(connectionstring))
    {
      using (var cmd = new OleDbCommand(sql, conn))
      {
        conn.Open();
        cmd.ExecuteNonQuery();
      }
    }



     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 30, 2012 2:34 AM
  • User143067745 posted

    Where to write Query in access 2007 or 2010 : click here

    How to write stored procedure : Click Here

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 30, 2012 2:43 AM

All replies

  • User-821857111 posted

    You can either do this using Access itself (search for an Access tutorial) or you can use DDL to create a procedure programmatically eg:

    var sql = "CREATE PROCEDURE GetProductsByCategory AS SELECT * FROM Products WHERE CategoryId = @categoryId";
    using (var conn = new OleDbConnection(connectionstring))
    {
      using (var cmd = new OleDbCommand(sql, conn))
      {
        conn.Open();
        cmd.ExecuteNonQuery();
      }
    }



     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 30, 2012 2:34 AM
  • User143067745 posted

    Where to write Query in access 2007 or 2010 : click here

    How to write stored procedure : Click Here

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 30, 2012 2:43 AM
  • User3866881 posted

    i need how to create procedure

    There's no real "Stored procdure" in Access……However I think you can just write something query of CRUD with parameters in the Query window,and save it as a "Query" in Access file,in fact you can directly use it just like using a Stored procdure。

    For more you can refer this:http://social.msdn.microsoft.com/Forums/en/accessdev/thread/33f3f6bc-03b2-4f64-84ca-cef65bbc0eee

    Reguards!

    Thursday, May 31, 2012 9:52 PM
  • User-1199946673 posted

    However I think you can just write something query of CRUD with parameters in the Query window,and save it as a "Query" in Access file,in fact you can directly use it just like using a Stored procdure。

    Once again, you're just repeating what others already explained

    I don't think this is a good resource at all. A lot of non-sense is told in this thread!!!! Because they keep saying that you can not execute a stored query just like a Stored Procedure, but we know better!

    To execute a stored query, the commandtext should be set to the name of the stored query. The CommandType should be set to StoredProcedure, and if the query contains parameters, they must be added to the parameter collection in the same order they (first) appear in the stored query. However, in the  stored query, you can also specify a parameter collection. If that is done, than that's the order the parameters should be added.

    Friday, June 1, 2012 2:40 AM
  • User3866881 posted

    Hello hans_v:)

    In order to give a full answer to the question asked by the questioner officially, sometimes I have to offer a whole answer, maybe there's something the same as what you've said, so here's a conclusion, so please don't challenge me.

    As to your saying, You think this link isn't good, however this offers the same situation of Update Query and how to use that, The questioner has solved the problem successfully, this is a good sample in my mind.

    Kindly correct me if you have anything else.....

    Reguards!

    Friday, June 1, 2012 2:46 AM
  • User-1199946673 posted

    however this offers the same situation of Update Query and how to use that

    Who says we're talking about an Update query?

    The questioner has solved the problem successfully

    Yes, by NOT creating a stored query (because this wouldn't work according to some of the people in that thread), but to write the query in the commandtext.

    this is a good sample in my mind.

    It doesn't answer to the question being asked.... Also, as I said before, a lot of non-sense is being told in that thread!

    Friday, June 1, 2012 2:55 AM