By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 2,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

update oledb database from text field

P: n/a
I need the code to update the database when Save is clicked and a text field
has changed. This should be very easy since I used Microsoft's wizards for
the OleDBAdapter and OleDBConnection, and DataSet; and all I'm doing is
showing one record in text fields, allowing the user to modify the text
fields, and then updating the database again when the user clicks the Save
button. The fields already show the correct data record since I have the
DataBinding text property filled in for the text fields. I need the code to
put in the save button click event.

Here are SOME of the portions I already have, most of it from the wizards...
....
using System.Data;
using System.Data.OleDb;
....
namespace EsperanzaThreads
public class frmComapnyEdit : System.Windows.Forms.Form
....
private System.Windows.Forms.TextBox txtCompany;
private System.Windows.Forms.TextBox txtCompanyID;
private System.Data.OleDb.OleDBCommand oleDbUpdateCommand1;
private EsperanzaThreads.dsCompanyEdit dsCompanyEdit1;
private string strCommandText;
private string strCurrent;
....
public frmComanyEdit(string idname)
{strCurrent = idname;
InitializeComponent();
....
private void InitializeComponent()
{
this.txtCompany = new System.Windows.Forms.TextBox();
this.txtCompanyID = new System.Windows.Forms.TextBox();
this.lblActive = new System.Windows.Forms.Label();
this.lblCompany = new System.Windows.Forms.Label();
this.lblCompanyID = new System.Windows.Forms.Label();
this.oleDbDataAdapterCompanyEdit = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.dsCompanyEdit1 = new EsperanzaThreads.dsCompanyEdit();
....
this.txtCompanyID.DataBindings.Add(new System.Windows.Forms.Binding("Text",
this.dsCompanyEdit1, "tblCompany.CompanyId"));
this.oleDbDataAdapterCompanyEdit.TableMappings.Add Range(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "tblCompany", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("CompanyId", "CompanyId"),
new System.Data.Common.DataColumnMapping("Company", "Company")})});
this.oleDbDataAdapterCompanyEdit.UpdateCommand = this.oleDbUpdateCommand1;

