locked
How to run MS Access Update Queries from .NET RRS feed

  • Question

  • I have an MSAccess 2007 database, to which I am connecting from a .NET application using the connection string below.

     <add name="DemoMSAccessDBConn" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MSAccessTest\DemoDB.accdb;Jet OLEDB:Database Password=mypassword;"

    />

    I have created Select, Update, Insert, and Delete queries on some of the database tables, and I have saved those queries in the database itself, i.e. I can run those queries from Access. I am trying to run the same queries from .NET.

    I can run the Select queries from .NET, but running the Update queries does not work. No error message is returned. The updates are simply not effected. I tried the following approaches:

    1. Using CommandType.StoredProcedure, e.g. trying to execute the Orders_Update query that has been saved in the target MSAccess database.
        ocm = new OleDbCommand("Orders_Update", GetMyOleConnection());
        ocm.CommandType = CommandType.StoredProcedure;
       This approach does not work.

    2. Using CommandType.Text, e.g. trying to execute the Orders_Update query that has been saved in the target MSAccess database.
            ocm = new OleDbCommand("Execute Orders_Update", GetMyOleConnection());
            ocm.CommandType = CommandType.Text;
       This approach works for the Select query, but not for the Update query.

    I also tried using an inline SQL query, and that works for the Update as well, but that does not take advantage of the queries that I have created and saved on the database. Am I using the wrong connection string settings, or the wrong command settings? Any ideas?

     

     

     

    Tuesday, August 9, 2011 2:02 PM

Answers

  • I finally got this to work. The answer is simple but unexpected.

    Even though the Update and Insert saved queries in the MSAccess database have named parameters, an those named parameters are referenced with the same name in the .NET code when defining the parameters for the OleDBCommand object, those parameters have to be defined in the same order that they appear in the query.

    E.g. an MS Access saved query called Orders_Update, defined as follows:

     UPDATE Orders SET Quantity = [_quantity?], ShippingAddress = [_shippingAddress?]
     WHERE OrderId=[_orderId?];

    To execute the query above in .NET using OleDB, use the following code:

           ocm = new OleDbCommand("Execute Orders_Update", oc);   //oc is simple the usual OleDBConnection object to the database
           ocm.CommandType = CommandType.Text;

           ocm.Parameters.Add("_quantity", OleDbType.Integer);    -- line a
           ocm.Parameters["_quantity"].Direction = ParameterDirection.Input;
           ocm.Parameters["_quantity"].Value = ovo.Quantity;

           ocm.Parameters.Add("_shippingAddress", OleDbType.VarChar, OrderVO.ShippingAddressFieldSize);   -- line b
           ocm.Parameters["_shippingAddress"].Direction = ParameterDirection.Input;
           ocm.Parameters["_shippingAddress"].Value = ovo.ShippingAddress;

           ocm.Parameters.Add("_orderId", OleDbType.Integer);   -- line c
           ocm.Parameters["_orderId"].Direction = ParameterDirection.Input;
           ocm.Parameters["_orderId"].Value = ovo.OrderId;

    If the parameter order is not respected, e.g. line c appears before line a or line b, then the OleDB fail to execute the query, or rather it ties the wrong  parameter value to the wrong parameter, and in the case of the update query above, if if ties the Quantity value to the OrderId parameter, it probably will not find a matching record, which explains why it fails to update the correct record without even returning an error.

    I cannot believe that Microsoft failed to make the OleDB interface use the query parameter names properly, as it is in case of a SQL Server connection.

    Friday, October 14, 2011 5:49 PM

