Updating a Database in C#

Edward Burrell

OK, I'm a newbie, and need help. I am trying to interface a C# program with a MS Access database, and can connect, get data and navigate through the dataset, but am having trouble updating the database. If anyone could take a look at what I have and give me some suggestions, it would be greatly appreciated.

The error seems to be in the BuildCommands() section, but I am not sure what I did wrong. Thanks in advance.

The code is as follows:
namespace PlantTrackerWPF
{
///

/// Interaction logic for EventWindow.xaml
///

public partial class EventWindow : Window
{
// +++ ADO.NET Objects +++
//
// MS Access connection String.
public static string connectionString = @"Provider=Microsoft.JET.OLEDB.4.0; data source = C:\Plant Tracker\PlantTracking.mdb";
private OleDbDataAdapter dataAdapter;
private DataSet dataSet;
private DataTable dataTable;
private OleDbConnection conn;
// *** END ADO.NET Objects ***

// +++ Database Objects +++
private static string id;
private static string eventType;
private static int unitNo;
private static DateTime startTime;
private static DateTime endTime;
private static decimal derate;
private static Boolean trip;
private static string comments;
private int indexNumber = 0;
// *** END Database Objects

public EventWindow()
{
    InitializeComponent();

    eventTypeCmboBx.Items.Add("SO");
    eventTypeCmboBx.Items.Add("FO");
    eventTypeCmboBx.Items.Add("DR");

    // Default select command on the EventData Table.
    string commandString = "select * from EventData";
    // The link between sql command and the database connection
    dataAdapter = new OleDbDataAdapter(commandString, connectionString);
    // define insert, update and delete sql commands to use.
    BuildCommands();
    // Declare and fill the in-memory dataset from the database.
    dataSet = new DataSet();
    dataSet.CaseSensitive = true;
    dataAdapter.Fill(dataSet, "EventData");
    // Fill form with first record at loading.
    FillForm(indexNumber);
} // *** END EventWindow ***
private void FillForm(int rowID)
{
    dataTable = dataSet.Tables[0];
    DataRow dataRow = dataTable.Rows[rowID];
eventIDTxtBx.Text = dataRow["ID"].ToString().Trim();
    eventTypeCmboBx.Text = dataRow["EventType"].ToString().Trim();
    unitNoTxtBx.Text = dataRow["UnitNo"].ToString().Trim();
    startTxtBx.Text = dataRow["Start"].ToString().Trim();
    endTxtBx.Text = dataRow["End"].ToString().Trim();
    derateTxtBx.Text = dataRow["Derate"].ToString().Trim();
   commentsTxtBox.Text = dataRow["Comments"].ToString().Trim();
   tripChkBx.IsChecked = (Boolean)dataRow["Trip"];

} // *** END FillForm ***
private void BuildCommands()
{
      //Use the select comand's connection again
      OleDbConnection connection = (OleDbConnection)dataAdapter.SelectCommand.Connection;
      //Declare a reusable insert command with parameters
      dataAdapter.InsertCommand = connection.CreateCommand();
      dataAdapter.InsertCommand.CommandText =
         "insert into EventData " +
         "(ID, EventType, UnitNo, Start, End, Derate, Trip, Comments) " +
         "values " +
         "(?,?,?,?,?,?,?,?)";
      dataAdapter.InsertCommand.Parameters.Add("ID", OleDbType.Integer, 0, "ID");
      dataAdapter.InsertCommand.Parameters.Add("EventType", OleDbType.Char, 0, "EventType");
      dataAdapter.InsertCommand.Parameters.Add("UnitNo", OleDbType.Integer, 0, "UnitNo");
      dataAdapter.InsertCommand.Parameters.Add("Start", OleDbType.Date, 0, "Start");
      dataAdapter.InsertCommand.Parameters.Add("End", OleDbType.Date, 0, "End");
      dataAdapter.InsertCommand.Parameters.Add("Derate", OleDbType.Decimal, 0, "Derate");
      dataAdapter.InsertCommand.Parameters.Add("Trip", OleDbType.Boolean, 0, "Trip");
      dataAdapter.InsertCommand.Parameters.Add("Comments", OleDbType.Char, 0, "Comments");
      //Declare a reusable update command with parameters.
      dataAdapter.UpdateCommand = connection.CreateCommand();
      dataAdapter.UpdateCommand.CommandText =
         "update EventData " +
         "set EventType = ?, UnitNo = ?, Start = ?, End = ?, Derate = ?, Trip = ?, Comments = ? " +
         "where ID = ?";
      dataAdapter.UpdateCommand.Parameters.Add("EventType", OleDbType.Char, 0, "EventType");
      dataAdapter.UpdateCommand.Parameters.Add("UnitNo", OleDbType.Integer, 0, "UnitNo");
      dataAdapter.UpdateCommand.Parameters.Add("Start", OleDbType.Date, 0, "Start");
      dataAdapter.UpdateCommand.Parameters.Add("End", OleDbType.Date, 0, "End");
      dataAdapter.UpdateCommand.Parameters.Add("Derate", OleDbType.Decimal, 0, "Derate");
      dataAdapter.UpdateCommand.Parameters.Add("Trip", OleDbType.Boolean, 0, "Trip");
      dataAdapter.UpdateCommand.Parameters.Add("Comments", OleDbType.Char, 0, "Comments");
      dataAdapter.UpdateCommand.Parameters.Add("ID", OleDbType.Integer, 0, "ID");
      // Declare a reusable delete command with parameters
      dataAdapter.DeleteCommand = connection.CreateCommand();
      dataAdapter.DeleteCommand.CommandText = "delete from EventData where ID = ?";
      dataAdapter.DeleteCommand.Parameters.Add("ID", OleDbType.Integer, 0, "ID");
    } // *** END BuildComands()
   private void exitMenuItem_Click(object sender, RoutedEventArgs e)
   {
      this.Close();
   }
   private void Window_Loaded(object sender, RoutedEventArgs e)
   {
      //TO DO!!
   }
   private void firstRecord_Click(object sender, RoutedEventArgs e)
   {
      FillForm(0);
   }
   private void Image_ImageFailed(object sender, ExceptionRoutedEventArgs e)
   {
   }
   private void previousRecord_Click(object sender, RoutedEventArgs e)
   {
      if (indexNumber >= 0)
      {
         indexNumber -= 1;
         FillForm(indexNumber);
      }
      else
      {
         MessageBox.Show("At the beginning of the database.");
      }
   }
   private void nextRecord_Click(object sender, RoutedEventArgs e)
   {
      if (indexNumber < dataTable.Rows.Count)
      {
         indexNumber += 1;
         FillForm(indexNumber);
      }
      else
      {
      MessageBox.Show("At the end of the database.");
      }
   }
   private void lastRecord_Click(object sender, RoutedEventArgs e)
   {
      FillForm(dataTable.Rows.Count-1);
   }
   private void newRecord_Click(object sender, RoutedEventArgs e)
   {
      //Get number of records and last ID number
      int numberOfRows = dataTable.Rows.Count;
      DataRow dataRow = dataTable.Rows[numberOfRows-1];
      int newIdNo = (int)dataRow["ID"] + 1;
      eventIDTxtBx.Text = newIdNo.ToString();
      eventTypeCmboBx.Text = "";
      unitNoTxtBx.Text = "";
      startTxtBx.Text = "";
      endTxtBx.Text = "";
      derateTxtBx.Text = "";
      tripChkBx.IsChecked = false;
      commentsTxtBox.Text = "";
   }
   private void newRecordInsert()
   {

      // Create a new row and populate it.
      DataRow newRow = dataTable.NewRow();
      newRow["ID"] = eventIDTxtBx.Text;
      newRow["EventType"] = eventTypeCmboBx.Text;
      newRow["UnitNo"] = unitNoTxtBx.Text;
      newRow["Start"] = startTxtBx.Text;
      newRow["End"] = endTxtBx.Text;
      newRow["Derate"] = derateTxtBx.Text;
      newRow["Trip"] = tripChkBx.IsChecked;
      newRow["Comments"] = commentsTxtBox.Text;
      //Update the database
      try
      {
      dataSet.Tables["EventData"].Rows.Add(newRow);
      dataAdapter.Update(dataSet, "EventData");
      dataSet.AcceptChanges();
      // inform the user.
      MessageBox.Show("Update Successful");
   }
   catch(OleDbException ex)
   {
      dataSet.RejectChanges();
      MessageBox.Show(ex.Message);
      MessageBox.Show(ex.ErrorCode.ToString());
   }

   }// *** END nerRecordInsert ***
   private void saveRecord_Click(object sender, RoutedEventArgs e)
   {
      newRecordInsert();
      } // *** END saveRecord_Click ***

}
}

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs17y ago
Accepted answer
In newRecordInsert, put a breakpoint on the line:
dataAdapter.Update(dataSet, "EventData");
Do you know how to use breakpoints? You can put a breakpoint on the line by putting the cursor on the line, then pressing F9. A red dot will appear in the margin to the left of the line. Then debug, which can be done using F5. This will be a problem if the error does not occur on the first record and there are many records. While debugging, you can execute code and then break again at the breakpoint if you press F5 again during debugging. If this is new to you, then you need to spend some time learning about debugging.

