C# WinForms - how to send updates from DataGridView to DataBase
I have a .mdb file with a Customers table and an Agents table. The only thing that the Agents table does as yet is populate the Agent dropdown for each customer...
I have a DataGridView linked to the customerBindingSource. The customerBindingSource has DataMember set to Customer and DataSource set to bindingSource1. This has the DataSource set to customerAppDS21. If I select customerAppDS21 and click Edit in DataSet Designer I can quite clearly see that there is a Customer table and Agent table. These were dragged directly from the Data.mdf > Tables folder. I have been through the Configure wizard and checked that the Update, Insert and Delete commands are generated correctly.
I am setting the unique ID (GUID) when the user leaves a row or adds a row (I dont think both are needed, but I very much doubt this is the cause of the problem). The user can update or add as many rows as possible. When the user clicks the Save button it calls customerTableAdapter.Update(customerAppDS21.Customer);. All the events are definitely wired up correctly.
The problem is basically that the DataSet appears to get updated but the database itself is not updated. I can close the program and reload it straight away and the data is there. However if I make any changes to the code and then recompile and load the program all the data is gone. This is what makes me think the DataSet is being updated but not the database.
Does anyone have any idea how to solve the problem? I have tried adding the .acceptChanges(); line both before and after the .Update(bla); line, with no success. I have also tried calling customerBindingSource.EndEdit(); and bindingSource1.EndEdit(); before the .Update(bla); line.
Any help with this would be greatly appreciated. I have had this problem for 2 days now and tried alsorts of tutorials to get a hint on where I am going wrong.
Regards,
Richard
Update: Code below...
void button1_Click(object sender, EventArgs e)
{
Validate();
customerBindingSource.EndEdit();
customerTableAdapter.Update(customerAppDS21.Customer);
}
void grdCustomers_RowLeave(object sender, DataGridViewCellEventArgs e)
{
DataGridViewRow gvr = grdCustomers.Rows[e.RowIndex];
if (gvr.Cells[0].Value == null)
{
String g = Guid.NewGuid().ToString();
gvr.Cells[0].Value = g;
}
else
{
String currID = gvr.Cells[0].Value.ToString();
if (currID.Equals(""))
{
String g = Guid.NewGuid().ToString();
gvr.Cells[0].Value = g;
}
}
}
void grdCustomers_UserAddedRow(object sender, DataGridViewRowEventArgs e)
{
DataGridViewRow gvr = grdCustomers.Rows[grdCustomers.SelectedCells[0].RowIndex];
if (gvr.Cells[0].Value == null)
{
String g = Guid.NewGuid().ToString();
gvr.Cells[0].Value = g;
}
else
{
String currID = gvr.Cells[0].Value.ToString();
if (currID.Equals(""))
{
String g = Guid.NewGuid().ToString();
gvr.Cells[0].Value = g;
}
}
}
Designer code (sorry its so long - I dont want to risk missing out anything vital):
namespace CustomerApp
{
partial class CustomerAppForm
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle();
System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle2 = new System.Windows.Forms.DataGridViewCellStyle();
System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle3 = new System.Windows.Forms.DataGridViewCellStyle();
this.tabControl1 = new System.Windows.Forms.TabControl();
this.tabPage1 = new System.Windows.Forms.TabPage();
this.button1 = new System.Windows.Forms.Button();
this.grdCustomers = new System.Windows.Forms.DataGridView();
this.agentBindingSource = new System.Windows.Forms.BindingSource(this.components);
this.bindingSource1 = new System.Windows.Forms.BindingSource(this.components);
this.customerAppDS21 = new CustomerApp.CustomerAppDS2();
this.tabPage2 = new System.Windows.Forms.TabPage();
this.customerTableAdapter = new CustomerApp.CustomerAppDS2TableAdapters.CustomerTableAdapter();
this.agentTableAdapter = new CustomerApp.CustomerAppDS2TableAdapters.AgentTableAdapter();
this.customerBindingSource = new System.Windows.Forms.BindingSource(this.components);
this.idDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.companynameDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.contactforenameDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.contactsurnameDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.companyaddress1DataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.companyaddress2DataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.companytownDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.companycountyDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.companypostcodeDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.contacttelephoneDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.contactfaxDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.contactemailDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.agentIDDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewComboBoxColumn();
this.contactfullnameDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.tabControl1.SuspendLayout();
this.tabPage1.SuspendLayout();
((System.ComponentModel.ISupportInitialize)(this.grdCustomers)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.agentBindingSource)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.bindingSource1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.customerAppDS21)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.customerBindingSource)).BeginInit();
this.SuspendLayout();
//
// tabControl1
//
this.tabControl1.Controls.Add(this.tabPage1);
this.tabControl1.Controls.Add(this.tabPage2);
this.tabControl1.Location = new System.Drawing.Point(12, 12);
this.tabControl1.Name = "tabControl1";
this.tabControl1.SelectedIndex = 0;
this.tabControl1.Size = new System.Drawing.Size(1000, 640);
this.tabControl1.TabIndex = 0;
//
// tabPage1
//
this.tabPage1.Controls.Add(this.button1);
this.tabPage1.Controls.Add(this.grdCustomers);
this.tabPage1.Location = new System.Drawing.Point(4, 22);
this.tabPage1.Name = "tabPage1";
this.tabPage1.Padding = new System.Windows.Forms.Padding(3);
this.tabPage1.Size = new System.Drawing.Size(992, 614);
this.tabPage1.TabIndex = 0;
this.tabPage1.Text = "Customers";
this.tabPage1.UseVisualStyleBackColor = true;
//
// button1
//
this.button1.Location = new System.Drawing.Point(882, 462);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 1;
this.button1.Text = "Save";
this.button1.UseVisualStyleBackColor = true;
//
// grdCustomers
//
this.grdCustomers.AllowUserToOrderColumns = true;
this.grdCustomers.AutoGenerateColumns = false;
dataGridViewCellStyle1.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
dataGridViewCellStyle1.BackColor = System.Drawing.SystemColors.Control;
dataGridViewCellStyle1.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
dataGridViewCellStyle1.ForeColor = System.Drawing.SystemColors.WindowText;
dataGridViewCellStyle1.SelectionBackColor = System.Drawing.SystemColors.Highlight;
dataGridViewCellStyle1.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
dataGridViewCellStyle1.WrapMode = System.Windows.Forms.DataGridViewTriState.True;
this.grdCustomers.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle1;
this.grdCustomers.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.grdCustomers.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
this.idDataGridViewTextBoxColumn,
this.companynameDataGridViewTextBoxColumn,
this.contactforenameDataGridViewTextBoxColumn,
this.contactsurnameDataGridViewTextBoxColumn,
this.companyaddress1DataGridViewTextBoxColumn,
this.companyaddress2DataGridViewTextBoxColumn,
this.companytownDataGridViewTextBoxColumn,
this.companycountyDataGridViewTextBoxColumn,
this.companypostcodeDataGridViewTextBoxColumn,
this.contacttelephoneDataGridViewTextBoxColumn,
this.contactfaxDataGridViewTextBoxColumn,
this.contactemailDataGridViewTextBoxColumn,
this.agentIDDataGridViewTextBoxColumn,
this.contactfullnameDataGridViewTextBoxColumn});
this.grdCustomers.DataSource = this.customerBindingSource;
dataGridViewCellStyle2.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
dataGridViewCellStyle2.BackColor = System.Drawing.SystemColors.Window;
dataGridViewCellStyle2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
dataGridViewCellStyle2.ForeColor = System.Drawing.SystemColors.ControlText;
dataGridViewCellStyle2.SelectionBackColor = System.Drawing.SystemColors.Highlight;
dataGridViewCellStyle2.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
dataGridViewCellStyle2.WrapMode = System.Windows.Forms.DataGridViewTriState.False;
this.grdCustomers.DefaultCellStyle = dataGridViewCellStyle2;
this.grdCustomers.Location = new System.Drawing.Point(3, 3);
this.grdCustomers.Name = "grdCustomers";
dataGridViewCellStyle3.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
dataGridViewCellStyle3.BackColor = System.Drawing.SystemColors.Control;
dataGridViewCellStyle3.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
dataGridViewCellStyle3.ForeColor = System.Drawing.SystemColors.WindowText;
dataGridViewCellStyle3.SelectionBackColor = System.Drawing.SystemColors.Highlight;
dataGridViewCellStyle3.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
dataGridViewCellStyle3.WrapMode = System.Windows.Forms.DataGridViewTriState.True;
this.grdCustomers.RowHeadersDefaultCellStyle = dataGridViewCellStyle3;
this.grdCustomers.RowTemplate.DefaultCellStyle.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(192)))));
this.grdCustomers.RowTemplate.DefaultCellStyle.Padding = new System.Windows.Forms.Padding(2);
this.grdCustomers.RowTemplate.DefaultCellStyle.SelectionBackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(224)))), ((int)(((byte)(192)))));
this.grdCustomers.Size = new System.Drawing.Size(983, 605);
this.grdCustomers.TabIndex = 0;
//
// agentBindingSource
//
this.agentBindingSource.DataMember = "Agent";
this.agentBindingSource.DataSource = this.bindingSource1;
//
// bindingSource1
//
this.bindingSource1.AllowNew = true;
this.bindingSource1.DataSource = this.customerAppDS21;
this.bindingSource1.Position = 0;
//
// customerAppDS21
//
this.customerAppDS21.DataSetName = "CustomerAppDS2";
this.customerAppDS21.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
//
// tabPage2
//
this.tabPage2.Location = new System.Drawing.Point(4, 22);
this.tabPage2.Name = "tabPage2";
this.tabPage2.Padding = new System.Windows.Forms.Padding(3);
this.tabPage2.Size = new System.Drawing.Size(992, 614);
this.tabPage2.TabIndex = 1;
this.tabPage2.Text = "Agents";
this.tabPage2.UseVisualStyleBackColor = true;
//
// customerTableAdapter
//
this.customerTableAdapter.ClearBeforeFill = true;
//
// agentTableAdapter
//
this.agentTableAdapter.ClearBeforeFill = true;
//
// customerBindingSource
//
this.customerBindingSource.DataMember = "Customer";
this.customerBindingSource.DataSource = this.bindingSource1;
//
// idDataGridViewTextBoxColumn
//
this.idDataGridViewTextBoxColumn.DataPropertyName = "id";
this.idDataGridViewTextBoxColumn.HeaderText = "id";
this.idDataGridViewTextBoxColumn.Name = "idDataGridViewTextBoxColumn";
this.idDataGridViewTextBoxColumn.ReadOnly = true;
//
// companynameDataGridViewTextBoxColumn
//
this.companynameDataGridViewTextBoxColumn.DataPropertyName = "company_name";
this.companynameDataGridViewTextBoxColumn.HeaderText = "Company Name";
this.companynameDataGridViewTextBoxColumn.Name = "companynameDataGridViewTextBoxColumn";
this.companynameDataGridViewTextBoxColumn.ToolTipText = "The name of the company";
//
// contactforenameDataGridViewTextBoxColumn
//
this.contactforenameDataGridViewTextBoxColumn.DataPropertyName = "contact_forename";
this.contactforenameDataGridViewTextBoxColumn.HeaderText = "Contact Forename";
this.contactforenameDataGridViewTextBoxColumn.Name = "contactforenameDataGridViewTextBoxColumn";
//
// contactsurnameDataGridViewTextBoxColumn
//
this.contactsurnameDataGridViewTextBoxColumn.DataPropertyName = "contact_surname";
this.contactsurnameDataGridViewTextBoxColumn.HeaderText = "Contact Surname";
this.contactsurnameDataGridViewTextBoxColumn.Name = "contactsurnameDataGridViewTextBoxColumn";
//
// companyaddress1DataGridViewTextBoxColumn
//
this.companyaddress1DataGridViewTextBoxColumn.DataPropertyName = "company_address1";
this.companyaddress1DataGridViewTextBoxColumn.HeaderText = "Address 1";
this.companyaddress1DataGridViewTextBoxColumn.Name = "companyaddress1DataGridViewTextBoxColumn";
//
// companyaddress2DataGridViewTextBoxColumn
//
this.companyaddress2DataGridViewTextBoxColumn.DataPropertyName = "company_address2";
this.companyaddress2DataGridViewTextBoxColumn.HeaderText = "Address 2";
this.companyaddress2DataGridViewTextBoxColumn.Name = "companyaddress2DataGridViewTextBoxColumn";
//
// companytownDataGridViewTextBoxColumn
//
this.companytownDataGridViewTextBoxColumn.DataPropertyName = "company_town";
this.companytownDataGridViewTextBoxColumn.HeaderText = "Town";
this.companytownDataGridViewTextBoxColumn.Name = "companytownDataGridViewTextBoxColumn";
//
// companycountyDataGridViewTextBoxColumn
//
this.companycountyDataGridViewTextBoxColumn.DataPropertyName = "company_county";
this.companycountyDataGridViewTextBoxColumn.HeaderText = "County";
this.companycountyDataGridViewTextBoxColumn.Name = "companycountyDataGridViewTextBoxColumn";
//
// companypostcodeDataGridViewTextBoxColumn
//
this.companypostcodeDataGridViewTextBoxColumn.DataPropertyName = "company_postcode";
this.companypostcodeDataGridViewTextBoxColumn.HeaderText = "Postcode";
this.companypostcodeDataGridViewTextBoxColumn.Name = "companypostcodeDataGridViewTextBoxColumn";
//
// contacttelephoneDataGridViewTextBoxColumn
//
this.contacttelephoneDataGridViewTextBoxColumn.DataPropertyName = "contact_telephone";
this.contacttelephoneDataGridViewTextBoxColumn.HeaderText = "Telephone";
this.contacttelephoneDataGridViewTextBoxColumn.Name = "contacttelephoneDataGridViewTextBoxColumn";
//
// contactfaxDataGridViewTextBoxColumn
//
this.contactfaxDataGridViewTextBoxColumn.DataPropertyName = "contact_fax";
this.contactfaxDataGridViewTextBoxColumn.HeaderText = "Fax";
this.contactfaxDataGridViewTextBoxColumn.Name = "contactfaxDataGridViewTextBoxColumn";
//
// contactemailDataGridViewTextBoxColumn
//
this.contactemailDataGridViewTextBoxColumn.DataPropertyName = "contact_email";
this.contactemailDataGridViewTextBoxColumn.HeaderText = "Email";
this.contactemailDataGridViewTextBoxColumn.Name = "contactemailDataGridViewTextBoxColumn";
//
// agentIDDataGridViewTextBoxColumn
//
this.agentIDDataGridViewTextBoxColumn.DataPropertyName = "agentID";
this.agentIDDataGridViewTextBoxColumn.DataSource = this.agentBindingSource;
this.agentIDDataGridViewTextBoxColumn.DisplayMember = "contact_fullname";
this.agentIDDataGridViewTextBoxColumn.HeaderText = "agentID";
this.agentIDDataGridViewTextBoxColumn.Name = "agentIDDataGridViewTextBoxColumn";
this.agentIDDataGridViewTextBoxColumn.Resizable = System.Windows.Forms.DataGridViewTriState.True;
this.agentIDDataGridViewTextBoxColumn.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
this.agentIDDataGridViewTextBoxColumn.ValueMember = "id";
//
// contactfullnameDataGridViewTextBoxColumn
//
this.contactfullnameDataGridViewTextBoxColumn.DataPropertyName = "contact_fullname";
this.contactfullnameDataGridViewTextBoxColumn.HeaderText = "contact_fullname";
this.contactfullnameDataGridViewTextBoxColumn.Name = "contactfullnameDataGridViewTextBoxColumn";
this.contactfullnameDataGridViewTextBoxColumn.ReadOnly = true;
this.contactfullnameDataGridViewTextBoxColumn.Visible = false;
//
// CustomerAppForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(1184, 662);
this.Controls.Add(this.tabControl1);
this.Location = new System.Drawing.Point(100, 100);
this.Name = "CustomerAppForm";
this.StartPosition = System.Windows.Forms.FormStartPosition.Manual;
this.Text = "Heritage Art Papers Ltd - Customer Application";
this.Load += new System.EventHandler(this.CustomerAppForm_Load);
this.tabControl1.ResumeLayout(false);
this.tabPage1.ResumeLayout(false);
((System.ComponentModel.ISupportInitialize)(this.grdCustomers)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.agentBindingSource)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.bindingSource1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.customerAppDS21)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.customerBindingSource)).EndInit();
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.TabControl tabControl1;
private System.Windows.Forms.TabPage tabPage1;
private System.Windows.Forms.TabPage tabPage2;
private System.Windows.Forms.BindingSource bindingSource1;
private CustomerAppDS2 customerAppDS21;
private System.Windows.Forms.BindingSource agentBindingSource;
private CustomerAppDS2TableAdapters.CustomerTableAdapter customerTableAdapter;
private CustomerAppDS2TableAdapters.AgentTableAdapter agentTableAdapter;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.BindingSource customerBindingSource;
private System.Windows.Forms.DataGridViewTextBoxColumn idDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn companynameDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn contactforenameDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn contactsurnameDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn companyaddress1DataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn companyaddress2DataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn companytownDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn companycountyDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn companypostcodeDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn contacttelephoneDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn contactfaxDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn contactemailDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewComboBoxColumn agentIDDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridViewTextBoxColumn contactfullnameDataGridViewTextBoxColumn;
private System.Windows.Forms.DataGridView grdCustomers;
}
}
Answer
I guess this might be your problem. Read these two articles very carefully. The first one contains a thread where in the second link (I gave below) is suggested.
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic58794.aspx
and
http://msdn.microsoft.com/en-us/library/ms246989.aspx
In the first link, read the answer of: Kerry Moorman.