I am building a simple c# windows form to connect to an access database. I tried
ID: 3578529 • Letter: I
Question
I am building a simple c# windows form to connect to an access database. I tried using OleDb and SQL statements, but I couldn't get it to function correctly. So I decided to try using the tableadapter, but it is not updating the way I thought it was supposed to be updating. I have followed the directions on msdn.microsoft.com, but it is frustrating that nothing is working correctly. Here is some of the code - the first method is to confirm an add row. The row is added, update appears to work, but the datagridview populates the ID column line one with the data for the new row (copies over the old data) AND adds a new row with the same data. I can't figure out why it would populate the first row of data... Due tonight at midnight. I've about given up on this... I need to write the "report" part of the assignment, but the program is broken...
private void btnConfirm2_Click(object sender, EventArgs e)
{
Int32 count = 0;
//Create connection object from class InvDataDB
OleDbConnection connection = InvDataDB.GetConnection();
//Set SQL query Statement to count rows
string queryStatement =
"SELECT COUNT(*) FROM InventoryMain";
//create command object for counting rows
OleDbCommand rowCount = new OleDbCommand(queryStatement, connection);
connection.Open();
count = Convert.ToInt32(rowCount.ExecuteScalar());
//if count is more than zero (records exist) add one to count to add a new row
//if not, advise of error
if (count > 0)
{
count = count + 1;
}
else
{
MessageBox.Show("Error, the count is showing zero rows.");
this.Close();
}
//set txtID to the new count
txtID.Text = count.ToString();
//connection.Close();
InvDataDataSet.InventoryMainRow newInventoryMainRow =
invDataDataSet.InventoryMain.NewInventoryMainRow();
//convert ID field to short
short s;
s = short.Parse(this.txtID.Text);
MessageBox.Show("s is: " + s);
//updates to columns
newInventoryMainRow.ID = s;
newInventoryMainRow.Title = txtTitle.Text;
newInventoryMainRow.ISBN = txtISBN.Text;
newInventoryMainRow.AL1 = txtAuthorL1.Text;
newInventoryMainRow.AF1 = txtAuthorF1.Text;
newInventoryMainRow.AL2 = txtAuthorL2.Text;
newInventoryMainRow.AF2 = txtAuthorF2.Text;
newInventoryMainRow.IL = txtIllustratorL.Text;
newInventoryMainRow.IF = txtIllustratorF.Text;
newInventoryMainRow.Retail = txtPrice.Text;
newInventoryMainRow.Wholesale = txtWholesale.Text;
newInventoryMainRow.InStock = txtInStock.Text;
newInventoryMainRow.Reorder = txtReorder.Text;
//adds row to dataset
invDataDataSet.InventoryMain.Rows.Add(newInventoryMainRow);
//reset the buttons and fields on the form
resetForm();
updateDatabase();
this.tableAdapterManager.UpdateAll(this.invDataDataSet);
}
This is the update method:
private void updateDatabase()
{
this.Validate();
this.inventoryMainBindingSource.EndEdit();
InvDataDataSet.InventoryMainDataTable deletedInventoryMain = (InvDataDataSet.InventoryMainDataTable)
invDataDataSet.InventoryMain.GetChanges(DataRowState.Deleted);
InvDataDataSet.InventoryMainDataTable newInventoryMain = (InvDataDataSet.InventoryMainDataTable)
invDataDataSet.InventoryMain.GetChanges(DataRowState.Added);
InvDataDataSet.InventoryMainDataTable modifiedInventoryMain = (InvDataDataSet.InventoryMainDataTable)
invDataDataSet.InventoryMain.GetChanges(DataRowState.Modified);
try
{
if (deletedInventoryMain != null)
{
inventoryMainTableAdapter.Update(deletedInventoryMain);
}
if (newInventoryMain != null)
{
inventoryMainTableAdapter.Update(newInventoryMain);
}
if (modifiedInventoryMain != null)
{
inventoryMainTableAdapter.Update(modifiedInventoryMain);
}
this.inventoryMainTableAdapter.Update(this.invDataDataSet.InventoryMain);
MessageBox.Show("Update successful");
inventoryMainDataGridView.DataSource = null;
inventoryMainDataGridView.DataSource = invDataDataSet;
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed");
}
finally
{
if (deletedInventoryMain != null)
{
deletedInventoryMain.Dispose();
}
if (newInventoryMain != null)
{
newInventoryMain.Dispose();
}
if (modifiedInventoryMain != null)
{
modifiedInventoryMain.Dispose();
}
}
}
Explanation / Answer
add the code in bold to the try block
try
{
if (deletedInventoryMain != null)
{
inventoryMainTableAdapter.Update(deletedInventoryMain);
}
if (newInventoryMain != null)
{
inventoryMainTableAdapter.Update(newInventoryMain);
}
if (modifiedInventoryMain != null)
{
inventoryMainTableAdapter.Update(modifiedInventoryMain);
}
// this.inventoryMainTableAdapter.Update(this.invDataDataSet.InventoryMain);
invDataDataSet.AcceptChanges();
MessageBox.Show("Update successful");
inventoryMainDataGridView.DataSource = null;
inventoryMainDataGridView.DataSource = invDataDataSet;
}
It is better to use oledb connection string
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:myFoldermyAccessFile.accdb;
Persist Security Info=False;
There first u should verify the driver version in the PC u are using and the respective string need to be written there.Also the access database name and its path where it is stored in ur PC should also be given correctly
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.