this.oleDbUpdateCommand1.CommandText = "UPDATE tblCompany SET CompanyId = ?,
Company = ?, Active = ? WHERE (CompanyId = ?" +") AND (Active = ?) AND
(Company = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyId",
System.Data.OleDb.OleDbType.VarWChar, 1, "CompanyId"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Company",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Active",
System.Data.OleDb.OleDbType.Boolean, 2, "Active"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company Id",
System.Data.OleDb.OleDbType.VarWChar, 1,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyId", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Active" ,
System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Active",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company ",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Company", System.Data.DataRowVersion.Original, null));
....
this.dsCompanyEdit1.DataSetName = "dsCompanyEdit";
....
private void frmCompanyEdit_Load(object sender, System.EventArgs e)
{
strCommandText = "SELECT CompanyID, Company, Active FROM tblCompany WHERE
CompanyID = '" + strCurrent + "'";
this.oleDbSelectCommand1.CommandText = strCommandText;
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
}

private void btnSave_Click(object sender, System.EventArgs e) {
// WHAT SHOULD GO IN HERE?

The following is what I had in the btnSave_Click when I wanted to add a new
record, but I think there is probably an even easier way using more of
Microsoft's prewritten code and I don't know how to do it. But what I really
care about is figuring out how to update the database when a record is
modified. I played with this code and can't figure out how to convert it to
do updates.

//Code I used for adding a new record:
DataTable dt = dsCompanyAdd1.Tables["tblCompany"];
DataRow dr = dt.NewRow();
drNew["CompanyID"] = this.txtCompanyID.Text;
drNew["Company"] = this.txtCompany.Text;
drNew["Active"] = this.cmbActive.Text;
dt.Rows.Add(drNew);
oleDbDataAdapterCompanyAdd.Update(dsCompanyAdd1,"t blCompany");
dsCompanyAdd1.Clear();
oleDbDataAdapterCompanyAdd.Fill(dsCompanyAdd1);
this.Close();

I found knowlegebase and help articles but I am confused since the
parameters, UpdateCommand, data adapter, and data connection are already
written by Microsoft's wizard and I couldn't find any articles that showed me
how to use the prewritten code.

The following code is the best example I could find on how to do what I
want, but I still can't figure out which portions I need, given the Microsoft
prewritten Wizard code. Could you show me the correct portions to use with
the prewritten wizard code, or better yet, what the code should exactly be
for the Save_Click event?

Sample from knowledgebase:
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5,
"CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName" , OleDbType.VarChar, 40,
"CompanyName");

OleDbParameter myParm =
custDA.UpdateCommand.Parameters.Add("@OldCustomerI D", OleDbType.Char, 5,
"CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

public static OleDbDataAdapter CreateCustomerAdapter(OleDbConnection conn)
{
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand cmd;
OleDbParameter parm;

// Create the UpdateCommand.

cmd = new OleDbCommand("UPDATE Customers SET CustomerID = @CustomerID,
CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", conn);

cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID");
cmd.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName");

parm = cmd.Parameters.Add("@oldCustomerID", OleDbType.Char, 5,
"CustomerID");
parm.SourceVersion = DataRowVersion.Original;

da.UpdateCommand = cmd;

return da;
}

Thanks,
Pam
Nov 16 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Hi Pam,

Thank you for your posting. Regarding on the issue, I am
finding proper resource to assist you and we will update as soon as posible.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security(This posting is provided "AS IS",
with no warranties, and confers no rights.)

Nov 16 '05 #2

P: n/a
Hi Pam,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know the best way to put
the new data to the database using DataAdapter. If there is any
misunderstanding, please feel free to let me know.

Based on the code you have provided, I think your code is fine. The only
thing that we could improve, is to add data binding to the form, which
makes the newly added row goes to the DataTable directly. And data in each
TextBox will be filled to the newly added row automatically. Here is an
article about data binding in windows forms.

http://msdn.microsoft.com/library/de...us/dndotnet/ht
ml/databindingadonet.asp

Since you're using the DataAdapter wizard to generate the DataAdapter and
DataSet, we needn't add the parameters to the OleDbCommand ourselves.
Everything has been done by the wizard. It has generated code for you. If
you expand the region of Windows Form Designer generated code, you will see
the OleDbCommand with parameters created.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #3

P: n/a
No, that is not what I need. As I indicated in my post, what I really need
is the UPDATE code, not add code. As I indicated in my initial post, I have
the add capabilities working, and it is nice to know how to improve it, but
what I really need is the update code, to work with the code that the Wizards
wrote, for the save button.

As I indicated in my initial post, I do have the Data Binding text fields
already set properly and it is working properly for my add form.

Second, as far as I can tell, the reference you gave me only shows how to
bind to text fields to DISPLAY the fields. It does not say how to add,
update, or delete data in text fields.

PLEASE, I really need you to help me with this since it is the last step in
handing the finished project over to my client. I've been waiting since
Sunday to finish the project, all based on getting the Save button on the
Update form to work.

Thanks,
Pam
"Kevin Yu [MSFT]" wrote:
Hi Pam,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know the best way to put
the new data to the database using DataAdapter. If there is any
misunderstanding, please feel free to let me know.

Based on the code you have provided, I think your code is fine. The only
thing that we could improve, is to add data binding to the form, which
makes the newly added row goes to the DataTable directly. And data in each
TextBox will be filled to the newly added row automatically. Here is an
article about data binding in windows forms.

http://msdn.microsoft.com/library/de...us/dndotnet/ht
ml/databindingadonet.asp

Since you're using the DataAdapter wizard to generate the DataAdapter and
DataSet, we needn't add the parameters to the OleDbCommand ourselves.
Everything has been done by the wizard. It has generated code for you. If
you expand the region of Windows Form Designer generated code, you will see
the OleDbCommand with parameters created.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #4

P: n/a
Hi Pam,

Sorry for the misunderstanding. Since there are many ways which enable us
to update data from the DataSet to the database, I would like to confirm
more information on how data was binded to the form controls.

Is the data binding accomplished by setting the DataBinding options in the
Form Designer? It make data displayed in those TextBoxes as soon as data
are filled to the DataSet.

If the above is true, it will be very simple for us to update the database.
First we need to get a CurrencyManager from the binding context and call
its EndCurrentEdit method to save the current edit data in the DataSet. To
get the CurrencyManager object, please check the following link:

http://msdn.microsoft.com/library/de...us/cpref/html/
frlrfsystemwindowsformscurrencymanagerclasstopic.a sp

Then, we simply call
oleDbDataAdapterCompanyAdd.Update(dsCompanyAdd1,"t blCompany"); This makes
full use of the designer generated Update command and update alll the
inserted, modified and deleted rows.

If the above question is not true, we have to do the following for updating:
1. Search in the DataTable and get the reference to the row currently we
are modifying.
2. Assign all value in the rows with new data like the following.

drModified["CompanyID"] = this.txtCompanyID.Text;
drModified["Company"] = this.txtCompany.Text;
drModified["Active"] = this.cmbActive.Text;

3. Call oleDbDataAdapterCompanyAdd.Update(dsCompanyAdd1,"t blCompany"); to
update the rows which have been changed to the database.

However, for more advice on this, I have to get more detailed information
on the architecture of your app.

Here are some information on DataBinding and some good practices on windows
form data architectures.

http://msdn.microsoft.com/library/de...us/vbcon/html/
vboriWindowsFormsDataArchitecture.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #5

P: n/a
We are very close. The following code executes without errors, but actually
does not update the database. I think my currency statement is either wrong
or in the wrong place?... it's in the form_load event. If that's not the
problem, then what did I do wrong? Look at the btnSave_Click and the form
load event, the rest of my code should be correct.

Databindings for the fields, in the Text property:
for txtCompanyid the value is dsCompanyEdit1 - tblCompany.CompanyId
for txtCompany the value is dsCompanyEdit1 - tblCompany.Company
for cmbActive the value is dsCompanyEdit1 - tblCompany.Active

for the Item Collection property of cmbActive I have the values True and
False, which are the only allowed values for this field.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace EsperanzaThreads
{
/// <summary>
/// Summary description for frmCompanyEdit.
/// </summary>
public class frmCompanyEdit : System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
private string strCommandText;
private System.Windows.Forms.ComboBox cmbActive;
private System.Windows.Forms.Button btnCancel;
private System.Windows.Forms.Button btnSave;
private System.Windows.Forms.TextBox txtCompany;
private System.Windows.Forms.TextBox txtCompanyID;
private System.Windows.Forms.Label lblActive;
private System.Windows.Forms.Label lblCompany;
private System.Windows.Forms.Label lblCompanyID;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapterCompanyEdit;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private EsperanzaThreads.dsCompanyEdit dsCompanyEdit1;
private string strCurrent;

public frmCompanyEdit()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

//
// TODO: Add any constructor code after InitializeComponent call
//
}

public frmCompanyEdit(string idname)
{
//
// Required for Windows Form Designer support
//
strCurrent = idname;
InitializeComponent();
this.Text = "Edit Company";
//
// TODO: Add any constructor code after InitializeComponent call
//
}

/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(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.cmbActive = new System.Windows.Forms.ComboBox();
this.dsCompanyEdit1 = new EsperanzaThreads.dsCompanyEdit();
this.btnCancel = new System.Windows.Forms.Button();
this.btnSave = new System.Windows.Forms.Button();
this.txtCompany = new System.Windows.Forms.TextBox();
this.txtCompanyID = new System.Windows.Forms.TextBox();
this.lblActive = new System.Windows.Forms.Label();
this.lblCompany = new System.Windows.Forms.Label();
this.lblCompanyID = new System.Windows.Forms.Label();
this.oleDbDataAdapterCompanyEdit = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand()
((System.ComponentModel.ISupportInitialize)(this.d sCompanyEdit1)).BeginInit();
this.SuspendLayout();
//
// cmbActive
//
this.cmbActive.DataSource = this.dsCompanyEdit1.tblCompany;
this.cmbActive.DisplayMember = "Active";
this.cmbActive.DropDownStyle =
System.Windows.Forms.ComboBoxStyle.DropDownList;
this.cmbActive.Location = new System.Drawing.Point(80, 56);
this.cmbActive.MaxDropDownItems = 2;
this.cmbActive.Name = "cmbActive";
this.cmbActive.Size = new System.Drawing.Size(88, 21);
this.cmbActive.TabIndex = 16;
//
// dsCompanyEdit1
//
this.dsCompanyEdit1.DataSetName = "dsCompanyEdit";
this.dsCompanyEdit1.Locale = new System.Globalization.CultureInfo("en-US");
//
// btnCancel
//
this.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.btnCancel.Location = new System.Drawing.Point(280, 88);
this.btnCancel.Name = "btnCancel";
this.btnCancel.TabIndex = 18;
this.btnCancel.Text = "Cancel";
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// btnSave
//
this.btnSave.Location = new System.Drawing.Point(184, 88);
this.btnSave.Name = "btnSave";
this.btnSave.TabIndex = 17;
this.btnSave.Text = "Save";
this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
//
// txtCompany
//
this.txtCompany.DataBindings.Add(new System.Windows.Forms.Binding("Text",
this.dsCompanyEdit1, "tblCompany.Company"));
this.txtCompany.Location = new System.Drawing.Point(80, 32);
this.txtCompany.Name = "txtCompany";
this.txtCompany.Size = new System.Drawing.Size(272, 20);
this.txtCompany.TabIndex = 15;
this.txtCompany.Text = "";
//
// txtCompanyID
//
this.txtCompanyID.DataBindings.Add(new System.Windows.Forms.Binding("Text",
this.dsCompanyEdit1, "tblCompany.CompanyId"));
this.txtCompanyID.Location = new System.Drawing.Point(80, 8);
this.txtCompanyID.MaxLength = 1;
this.txtCompanyID.Name = "txtCompanyID";
this.txtCompanyID.Size = new System.Drawing.Size(24, 20);
this.txtCompanyID.TabIndex = 14;
this.txtCompanyID.Text = "";
//
// lblActive
//
this.lblActive.Location = new System.Drawing.Point(8, 56);
this.lblActive.Name = "lblActive";
this.lblActive.Size = new System.Drawing.Size(72, 23);
this.lblActive.TabIndex = 21;
this.lblActive.Text = "Active";
//
// lblCompany
//
this.lblCompany.Location = new System.Drawing.Point(8, 32);
this.lblCompany.Name = "lblCompany";
this.lblCompany.Size = new System.Drawing.Size(72, 23);
this.lblCompany.TabIndex = 20;
this.lblCompany.Text = "Company";
//
// lblCompanyID
//
this.lblCompanyID.Location = new System.Drawing.Point(8, 8);
this.lblCompanyID.Name = "lblCompanyID";
this.lblCompanyID.Size = new System.Drawing.Size(72, 23);
this.lblCompanyID.TabIndex = 19;
this.lblCompanyID.Text = "Company ID";
//
// oleDbDataAdapterCompanyEdit
//
this.oleDbDataAdapterCompanyEdit.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapterCompanyEdit.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapterCompanyEdit.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapterCompanyEdit.TableMappings.Add Range(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "tblCompany", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("CompanyId", "CompanyId"),
new System.Data.Common.DataColumnMapping("Company", "Company"),
new System.Data.Common.DataColumnMapping("Active", "Active")})});
this.oleDbDataAdapterCompanyEdit.UpdateCommand = this.oleDbUpdateCommand1;
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = "DELETE FROM tblCompany WHERE
(CompanyId = ?) AND (Active = ?) AND (Company = ?)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company Id",
System.Data.OleDb.OleDbType.VarWChar, 1,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyId", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Active" ,
System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Active",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company ",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Company", System.Data.DataRowVersion.Original, null));
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data
Source=""<path omitted>EspThr.mdb"";Jet OLEDB:Engine
Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet
OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share
Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair=False;User ID=<omitted>;Jet OLEDB:Global Bulk
Transactions=1";
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = "INSERT INTO tblCompany(CompanyId,
Company, Active) VALUES (?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyId",
System.Data.OleDb.OleDbType.VarWChar, 1, "CompanyId"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Company",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Active",
System.Data.OleDb.OleDbType.Boolean, 2, "Active"));
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT CompanyId, Company, Active
FROM tblCompany";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = "UPDATE tblCompany SET CompanyId =
?, Company = ?, Active = ? WHERE (CompanyId = ?" +
") AND (Active = ?) AND (Company = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyId",
System.Data.OleDb.OleDbType.VarWChar, 1, "CompanyId"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Company",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Active",
System.Data.OleDb.OleDbType.Boolean, 2, "Active"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company Id",
System.Data.OleDb.OleDbType.VarWChar, 1,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyId", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Active" ,
System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Active",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company ",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Company", System.Data.DataRowVersion.Original, null));
//
// frmCompanyEdit
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(360, 118);
this.Controls.Add(this.cmbActive);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.btnSave);
this.Controls.Add(this.txtCompany);
this.Controls.Add(this.txtCompanyID);
this.Controls.Add(this.lblActive);
this.Controls.Add(this.lblCompany);
this.Controls.Add(this.lblCompanyID);
this.Name = "frmCompanyEdit";
this.Text = "frmCompanyEdit";
this.Load += new System.EventHandler(this.frmCompanyEdit_Load);
((System.ComponentModel.ISupportInitialize)(this.d sCompanyEdit1)).EndInit();
this.ResumeLayout(false);

}
#endregion

