I am working in Visual C# Express Edition, and I am using the Update() method of a TableAdapter.
I am using the following C# code to update the database:
try
{
this.Validate();
this.statsDataSetBindingSource.EndEdit();
this.runsTableAdapter.Update(this.statsDataSet.Runs);
MessageBox.Show("Update successful");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
I have tried the following two work arounds:
(1) I followed the advice in the Visual Basic forum that suggests setting
AttachDbFileName to point to the copy of the file in the ...\bin\Debug folder when
the .mdf file is local. This also involves setting “Copy to Output Directory”
to “Do not copy.”
(2) I tried the suggestion of putting the .mdf file outside the project
hierarchy. This also involves being sure to copy the updated file from the database
folder (outside the hierarchy) to the …\bin\Debug folder in the hierarchy.
Neither of these measures solves the problem, so let me clarify
further.
When I run the code the MessageBox pops up to give me the message, “Update
succesful.” Indeed, the data in the DataSet have changed, as evidenced in the DataGridView
connected to the DataSet through a BindingSource.
Then another MessageBox pops up with a message whose content is not
germaine to this question. What does matter is that after I click the OK button
for this second MessageBox, the aforementioned TableAdapter’s Fill() method is
executed and the data in the DataSet return to the original values—the values
that were in the .mdf file before the Update() method was executed. This again
can be seen in the DataGridView. The Fill() method is filling the DataSet with the old data.
This leads me to believe that the .mdf file is never actually getting
updated.
Thanks,
CaveCoder
Data changes not being saved to the .mdf file
Phil Price
And you have only one place with Update() I mean there is no some code in Form.Closing event that can update data again
zagolin
CaveCoder
yorengoy
Hi!
I think something else happens after this code. You can make test, after update code, create new empty dataset and read data in it. I think you will see that database have changes. Is it possible that your code do another update somewhere else with old data
Mike Haladjian
ndarwish
Hi CaveCoder,
I had the same problem and I've seen a blog with a temporary workaround. It involves modifying the project's config.file. Open the config file and substitute |DataDirectory| with the actual path of the mdf file (hard code it) in the 'connnection string' section. Try it and let me know how it turns out. Good luck!!
Redhaze
Hi CaveCoder,
Your connection string seems a bit strange. Mine looks like this:
Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Data Access\TestingMDF\TestingMDF\Contacts.mdf;Integrated Security=True;User Instance=True
"Does this help
bmm22
Thanks for your suggestion. Before I try it, let me give you one more piece of information. When I exit from the app and then run it again, the TableAdapter's Fill() method populates the DataGridView with the old data.
I took this to mean that the .mdf file still has the old data in it instead of the new. It's as if the new data were never stored in the file. Do you agree
Another thing puzzles me. When the Update() method executes, the time stamp on the .mdf file changes, but a subsequent Fill() brings in the old data. Then too, when I exit from the app, the time stamp on the .mdf file changes again. Why is the .mdf file being saved when I exit from the app, and why is it being filled with the old data
Thanks,
CaveCoder
cooz
CaveCoder
dpolyakov
Hi Sergey,
I'm using Vb 2005 Standard but I'm having the same problem. It must be another of those major MS bugs. I'm getting soooo turned off.
cosmopoet
You not using any transactions that may rollback changes
narendrakg
You have both zeroed in on my connectionString, so I will attempt to answer you both in this post.
Here is the connectionString from my app.config file:
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="G:\Projects\DB\stats.mdf";Integrated Security=True;ConnectTimeout=30;UserInstance=True"
You can see that the |DataDirectory| reference has been replaced with the actual path to the database file.
I'm afraid this does not help. The undesired behavior persists.
CaveCoder
Very Anonymous
Hi Franco22,
If I compare our two connectionStrings and remove the parts that are the same, I get:
Your string:
"Data Source=AttachDbFilename=D:\Data Access\TestingMDF\TestingMDF\Contacts.mdf;"
My string
"Data Source=AttachDbFilename="G:\Projects\DB\stats.mdf";Connect Timeout=30"
One difference between them is that mine has a ConnectTimeout setting. I removed that and ran again. The behavior did not change.
Was the "Connect Timeout=30" what you were calling strange, or was it the """ expressions surrounding the full path to the database file
Srini Kuchi
All code and connection strings looks very familiar and must work, except UserInstance in connection string, I never used it before.
I'm reading about UserInstance parameter right now, can you try remove it Will it change behavior
kath
"I'm reading about UserInstance parameter right now, can you try remove it Will it change behavior "
As you suggested I removed "User Instance=True" from the connectionString. The behavior is unchanged.
Many thanks to you, Sergey, and to Franco22 for your efforts to help me.
CaveCoder