locked
Insert Query defined centrally RRS feed

  • Question

  • User122187137 posted

    Can I define Insert query only once as in stored procedures for access database?

    Friday, May 20, 2011 7:01 AM

Answers

  • User-1199946673 posted

    I mean this: In Access, you can store a query named query1, for example:

     

    PARAMETERS @Field1 Text ( 255 ), @ID Long;
    UPDATE table1 SET Field1 = @Field1 WHERE ID = @ID

     

    Then you can do something like:

     

            Using cn As New Data.OleDb.OleDbConnection("youconnectionstring")
                Using cmd As New Data.OleDb.OleDbCommand("query1", cn)
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("Field1", "test")
                    cmd.Parameters.AddWithValue("ID", 1)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 24, 2011 3:45 AM

All replies

  • User-1917713218 posted

    Yes you can

    but in that you need to pass field and table name dynamically.

    Friday, May 20, 2011 7:31 AM
  • User1867929564 posted

    refer this,

    http://support.microsoft.com/kb/202116 
    Secondly if you make a query in mdb like in emp table(4 columns)

    INSERT INTO Employee ( empid, fname, lname, deptid ) values(?,?,?,?)

    It will ask for paramter.
    I think you can invoke this from asp.net and pass parameter)

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    http://www.xefteri.com/articles/show.cfm?id=6 

    Saturday, May 21, 2011 2:00 AM
  • User122187137 posted

    How can I pass field and table name dynamically?

    Saturday, May 21, 2011 2:39 AM
  • User3866881 posted

    Hi:)

    First I beg your pardon but I cannot understand how you can use query in Access as StoredProcdure? I know that Access db doesn't support SP.

    And If you want to dynamically craete an insert with different tables as well as its names. I think you use something like this:

    public boolean DynamicInsert(string insertsql,OleDbParameter[]parameters)

    {

              using (OleDbConnection con = new OleDbConnection(Your conn str))

               {

                       OleDbCommand cmd = new OleDbCommand(insertsql,con);

                      cmd.Parameters.AddRange(parameters);

                      con.Open();

                      return cmd.ExecuteNonQuery()>0;

               }

    }

    You can call this method as:

    DynamicInsert("insert into tableA values(@a,@b)",new OleDbParameter[]

    {

    new OleDbParameterp{ParameterName="@a",Type="xxx",Value="xxx",

    new OleDbParameterp{ParameterName="@b",Type="yyy",Value="yyy",

    }

    );

    With different kinds of insert sql statement as parameters, you should finish the task.

    Sunday, May 22, 2011 10:36 PM
  • User-1199946673 posted

    First I beg your pardon but I cannot understand how you can use query in Access as StoredProcdure? I know that Access db doesn't support SP

    Access doesn't support Stored Procedures. However, it does support stored queries (single sql statements) that you can execute just like SQL stored procedures...

    Monday, May 23, 2011 4:53 AM
  • User3866881 posted

    Oh, you mean just like the 2man's or what I say, hans_v?

    Just use OleDbCommand+Your sql statement?

    Thx again. Correct me if I'm wrong:)

    Monday, May 23, 2011 9:03 PM
  • User-1199946673 posted

    I mean this: In Access, you can store a query named query1, for example:

     

    PARAMETERS @Field1 Text ( 255 ), @ID Long;
    UPDATE table1 SET Field1 = @Field1 WHERE ID = @ID

     

    Then you can do something like:

     

            Using cn As New Data.OleDb.OleDbConnection("youconnectionstring")
                Using cmd As New Data.OleDb.OleDbCommand("query1", cn)
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("Field1", "test")
                    cmd.Parameters.AddWithValue("ID", 1)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 24, 2011 3:45 AM
  • User3866881 posted

    Oh man, you are really a hero——hans_v!

    Specialized in Access...

    Yours is right.

    I've also learnt a lot from you

    Thx again

    Hope we can futher discuss more on some issues

    Tuesday, May 24, 2011 4:34 AM