private void frmCompanyEdit_Load(object sender, System.EventArgs e)
{
strCommandText = "SELECT CompanyID, Company, Active FROM tblCompany WHERE
CompanyID = '" + strCurrent + "'";
this.oleDbSelectCommand1.CommandText = strCommandText;
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
CurrencyManager cm = (CurrencyManager)this.BindingContext[dsCompanyEdit1,
"tblCompany"];
}

private void btnSave_Click(object sender, System.EventArgs e)
{
if ((this.txtCompanyID.Text == "") || (this.txtCompany.Text == "") ||
(this.cmbActive.Text == ""))
{
MessageBox.Show ("Information may not be blank", "Problem Saving",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1);
this.txtCompanyID.Focus();
}
else
{
try
{
oleDbDataAdapterCompanyEdit.Update(dsCompanyEdit1, "tblCompany");
MessageBox.Show("Database Updated!");
dsCompanyEdit1.Clear();
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
this.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
this.txtCompanyID.Focus();
}
}

}

private void btnCancel_Click(object sender, System.EventArgs e)
{
this.Close();
}

}
}

Nov 16 '05 #6

P: n/a
Hi Pam,

Thanks for your code. As I mentioned in my last post, we have to get the
CurrencyManager from the binding context and call EndCurrentEdit method
before updating data to the database. This makes sure that the changes to
the DataSet has been saved. Here I made some changes to the Save method.

