|
I'm new to C# and am trying to access and update a Access Database. I've managed to create the database and can see the results in Access. I'm also able to open the Database and load the tables, but when I try to do an update it does not post back my changes. Below is the code with comments where things seem to fail. I do not get an exception, I just don't see the changes in the database file.
// This method seems to work fine, it loads the data from the database into the six tables.
public void openDatabase(DataSet ds, string filename)
{
// I have 6 tables in the dataset/database.
string[] names = { "Project", "Portfolios", "Equations", "Scenarios", "Reports", "Rules" };
string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename;
for (int i = 0; i < names.Length; i++)
{
string strAccessSelect = "SELECT * FROM " + names[i];
try
{
myAccessConn = new OleDbConnection(strAccessConn);
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
return;
}
try
{
OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
myDataAdapter = new OleDbDataAdapter(myAccessCommand);
myAccessConn.Open();
myDataAdapter.Fill(ds, names[i]);
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return;
}
finally
{
myAccessConn.Close();
}
}
}
// This is the method I'm having trouble with, I sent the Dataset off to another routine which changes some
// of the datarows, I can see the changes there in the dataset but update does not write them to disk.
public void updateDatabase(DataSet ds)
{
try
{
dw = new DebugWindow(); // This opens up a window to display the dataset
dw.Show();
dw.dumpDataSet(ds); // This dumps the dataset to the window, the dataset looks correct with my changes
myAccessConn.Open(); // I don't think I need to do an open as the update should do this but I'm trying all kinds of things
ds.AcceptChanges(); // Don't think I need this
// This seems to be the line that does not work. I've also tried (ds, "Rules") as well. Ideally, I would
// just pass in the dataset and all the tables would be updated.
myDataAdapter.Update(ds.Tables["Rules"]);
myAccessConn.Close();
}
catch (System.Exception ex)
{
dw.displayMsg("Update Failed" + ex.Message);
}
}
|
|
|
|
|
DataAdapters are very simple; they can only do very simple things. They are really only good for writing quick-and-dirty demoes at Microsoft launch events. Any non-trivial application will quickly become complex enough that a DataAdapter will be of no use -- especially Update.
I don't use DataAdapters and neither should you (or anyone else for that matter); they cause more trouble than they're worth. You would be much better off in the long run, learning how to access databases at a lower level.
ExecuteScalar, ExecuteNonQuery, DataAdapter.Fill, and DataAdapter.Update all use ExecuteReader (and therefore DataReaders); learn to use DataReaders.
(I think part of your problem above may be that myDataAdapter gets changed between calls to openDatabase and updateDatabase. But the whole thing should be rewritten.  )
|
|
|
|
|
Thank you for your insight. I'll look into using a DataReader. When I first implemented this I actually read the .mdb in binary format and converted it line by line. It seems most books point to the DataAdapters, but as you say these seem only good for the quick and dirty. Thanks.
|
|
|
|
|
Oi Adam
Listen to what Piebald said, quick and dirty or DO NOT USE THEM. Learn about a Data Access Layer (DAL) and implement one of the many existing ones available here in the articles.
Most accomplished developers build there own, I see a lot of new devs using the Enterprise library for their DAL these days.
|
|
|
|
|
You can listen to the other peoples' advice to use ExecuteNonQuery(), etc. However, I am going to just talk about your piece of code.
The adapter maintains four different query commands internally. One of them is the update query you implicitly called when you call myDataAdapter.Update(...). However, in order for it to work, the adapter has to know how to correctly construct the update query internally. What you need to make this program to work is to add the following line to tell the adapter how to construct the query internally:
OleDbCommandBuilder builder=new OleDbCommandBuilder(myDataAdapter);
Add the above line right after the line:
myDataAdapter = new OleDbDataAdapter(myAccessCommand);
After that, your Update(...) call should work.
|
|
|
|
|
Thanks dxlee, I gave this a try but update still does not seem to write to the disk. I tried to cut things down to one table as I think flipping between them is causing problems. I'll look more into the OleDBCommandBuilder. Thanks.
|
|
|
|
|
We need to use MSMQ in one of my project. Is there any Library(Similar to enter prise library DAAB) available that will make the task easy and manageable.
Also it would be great if you can point me to some MSMQ starter kits or real life sample applications that uses MSMQ
Thanks in advance
|
|
|
|
|
|
Spring.NET might help, it has some MSMQ support although I have never used it. Spring JMS is very easy to use, so if the .NET MSMQ equivalent is similar it is probably worth looking into.
|
|
|
|
|
I published my C# application using Visual Studio 2005 to a server. I then ran the setup to install the app on my computer ... no problem, app runs fine. Then, I had another analyst in the office do the same to their computer ... again, no problem and app runs fine. The manager tried it to his computer and although the install seemd to go fine, there is a problem, app does not appear.
When he starts the application, nothing appears on the screen. The app appears in the task manager and is running but it is no where to be found on the screen nor when Shift-Tab are pressed.
Am I missing something? This reminds me of imaging a computer and then applying it to other computers during a deployment. The computer environment has to be similar (exact) or there will be problems. Does something similar happen when trying to deploy a published app to various machines (enviroments).
BTW, the app works fine on the manager's computer if the source is run from within Visual Studio using Start Without Debugging.
This is perplexing and any advise will be much appreciated.
|
|
|
|
|
Hi all.
Is it possible to change type of calendar in DateTimePicker ?
e.g : I'm gonna replace Persian Calendar with the default calendar in DateTimePicker !
Thanks
|
|
|
|
|
No.. It wont be. You need to use any Javascript calendar.. 
|
|
|
|
|
Dear All,
Im making a windows service in vs 2008. it has to perform a task once a day at 12:00 AM.
What is the best way to accomplish this? using Timers? or other ways?
Thanks in advance.
|
|
|
|
|
Does it really have to be a windows service if you only need to run it once every day at a certain time? Maybe you could schedule windows to run your program att 12AM instead?
|
|
|
|
|
Hi Calla,
Thanks for response.
It has to be a win service that automatically runs at that time and not throug windows
|
|
|
|
|
Priya Prk wrote: It has to be a win service
Why? What will the task do?
It's probably not the best technique. If it's a homework assignment, then it's OK. But if it's a work assignment then you should take our suggestions and comments to your boss and ask if he agrees with us. A smart boss will see our point, but there could be other concerns you don't know about and haven't presented. For instance, this may be just the first phase of a larger scheduling project.
Priya Prk wrote: not throug windows
It's going to be "through Windows" either way.
P.S.
Priya Prk wrote: that automatically runs at that time
Do you understand that a Windows Service is always running? (Unless you stop it of course.)
|
|
|
|
|
I would use a Windows Scheduled Task; not a Windows Service.
I only use a Windows Service for things that need to happen much more frequently (perhaps up to every ten minutes). Even things that are to happen every hour are better done with a task, in my opinion.
What will the process do?
|
|
|
|
|
I worked with events (cause your service should be able to stop on request) and used a loop like this.
timeToWait = CalcMillisecondsTillMaintainceFromNow();
while(true)
{
if(WaitForEvents(timeToWait) == event fired)
timeToWait = CalcMillisecondsTillMaintainceFromNow();
else
InvokeMaintanceRoutine();
}
Greetings
Covean
|
|
|
|
|
If it has to run only once a day, you don't need a Windows Service because that is not what it was made for . Instead, create a console application that does your work and use Task Scheduler to run your app at 12:00 AM.
|
|
|
|
|
Sorry I voted your answer with 1, cause you are the 3th one who said to use the task planer and he also said it has to be a service!
Greetings
Covean
|
|
|
|
|
May be the OP does not know what he/she wants. That is why I suggested console app + Task schduler will be a better candidate than a Windows Service. Windows Service is NOT designed to act like a task scheduler.
|
|
|
|
|
But you were the third one who said exactly the same! And this even after the OP said it has to be a service!
Greetings
Covean
|
|
|
|
|
The Windows Task Scheduler is a service. Maybe the OP doesn't know that.
|
|
|
|
|
Maybe the OP has known that the Windows Task Scheduler is a service but its simply not available (cause as a result of some security issue they disabled the task planer) on the machine he has to do his maintenance or something else.
Greetings
Covean
|
|
|
|
|
So, from when have you started mind-reading? As technology specialists, our job is not just to give answers, but the best answers, and that is what I am trying to do here. The OP did not mention anywhere that Task Scheduler cannot be used. What I gave is a general idea.
I would suggest that you try to help OP on your own rather than concentrating on others' answers and rating them down (insanely and for imaginary reasons).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Badger | [badger,badger,badger,badger...]](https://www.codeproject.com/script/Forums/Images/badger.gif)