TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Edward Burrell
NA
10
0
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 ***
}
}
Reply
Answers (
9
)
Bulk Copy error The given value of type String from the data source cannot be converted to type nchar of the specified target column.
How to retrive emails,attachments from exchange server using C#.NET