private void btnSave_Click(object sender, System.EventArgs e)
{
if ((this.txtCompanyID.Text == "") || (this.txtCompany.Text == "") ||
(this.cmbActive.Text == ""))
{
MessageBox.Show ("Information may not be blank", "Problem Saving",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1);
this.txtCompanyID.Focus();
}
else
{
try
{
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dsCompanyEdit1, "tblCompany"];
cm.EndCurrentEdit(); //Get currencymanager and call EndCurrentEdit here
to make sure that changes have been saved to DataSet
oleDbDataAdapterCompanyEdit.Update(dsCompanyEdit1, "tblCompany");
MessageBox.Show("Database Updated!");
dsCompanyEdit1.Clear();
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
this.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
this.txtCompanyID.Focus();
}
}
}
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #7

P: n/a
Kevin,

Still almost there, but doing better than before...

Yes it now updates (mostly), amazing what cm.EndCurrentEdit(); will do!
Thank you!!!! (I read a lot about cm and tried using it earlier, but my code
didn't work since I never knew about EndCurrentEdit(). Thanks.)

However, cmbActive, when I click on it and change it from True to False does
not update.

I am confused about the following properties:

Data Bindings
Selected Value
Selected Item
Tag
Text
and
Display Source
Display Member
and
Value Member
and
Items Collection...

I am never quite sure which I should use and why,
and notice that if I use Display Source/Display Member then I shouldn't use
DataBindings Text.
and also notice that if I use Items Collection then I shouldn't fill in
Display Source/Display Member.

What I want to do is limit cmbActive to only accept True/False so I used
Collection and put in True and False. I wanted it to update the database so
I used DataBindings Text property value of dsCompanyEdit1 -
tblCompany.Active. It does allow me to choose only True/False, but it is not
updating Active in the database even though it is updating the text fields
properly (just not the combo box) and isn't giving me an error.

So first and most importantly I need to know what I did wrong and how to fix
it so I can finish the project.

And then second, I want to understand which of the above properties I use
for which controls and why. So maybe you could refer me to additional
articles? or explain a little more about what is happening? If this is too
much to ask or takes you a while, then just give me the immediate answer I
need to finish the project.

If you would take a little more of your extremely valuable time to help me
answer this last problem, then I will put up my final code in my next reply
so it will help others.

Thanks,
Pam
"Kevin Yu [MSFT]" wrote:
Hi Pam,

Thanks for your code. As I mentioned in my last post, we have to get the
CurrencyManager from the binding context and call EndCurrentEdit method
before updating data to the database. This makes sure that the changes to
the DataSet has been saved. Here I made some changes to the Save method.

private void btnSave_Click(object sender, System.EventArgs e)
{
if ((this.txtCompanyID.Text == "") || (this.txtCompany.Text == "") ||
(this.cmbActive.Text == ""))
{
MessageBox.Show ("Information may not be blank", "Problem Saving",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1);
this.txtCompanyID.Focus();
}
else
{
try
{
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dsCompanyEdit1, "tblCompany"];
cm.EndCurrentEdit(); //Get currencymanager and call EndCurrentEdit here
to make sure that changes have been saved to DataSet
oleDbDataAdapterCompanyEdit.Update(dsCompanyEdit1, "tblCompany");
MessageBox.Show("Database Updated!");
dsCompanyEdit1.Clear();
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
this.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
this.txtCompanyID.Focus();
}
}
}
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #8

