Updating a Database in C#
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 ***
}
}