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.Ole Db;
....
namespace EsperanzaThread s
public class frmComapnyEdit : System.Windows. Forms.Form
....
private System.Windows. Forms.TextBox txtCompany;
private System.Windows. Forms.TextBox txtCompanyID;
private System.Data.Ole Db.OleDBCommand oleDbUpdateComm and1;
private EsperanzaThread s.dsCompanyEdit dsCompanyEdit1;
private string strCommandText;
private string strCurrent;
....
public frmComanyEdit(s tring idname)
{strCurrent = idname;
InitializeCompo nent();
....
private void InitializeCompo nent()
{
this.txtCompany = new System.Windows. Forms.TextBox() ;
this.txtCompany ID = new System.Windows. Forms.TextBox() ;
this.lblActive = new System.Windows. Forms.Label();
this.lblCompany = new System.Windows. Forms.Label();
this.lblCompany ID = new System.Windows. Forms.Label();
this.oleDbDataA dapterCompanyEd it = new System.Data.Ole Db.OleDbDataAda pter();
this.oleDbUpdat eCommand1 = new System.Data.Ole Db.OleDbCommand ();
this.oleDbConne ction1 = new System.Data.Ole Db.OleDbConnect ion();
this.dsCompanyE dit1 = new EsperanzaThread s.dsCompanyEdit ();
....
this.txtCompany ID.DataBindings .Add(new System.Windows. Forms.Binding(" Text",
this.dsCompanyE dit1, "tblCompany.Com panyId"));
this.oleDbDataA dapterCompanyEd it.TableMapping s.AddRange(new
System.Data.Com mon.DataTableMa pping[] {
new System.Data.Com mon.DataTableMa pping("Table", "tblCompany ", new
System.Data.Com mon.DataColumnM apping[] {
new System.Data.Com mon.DataColumnM apping("Company Id", "CompanyId" ),
new System.Data.Com mon.DataColumnM apping("Company ", "Company")} )});
this.oleDbDataA dapterCompanyEd it.UpdateComman d = this.oleDbUpdat eCommand1;
this.oleDbUpdat eCommand1.Comma ndText = "UPDATE tblCompany SET CompanyId = ?,
Company = ?, Active = ? WHERE (CompanyId = ?" +") AND (Active = ?) AND
(Company = ?)";
this.oleDbUpdat eCommand1.Conne ction = this.oleDbConne ction1;
this.oleDbUpdat eCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("CompanyId" ,
System.Data.Ole Db.OleDbType.Va rWChar, 1, "CompanyId" ));
this.oleDbUpdat eCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("Company",
System.Data.Ole Db.OleDbType.Va rWChar, 50, "Company")) ;
this.oleDbUpdat eCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("Active",
System.Data.Ole Db.OleDbType.Bo olean, 2, "Active"));
this.oleDbUpdat eCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("Original_Co mpanyId",
System.Data.Ole Db.OleDbType.Va rWChar, 1,
System.Data.Par ameterDirection .Input, false, ((System.Byte)( 0)),
((System.Byte)( 0)), "CompanyId" , System.Data.Dat aRowVersion.Ori ginal, null));
this.oleDbUpdat eCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("Original_Ac tive",
System.Data.Ole Db.OleDbType.Bo olean, 2, System.Data.Par ameterDirection .Input,
false, ((System.Byte)( 0)), ((System.Byte)( 0)), "Active",
System.Data.Dat aRowVersion.Ori ginal, null));
this.oleDbUpdat eCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("Original_Co mpany",
System.Data.Ole Db.OleDbType.Va rWChar, 50,
System.Data.Par ameterDirection .Input, false, ((System.Byte)( 0)),
((System.Byte)( 0)), "Company", System.Data.Dat aRowVersion.Ori ginal, null));
....
this.dsCompanyE dit1.DataSetNam e = "dsCompanyEdit" ;
....
private void frmCompanyEdit_ Load(object sender, System.EventArg s e)
{
strCommandText = "SELECT CompanyID, Company, Active FROM tblCompany WHERE
CompanyID = '" + strCurrent + "'";
this.oleDbSelec tCommand1.Comma ndText = strCommandText;
oleDbDataAdapte rCompanyEdit.Fi ll(dsCompanyEdi t1);
}
private void btnSave_Click(o bject sender, System.EventArg s 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.T ables["tblCompany "];
DataRow dr = dt.NewRow();
drNew["CompanyID"] = this.txtCompany ID.Text;
drNew["Company"] = this.txtCompany .Text;
drNew["Active"] = this.cmbActive. Text;
dt.Rows.Add(drN ew);
oleDbDataAdapte rCompanyAdd.Upd ate(dsCompanyAd d1,"tblCompany" );
dsCompanyAdd1.C lear();
oleDbDataAdapte rCompanyAdd.Fil l(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.UpdateCo mmand.Parameter s.Add("@Custome rID", OleDbType.Char, 5,
"CustomerID ");
custDA.UpdateCo mmand.Parameter s.Add("@Company Name", OleDbType.VarCh ar, 40,
"CompanyNam e");
OleDbParameter myParm =
custDA.UpdateCo mmand.Parameter s.Add("@OldCust omerID", OleDbType.Char, 5,
"CustomerID ");
myParm.SourceVe rsion = DataRowVersion. Original;
public static OleDbDataAdapte r CreateCustomerA dapter(OleDbCon nection conn)
{
OleDbDataAdapte r da = new OleDbDataAdapte r();
OleDbCommand cmd;
OleDbParameter parm;
// Create the UpdateCommand.
cmd = new OleDbCommand("U PDATE Customers SET CustomerID = @CustomerID,
CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID" , conn);
cmd.Parameters. Add("@CustomerI D", OleDbType.Char, 5, "CustomerID ");
cmd.Parameters. Add("@CompanyNa me", OleDbType.VarCh ar, 40, "CompanyNam e");
parm = cmd.Parameters. Add("@oldCustom erID", OleDbType.Char, 5,
"CustomerID ");
parm.SourceVers ion = DataRowVersion. Original;
da.UpdateComman d = cmd;
return da;
}
Thanks,
Pam