P: n/a
Hi Pam,

When using DataBinding with a ComboBox, we need to do the following.

1. Set the ComboBox.DataSource to a certain DataTable.
2. Set the ComboBox.DisplayMember to one of the columns whose data will be
displayed in the dropdown list.
3. Set the ComboBox.ValueMember to another column in the table whose data
represents the value of this row. Usually a primary key column.

Now, we have set the stuff in the dropdown list. And need to set which item
to display.

4. Bind the ComboBox.SelectedValue property to the column that contains the
value of which item to display.

In the current case, there are only two fixed items, True and False in the
ComboBox dropdown list. I don't think we need the ComboBox to achieve this,
because it might be too complicated. I suggest we simply use a CheckBox. If
it is checked, it's true. Then it will be a simply binding. But make sure
to set a default value for the boolean column which the CheckBox binds to,
because of the following KB article.

http://support.microsoft.com/default...b;en-us;326440

Here are some more information and examples of Windows Form data binding.
HTH.

http://msdn.microsoft.com/library/de...us/cpqstart/ht
ml/cpsmpnetsamples-windowsformsdatabinding.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #9

P: n/a
Thank you Kevin Yu!!! It totally works now.

I took out the combo box as you suggested and am now using a checkbox. I
did not use the DataBinding Text property but instead used the DataBindings
Checked property.