While debugging, look at the data. You can look at the data going into the newRow. My guess is that one of the fields, probably the Comments, has some characters that are confusing SQL. If so, then no one could find the problem if they have all the source code since the problem is probably in the data. You can and should fix the program so that it does not get an error like this, but diagnosis of the problem might not be possible without the data too. It will be much, much easier if you simply look at the data. If the data is the problem, then you will see the problem very quickly and conveniently just by looking at the data.

Another way to debug would be to put a breakpoint on the line "dataSet.RejectChanges();" in the catch block in newRecordInsert. That way, the breakpoint will break only when there is an error. You can then look at the data that was being processed when the error occurs.

Please look at the data.
Sam Hobbs17y ago
If I helped, then you can show appreciation and give credit where credit is due simply by marking an reply as an answer.
Edward Burrell17y ago
I figured it out! I was using "End" as a column name and End is a reserved word in SQL. This is what was driving the error. I found the item on a MSDN link. After changinf the End field name to EndTime, it works perfect!

Thanks for all your help! :)

Edward Burrell17y ago

Ok, I think it is in my BuildCommands section and the data types I am assigning. Here it goes for one more chance.
The buldcommands method looks like this:

 
private void BuildCommands()
{
//Use the select comand's connection again
OleDbConnection connection = (OleDbConnection)dataAdapter.SelectCommand.Connection;
//Declare a reusable insert command with parameters
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText =
"insert into EventData " +
"(ID, EventType, UnitNo, Start, End, Derate, Trip, Comments) " +
"values " +
"(?,?,?,?,?,?,?,?)";
dataAdapter.InsertCommand.Parameters.Add("ID", OleDbType.BigInt, 0, "ID");
dataAdapter.InsertCommand.Parameters.Add("EventType", OleDbType.Char, 0, "EventType");
dataAdapter.InsertCommand.Parameters.Add("UnitNo", OleDbType.Integer, 0, "UnitNo");
dataAdapter.InsertCommand.Parameters.Add("Start", OleDbType.Date, 0, "Start");
dataAdapter.InsertCommand.Parameters.Add("End", OleDbType.Date, 0, "End");
dataAdapter.InsertCommand.Parameters.Add("Derate", OleDbType.Decimal, 0, "Derate");
dataAdapter.InsertCommand.Parameters.Add("Trip", OleDbType.Boolean, 0, "Trip");
dataAdapter.InsertCommand.Parameters.Add("Comments", OleDbType.Char, 0, "Comments");
//Declare a reusable update command with parameters.
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = 
"update EventData " +
"set EventType = ?, UnitNo = ?, Start = ?, End = ?, Derate = ?, Trip = ?, Comments = ? " +
"where ID = ?";
dataAdapter.UpdateCommand.Parameters.Add("EventType", OleDbType.Char, 0, "EventType");
dataAdapter.UpdateCommand.Parameters.Add("UnitNo", OleDbType.Integer, 0, "UnitNo");
dataAdapter.UpdateCommand.Parameters.Add("Start", OleDbType.Date, 0, "Start");
dataAdapter.UpdateCommand.Parameters.Add("End", OleDbType.Date, 0, "End");
dataAdapter.UpdateCommand.Parameters.Add("Derate", OleDbType.Decimal, 0, "Derate");
dataAdapter.UpdateCommand.Parameters.Add("Trip", OleDbType.Boolean, 0, "Trip");
dataAdapter.UpdateCommand.Parameters.Add("Comments", OleDbType.Char, 0, "Comments");
dataAdapter.UpdateCommand.Parameters.Add("ID", OleDbType.BigInt, 0, "ID");
// Declare a reusable delete command with parameters
dataAdapter.DeleteCommand = connection.CreateCommand();
dataAdapter.DeleteCommand.CommandText = "delete from EventData where ID = ?";
dataAdapter.DeleteCommand.Parameters.Add("ID", OleDbType.BigInt, 0, "ID");
} // *** END BuildComands()

