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
