locked
Updating a table in MS-Access using OLE DB RRS feed

  • Question

  • User986700334 posted

    I'm putting together a site for a friend who does not have the capability to run a SQL or MySQL instance.  For that reason, I'm using OLE DB to connect to a MS-Access database instead.  I'm able to retrieve data just fine using a SELECT statement on an OleDbCommand object.  I ran into problems updating the table directly using an UPDATE statement, however, so I tried a different approach, and so far have no luck there either.

    The SELECT query always returns data when called on a command object using ExecuteReader().

    SELECT UserName, LastName, FirstName, Email, PwdHash, Site, Admin FROM Users WHERE ID=@ID AND Site=@Site

    The UPDATE query always resulted in a "successful" (error-free) call to ExecuteNonQuery(), but the data in the table was never changed.

    UPDATE Users SET [UserName] = @UserName, [LastName] = @LastName, [FirstName] = @FirstName, [Email] = @Email, [PwdHash] = @PwdHash, [Admin] = @Admin WHERE ID = @ID AND Site = @Site

    (Note that I've used this query both with and without the enclosing brackets around the field names.)

    Because of this failure, I attempted to use a DataAdapter instead.  The code, minus exception-handling bits, looks like:

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter();             
                DataSet userData = new DataSet(string.Format("User{0}", _id));            
                dataAdapter.SelectCommand = retrieveCommand;
                dataAdapter.UpdateCommand = updateCommand;
    
                DataTable datUsers = new DataTable();
    
                retrieveCommand.Parameters.Clear();
                retrieveCommand.Parameters.Add(paramSiteID);
                retrieveCommand.Parameters.Add(paramUserID);
    
    connection.Open(); dataAdapter.Fill(userData); retrieveCommand.Parameters.Remove(paramSiteID); retrieveCommand.Parameters.Remove(paramUserID); // modify DataTable data userData.Tables[0].Rows[0]["UserName"] = _userName; userData.Tables[0].Rows[0]["LastName"] = _lastName; userData.Tables[0].Rows[0]["FirstName"] = _firstName; userData.Tables[0].Rows[0]["Email"] = _email; userData.Tables[0].Rows[0]["PwdHash"] = _pwdHash; userData.Tables[0].Rows[0]["Admin"] = _admin; dataAdapter.Update(datUsers);

    The table userData.Tables[0] is always empty.  The field count is correct, but there are 0 rows in the table.  This is confusing, since I'm using the same SELECT query here as in the object's data-retrieval method.  I get a null reference exception as soon as I start modifying the data in the rows.

    What am I missing?  I don't doubt that I don't have the DataTable wired in correctly yet, but I have to get past this problem to finish troubleshooting the method.  Any advice would be appreciated; if the UPDATE query approach can be made to work, I'm not averse to trying that again.  If it's of any use, I'm using Access 2010.  The User table, like all the tables, was originally created using an AutoNumber field for the ID (primary key), but that has since been changed to a Number type so that the application can manage the IDs.

    [UPDATE] I've attempted to fill a DataTable instead of a DataSet, with the same result. 

    Thursday, November 6, 2014 5:29 PM

Answers

  • User-1716253493 posted

    Can you put sql database in app_data folder then connect it?

    Try it, i guess you are able to connect sql server database file (sql express) without installing sql server instance.

    If you still want to use access database, parameters in access is deferent with sql parameter

    In access, actualy does not support parameter name

    update tbl set colb=?, colc=? where cola=?

    You should add

    parameters[0] for colb

    parameters[1] for colc

    parameters[2] for cola

    It's depend parameters squence.

    If you update the data based id value, id parameter should last parameter

    because param id typed at last : ....... where id=@id

    care about the squence order whatever parameter name

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 6, 2014 8:04 PM

All replies

  • User-1716253493 posted

    Can you put sql database in app_data folder then connect it?

    Try it, i guess you are able to connect sql server database file (sql express) without installing sql server instance.

    If you still want to use access database, parameters in access is deferent with sql parameter

    In access, actualy does not support parameter name

    update tbl set colb=?, colc=? where cola=?

    You should add

    parameters[0] for colb

    parameters[1] for colc

    parameters[2] for cola

    It's depend parameters squence.

    If you update the data based id value, id parameter should last parameter

    because param id typed at last : ....... where id=@id

    care about the squence order whatever parameter name

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 6, 2014 8:04 PM
  • User986700334 posted

    Awesome.  That did the trick.  I've seen that notation before, but since the parameters are working in the SELECT query, it didn't occur to me to try that instead of the named parameters.  

    Thank you!

    Thursday, November 6, 2014 11:21 PM