Here's the final code. Most of it was created using the oledb data adapter
wizard and dragging labels, text boxes, and a check boxes in to the form.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace EspThr
{
/// <summary>
/// Summary description for frmCompanyEdit.
/// </summary>
public class frmCompanyEdit : System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
private string strCommandText;
private System.Windows.Forms.Button btnCancel;
private System.Windows.Forms.Button btnSave;
private System.Windows.Forms.TextBox txtCompany;
private System.Windows.Forms.TextBox txtCompanyID;
private System.Windows.Forms.Label lblActive;
private System.Windows.Forms.Label lblCompany;
private System.Windows.Forms.Label lblCompanyID;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapterCompanyEdit;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private EspThr.dsCompanyEdit dsCompanyEdit1;
private System.Windows.Forms.CheckBox cbActive;
private string strCurrent;

public frmCompanyEdit()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

//
// TODO: Add any constructor code after InitializeComponent call
//
}

public frmCompanyEdit(string idname)
{
//
// Required for Windows Form Designer support
//
strCurrent = idname;
InitializeComponent();
this.Text = "Edit Company";
//
// TODO: Add any constructor code after InitializeComponent call
//
}

/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(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.dsCompanyEdit1 = new EspThr.dsCompanyEdit();
this.btnCancel = new System.Windows.Forms.Button();
this.btnSave = new System.Windows.Forms.Button();
this.txtCompany = new System.Windows.Forms.TextBox();
this.txtCompanyID = new System.Windows.Forms.TextBox();
this.lblActive = new System.Windows.Forms.Label();
this.lblCompany = new System.Windows.Forms.Label();
this.lblCompanyID = new System.Windows.Forms.Label();
this.oleDbDataAdapterCompanyEdit = new
System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.cbActive = new System.Windows.Forms.CheckBox();
((System.ComponentModel.ISupportInitialize)(this.d sCompanyEdit1)).BeginInit();
this.SuspendLayout();
//
// dsCompanyEdit1
//
this.dsCompanyEdit1.DataSetName = "dsCompanyEdit";
this.dsCompanyEdit1.Locale = new System.Globalization.CultureInfo("en-US");
//
// btnCancel
//
this.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.btnCancel.Location = new System.Drawing.Point(280, 88);
this.btnCancel.Name = "btnCancel";
this.btnCancel.TabIndex = 18;
this.btnCancel.Text = "Cancel";
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// btnSave
//
this.btnSave.Location = new System.Drawing.Point(184, 88);
this.btnSave.Name = "btnSave";
this.btnSave.TabIndex = 17;
this.btnSave.Text = "Save";
this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
//
// txtCompany
//
this.txtCompany.DataBindings.Add(new System.Windows.Forms.Binding("Text",
this.dsCompanyEdit1, "tblCompany.Company"));
this.txtCompany.Location = new System.Drawing.Point(80, 32);
this.txtCompany.Name = "txtCompany";
this.txtCompany.Size = new System.Drawing.Size(272, 20);
this.txtCompany.TabIndex = 15;
this.txtCompany.Text = "";
//
// txtCompanyID
//
this.txtCompanyID.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsCompanyEdit1,
"tblCompany.CompanyId"));
this.txtCompanyID.Location = new System.Drawing.Point(80, 8);
this.txtCompanyID.MaxLength = 1;
this.txtCompanyID.Name = "txtCompanyID";
this.txtCompanyID.Size = new System.Drawing.Size(24, 20);
this.txtCompanyID.TabIndex = 14;
this.txtCompanyID.Text = "";
//
// lblActive
//
this.lblActive.Location = new System.Drawing.Point(8, 56);
this.lblActive.Name = "lblActive";
this.lblActive.Size = new System.Drawing.Size(72, 23);
this.lblActive.TabIndex = 21;
this.lblActive.Text = "Active";
//
// lblCompany
//
this.lblCompany.Location = new System.Drawing.Point(8, 32);
this.lblCompany.Name = "lblCompany";
this.lblCompany.Size = new System.Drawing.Size(72, 23);
this.lblCompany.TabIndex = 20;
this.lblCompany.Text = "Company";
//
// lblCompanyID
//
this.lblCompanyID.Location = new System.Drawing.Point(8, 8);
this.lblCompanyID.Name = "lblCompanyID";
this.lblCompanyID.Size = new System.Drawing.Size(72, 23);
this.lblCompanyID.TabIndex = 19;
this.lblCompanyID.Text = "Company ID";
//
// oleDbDataAdapterCompanyEdit
//
this.oleDbDataAdapterCompanyEdit.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapterCompanyEdit.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapterCompanyEdit.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapterCompanyEdit.TableMappings.Add Range(new
System.Data.Common.DataTableMapping[] {
new
System.Data.Common.DataTableMapping("Table", "tblCompany", new
System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("CompanyId", "CompanyId"),
new
System.Data.Common.DataColumnMapping("Company", "Company"),
new
System.Data.Common.DataColumnMapping("Active", "Active")})});
this.oleDbDataAdapterCompanyEdit.UpdateCommand = this.oleDbUpdateCommand1;
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = "DELETE FROM tblCompany WHERE
(CompanyId = ?) AND (Active = ?) AND (Company = ?)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company Id",
System.Data.OleDb.OleDbType.VarWChar, 1,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyId", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Active" ,
System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Active",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company ",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Company", System.Data.DataRowVersion.Original, null));
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data
Source=""C:\EspThr.mdb"";Jet OLEDB:Engine
Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet
OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share
Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair=False;User ID=<omitted>;Jet OLEDB:Global Bulk
Transactions=1";
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = "INSERT INTO tblCompany(CompanyId,
Company, Active) VALUES (?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyId",
System.Data.OleDb.OleDbType.VarWChar, 1, "CompanyId"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Company",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Active",
System.Data.OleDb.OleDbType.Boolean, 2, "Active"));
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT CompanyId, Company, Active
FROM tblCompany";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = "UPDATE tblCompany SET CompanyId =
?, Company = ?, Active = ? WHERE (CompanyId = ?" +
") AND (Active = ?) AND (Company = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyId",
System.Data.OleDb.OleDbType.VarWChar, 1, "CompanyId"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Company",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Active",
System.Data.OleDb.OleDbType.Boolean, 2, "Active"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company Id",
System.Data.OleDb.OleDbType.VarWChar, 1,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyId", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Active" ,
System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Active",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Company ",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Company", System.Data.DataRowVersion.Original, null));
//
// cbActive
//
this.cbActive.DataBindings.Add(new
System.Windows.Forms.Binding("Checked", this.dsCompanyEdit1,
"tblCompany.Active"));
this.cbActive.Location = new System.Drawing.Point(80, 56);
this.cbActive.Name = "cbActive";
this.cbActive.TabIndex = 22;
//
// frmCompanyEdit
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(360, 118);
this.Controls.Add(this.cbActive);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.btnSave);
this.Controls.Add(this.txtCompany);
this.Controls.Add(this.txtCompanyID);
this.Controls.Add(this.lblActive);
this.Controls.Add(this.lblCompany);
this.Controls.Add(this.lblCompanyID);
this.Name = "frmCompanyEdit";
this.Text = "frmCompanyEdit";
this.Load += new System.EventHandler(this.frmCompanyEdit_Load);
((System.ComponentModel.ISupportInitialize)(this.d sCompanyEdit1)).EndInit();
this.ResumeLayout(false);

}
#endregion

