Writing update back to MSAccess table?

[planetthoughtful]

Hi All,

I'm building a Windows Form application that will use a Microsoft
Access mdb file as a data store.

I have a section of code that is executed when the main form is closed
that _should_ update a setting value in a table in my mdb file. The
code executes without any error, but the value in the table appears
unchanged when I open up the table in the mdb file. I call this
function in the FormClosed event of the app's main form.

Code for the function is below:

private void fnSetRewardRemain()
{
using (OleDbConnection conn = new
OleDbConnection(ConnString)){
string thisval = "20";
string CommandString = "UPDATE tblSettings SET
settingval = '" + thisval + "' WHERE setting = 'remainreward'";
OleDbCommand remcom = new OleDbCommand(CommandString,
conn);
remcom.Connection.Open();
remcom.ExecuteNonQuery();
remcom.Dispose();
conn.Close();
}
}

In the example I'm attempting to write the 'hard coded' value of '20'
back to the table. If I write the value of CommandString out to the
console, and manually run it as a query in the mdb file itself, it
works fine. I'm assuming that I'm supposed to do something to 'flush'
the update back to the database, but I'm not sure what. If it helps,
the mdb was created in Acc2003 and is the 2002-2003 compatible file
structure.

Can anyone help me figure out how to get the update to write back to
the actual database?

Many thanks in advance,

pt

 

[Nicholas Paldino [.NET/C# MVP]]

My guess is that the query is actually wrong. The code here is solid
(with the exception of maybe a using statement on the command, but that's a
minor oversight, as well as the fact that you should parameterize this
query). You don't have to do a flush or anything, the provider will take
care of everything.

Are you sure that the tblSettings table has a row where the value of the
setting column is "remainreward"?

If so, then can you provide a complete example (along with mdb file)?

 

[planetthoughtful]

My guess is that the query is actually wrong. The code here is solid
(with the exception of maybe a using statement on the command, but that's a
minor oversight, as well as the fact that you should parameterize this
query). You don't have to do a flush or anything, the provider will take
care of everything.

Are you sure that the tblSettings table has a row where the value of the
setting column is "remainreward"?

If so, then can you provide a complete example (along with mdb file)?

Hi Nicholas,

When the application is first executed, it reads the row with a
[setting] value of 'remainreward' successfully, executing this SQL
statement: SELECT settingval from tblSettings WHERE setting =
'remainreward'

This currently returns a value of '15', which is what is in the table
for this record.

The SQL being executed to update this row when the application closes
is (this is copied from writing the value of CommandString out via
Console.WriteLine):

UPDATE tblSettings SET settingval = '20' WHERE setting = 'remainreward'

However, the value against 'remainreward' in the settingval column
always stays as '15', and never actually updates to '20', as expected.

All the best,

pt

 

[planetthoughtful]

Hi Nicholas,

When the application is first executed, it reads the row with a
[setting] value of 'remainreward' successfully, executing this SQL
statement: SELECT settingval from tblSettings WHERE setting =
'remainreward'

This currently returns a value of '15', which is what is in the table
for this record.

The SQL being executed to update this row when the application closes
is (this is copied from writing the value of CommandString out via
Console.WriteLine):

UPDATE tblSettings SET settingval = '20' WHERE setting = 'remainreward'

However, the value against 'remainreward' in the settingval column
always stays as '15', and never actually updates to '20', as expected.

Okay, it seems this is a result of having 'Copy To Output Directory'
set to 'Copy always' in the properties of the mdb file in Solution
Explorer (right-click on mdb file in Solution Explorer and select
Properties). Changining this to 'Copy if newer' (which I assume means
that if I change the structure of the mdb file by adding / removing /
changing tables etc) seems to be the fix - I assume that with 'Copy
always' the 'master' copy of the mdb, which had a value of '15' against
'remainreward' in tblSettings, was being copied over the 'working' copy
of the mdb.

Hope this helps someone else in future.

Much warmth,

pt