Updating a Database in C#

Nov 12 2009 4:48 PM

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
{
  /// <summary>
  /// Interaction logic for EventWindow.xaml
  /// </summary>
  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 ***
 

}
}

 

Answers (9)