All replies

  • Since Access is a file based RDBMS and not a server based RDBMS I don't believe you can use the stored procdedures option of the .Net OleDBcommand object.  Well, that's my feeling anyway since I have not been able to find a way to use the stored procedure commandtype for Access.   I think you are resigned to just writing your queries in the sql strings in the .Net app. 

    If anyone knows a way to use the stored procedure commandtype for OleDB -- please share.  Maybe it's doable since the storedprocedure IS an OleDB commandtype option - or maybe it was just left over from the .Net development and does not have any real use in OleDB.

    I do know that the Insert, Update, and Delete OleDBcommand objects don't work with Access.  I think the only .Net OleDBcommand object that works with Access is the SelectCommand.  So for inserting, updating, and Deleting Access rows from .Net you have to write your Insert/Update/Delete queries in the SelectCommand object.  Thus, I don't think the storedprocedure commandtype option is really available for Access in .Net.  I could be wront, but I have not found otherwise (so far).

     

     


    Rich P
    Tuesday, August 9, 2011 3:18 PM
  • Try to use the ExecuteNonQuery () command, for example:


     

     Dim retValue As Integer
     Command = New SqlCommand(Sql, Connection)
    
     retValue = Command.ExecuteNonQuery()
    


     

    See this threads for reference:

    http://vb.net-informations.com/ado.net-dataproviders/ado.net-executereader-executenonquery.htm

    http://vb.net-informations.com/ado.net-dataproviders/ado.net-executenonquery-oledbcommand.htm


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Edited by danishani Tuesday, August 9, 2011 3:42 PM
    Tuesday, August 9, 2011 3:23 PM
  • I think the OP wants to execute an Access query that is stored in Access -- similar to executing a stored procedure that is stored in a sql server.  I don't think this is doable from .Net. 

    Dim retValue As Integer
    Command = New SqlCommand(Sql, Connection)
    retValue = Command.ExecuteNonQuery()

    the SqlCommand only works with Sql Server.  Access can only use the OleDBCommand object, and at that I believe it is limited to only the Select Command --- where you can write Select, Insert, Update, Delete statements (only in the Select Command for Access).

     

     


    Rich P
    Tuesday, August 9, 2011 3:31 PM
  • Hi Rich,

     

    You are right, you can't execute it straight from Access, it's indeed the alternative approach I suggested using the ExecuteNonQuery () command in combination with the OleDbCommand Object (see example in second link).

     

    Imports System.Data.OleDb
    Public Class Form1
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim cnn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim sql As String
    
        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MSAccessTest\DemoDB.accdb;Jet OLEDB:Database Password=mypassword;"
        sql = "UPDATE tblYourTableName SET Test ='B' WHERE Test='A'"
    
        cnn = New OleDbConnection(connetionString)
        Try
          cnn.Open()
          cmd = New OleDbCommand(sql, cnn)
          cmd.ExecuteNonQuery()
          cmd.Dispose()
          cnn.Close()
          MsgBox(" ExecuteNonQuery in OleDbConnection executed !!")
        Catch ex As Exception
          MsgBox("Can not open connection ! ")
        End Try
      End Sub
    End Class
    



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, August 9, 2011 6:15 PM
  • I just want to point out that I am able to execute a SELECT Access query from .NET, but a similar approach for an UPDATE query (using ExecuteNonQuery()) fails without returning an error. E.g. the following code works well and returns all the records generated by the Orders_SelectAll query that was created/saved in the target MS Access database.

            ocm = new OleDbCommand("Execute Orders_SelectAll", GetMyOleConnection());
            ocm.CommandType = CommandType.Text;
            odr = ocm.ExecuteReader();

    Tuesday, August 9, 2011 8:17 PM
  • Hi Oneworld-not three,

     

    Thank you for posting in our forum.

     

    You may need to modify your codes a little. Since you didn't post your whole function, I want to give you an example. You can use the example as reference. Modify some of your sentences in your codes according to the following example. Perhaps you can run your codes smoothly!

     

    http://www.codeguru.com/forum/showpost.php?p=1501259&postcount=2

     

    Note: Since the version of your database is different. The connection string you used are different.

    Here is another op that experienced the similar issue with yours. You may also want to read the thread as reference.

     

    http://www.codeguru.com/forum/showthread.php?t=412975

     

    I hope this will help your resolve the issue.

    If you have any other concerns, please feel free to let me know.

     

    Best Regards,


    Macy Dong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 11, 2011 9:02 AM
  • If your update query involves VBA function, it may run very well from Access, but not at all elsewhere, even from Excel, VB6, etc. That is because of a special tie between Access (the application) and Jet that other applications don't have. On the other hand, if your update query is a plain all SQL statement (no VBA functions, no user defined VBA function), then this is really strange. Is Daniel's example works for you?

     

    There should be no problem to use the stored procedure approach, Jet 4.0 automatically consider a query as a stored procedure (a query with parameter ***is*** a stored prodecure, after all). IN fact, you can create them with DDL-SQL:

    From within MS Access, in the immediate debug window:

    CurrentProject.Connection.Execute "CREATE PROCEDURE  toto AS SELECT * FROM table1"

     

    and, after an explicit refresh, the UI will list a new query, called toto. Note that you need CurrentProject, ADO,  not CurrentDb, DAO.

     

     

    Thursday, August 11, 2011 9:18 AM
  • Thank you for the response. Those examples and links are all about running inline SQL queries. My question here is about running a query that has been saved in the target MSAccess database, i.e. similar to calling a SQL Server Stored Procedure. I have done it with MSAccess 2000, but for some reason the same approach, described above, is not working for MSAccess 2007.
    Thursday, August 11, 2011 1:56 PM
  • Hi Oneworld-not three,

     

    Thank you for your quick response.

     

    You said:

    I have done it with MSAccess 2000, but for some reason the same approach, described above, is not working for MSAccess 2007.

     

    By this sentence I think:

    In Access 2000, Select, Update, Insert, and Delete queries all worked well. Right?

    If in Access 2007 all of the queried can't work, perhaps the connection string you use is not correct.

    If in Access 2007 only Update query can't work, perhaps it has something to do with the compatible.

    (You said, in database the update query worked well .So, I assumed that the query you wrote is correct and the query can find record to update.)

     

    If you installed other version of Access on your side, Could you be kind to try to run your codes with other version of Access and share the result with us?

     

    If you don’t mind, could you be kind to upload your database to the following site?

    https://skydrive.live.com/ 

    Please remember to share the link with us.

    If you can upload your database, it may be easier for us to understand your situation.

     

    I look forward for hearing from you.

    If you have any updates, could you be kind to share with us?

    I think many members are interesting in your thread.

     

    Best Regards,


    Macy Dong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, August 12, 2011 7:05 AM
  • I finally got this to work. The answer is simple but unexpected.

    Even though the Update and Insert saved queries in the MSAccess database have named parameters, an those named parameters are referenced with the same name in the .NET code when defining the parameters for the OleDBCommand object, those parameters have to be defined in the same order that they appear in the query.

    E.g. an MS Access saved query called Orders_Update, defined as follows:

     UPDATE Orders SET Quantity = [_quantity?], ShippingAddress = [_shippingAddress?]
     WHERE OrderId=[_orderId?];

    To execute the query above in .NET using OleDB, use the following code:

           ocm = new OleDbCommand("Execute Orders_Update", oc);   //oc is simple the usual OleDBConnection object to the database
           ocm.CommandType = CommandType.Text;

           ocm.Parameters.Add("_quantity", OleDbType.Integer);    -- line a
           ocm.Parameters["_quantity"].Direction = ParameterDirection.Input;
           ocm.Parameters["_quantity"].Value = ovo.Quantity;

           ocm.Parameters.Add("_shippingAddress", OleDbType.VarChar, OrderVO.ShippingAddressFieldSize);   -- line b
           ocm.Parameters["_shippingAddress"].Direction = ParameterDirection.Input;
           ocm.Parameters["_shippingAddress"].Value = ovo.ShippingAddress;

           ocm.Parameters.Add("_orderId", OleDbType.Integer);   -- line c
           ocm.Parameters["_orderId"].Direction = ParameterDirection.Input;
           ocm.Parameters["_orderId"].Value = ovo.OrderId;

    If the parameter order is not respected, e.g. line c appears before line a or line b, then the OleDB fail to execute the query, or rather it ties the wrong  parameter value to the wrong parameter, and in the case of the update query above, if if ties the Quantity value to the OrderId parameter, it probably will not find a matching record, which explains why it fails to update the correct record without even returning an error.

    I cannot believe that Microsoft failed to make the OleDB interface use the query parameter names properly, as it is in case of a SQL Server connection.

    Friday, October 14, 2011 5:49 PM
  • Hi oneworld, Thanks for the example on "UPDATE", hey do you have an example on an "INSERT" stored query that takes parameters as pass-in values? Pls send me an "INSERT" stored query example to wangbyw@yahoo.com, many Thanks!

    (Or if anyone else can send me an "INSERT" stored query example, will be appreciated too!)

    Thursday, October 27, 2016 4:00 AM