The section where I add a new row and assign the data and update looks like this:

 
private void newRecordInsert()
{


// Create a new row and populate it.
DataRow newRow = dataTable.NewRow();
newRow["ID"] = Convert.ToInt64(eventIDTxtBx.Text);
newRow["EventType"] = eventTypeCmboBx.Text;
newRow["UnitNo"] = Convert.ToInt32(unitNoTxtBx.Text);
newRow["Start"] = Convert.ToDateTime(startTxtBx.Text);
newRow["End"] = Convert.ToDateTime(endTxtBx.Text);
newRow["Derate"] = Convert.ToDecimal(derateTxtBx.Text);
newRow["Trip"] = tripChkBx.IsChecked;
newRow["Comments"] = commentsTxtBox.Text;
//Update the database
try
{
dataSet.Tables["EventData"].Rows.Add(newRow);
dataAdapter.Update(dataSet, "EventData");
dataSet.AcceptChanges();
// inform the user.
MessageBox.Show("Update Successful");
}
catch(OleDbException ex)
{
dataSet.RejectChanges();
MessageBox.Show(ex.Message);
MessageBox.Show(ex.ErrorCode.ToString());
}
 
}// *** END nerRecordInsert ***

My database uses the following fields with given type:
ID - AutoNumber - Long Integer
EventType - Text - Field Size = 255
UnitNo - Number - Integer
Start - Date/Time
End - Date/Time
Derate - Number - SIngle
Trip - Yes/No - True/False
Commnets - Memo