private void frmCompanyEdit_Load(object sender, System.EventArgs e)
{
strCommandText = "SELECT CompanyID, Company, Active FROM tblCompany WHERE
CompanyID = '" + strCurrent + "'";
this.oleDbSelectCommand1.CommandText = strCommandText;
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
// dgCompanyEdit.DataSource = dsCompanyEdit1.Tables["tblCompany"];
// ((DataView)cm.List).AllowNew = false;
}

private void btnSave_Click(object sender, System.EventArgs e)
{
// DataRow cRow = catDS.Tables["Categories"].Rows[0];
// cRow["CategoryName"] = "New Category";
// catDA.Update(catDS);
if ((this.txtCompanyID.Text == "") || (this.txtCompany.Text == ""))
{
MessageBox.Show ("Information may not be blank", "Problem Saving",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1);
this.txtCompanyID.Focus();
}
else
{
try
{
CurrencyManager cm =
(CurrencyManager)this.BindingContext[dsCompanyEdit1, "tblCompany"];
cm.EndCurrentEdit();
oleDbDataAdapterCompanyEdit.Update(dsCompanyEdit1, "tblCompany");
MessageBox.Show("Database Updated!");
dsCompanyEdit1.Clear();
oleDbDataAdapterCompanyEdit.Fill(dsCompanyEdit1);
this.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
this.txtCompanyID.Focus();
}
}

}

private void btnCancel_Click(object sender, System.EventArgs e)
{
this.Close();
}

}
}

Thanks again,
Pam
Nov 16 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.