Any ideas?

Thanks,

Ray

Sam Hobbs17y ago
I am not aware of any way this site offers for transferring code; I think they should. They don't even make it easy to post code in a conveniently formatted manner.

I would prefer to not look at the entire project, especially if the problem is in the data. Did you look at the data as I suggested?
Edward Burrell17y ago
OK, from the debuging, the InnerException is null.
Does this help? line 230 is the line it gets the error on.
StackTrace " at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)\r\n at PlantTrackerWPF.EventWindow.newRecordInsert() in C:\\Users\\rburrell\\Documents\\Visual Studio 2008\\MyPrograms\\PlantTrackerWPF\\PlantTrackerWPF\\EventWindow.xaml.cs:line 230" string

If it would help, I would be glad to zip up the whole project and send it to you. Can you do that on this site?
Sam Hobbs17y ago
Put a breakpoint in the catch block, then debug and in the catch block, check to see if ex.InnerException is not null; if it is not, then it might have useful information. Also look at the values being inserted; the data might have characters causing the insert command to have incorrect syntax.
Edward Burrell17y ago
Sorry about that. The error that I get is -- "Syntax error in INSERT INTO statement".
The program kicks it out in the try block of the newRecordInsert() method and the line that it hits on is the 2nd inside the try block that states:

dataAdapter.Update(dataSet, "EventData");
After this line, the program goes directly to the catch block.
Thanks again.
Sam Hobbs17y ago
It would really help if you can be sure where the error is; what line of code is the error occuring. Also, it really helps if you provide the error message.