how to write the update query in 3tire archetucture by using MS

// this is data access layer code
public bool Update(BOL.bolCompanyDetailsMaster obj)
{
dbConnection conn =new dbConnection();
string sql = "update [CompanyDetailsMaster] set [BranchName]=@BranchName,[Address]=@Address,[PhoneNo]=@PhoneNo,[EmailId]=@EmailId,[CompanyLogo]=@CompanyLogo where BranchCode=@BranchCode";

OleDbParameter[] oledbParameters = new OleDbParameter[6];
//sqlParameters[0] = new SqlParameter("@Srlno", SqlDbType.Int);
//sqlParameters[0].Value = obj.Srlno;
oledbParameters[0] = new OleDbParameter("@BranchCode", OleDbType.VarChar);
oledbParameters[0].Value = obj.Branchcode;
oledbParameters[1] = new OleDbParameter("@BranchName", OleDbType.VarChar);
oledbParameters[1].Value = obj.Branchname;
oledbParameters[2] = new OleDbParameter("@Address", OleDbType.VarChar);
oledbParameters[2].Value = obj.Address;
oledbParameters[3] = new OleDbParameter("@PhoneNo", OleDbType.VarChar);
oledbParameters[3].Value = obj.Phoneno;
oledbParameters[4] = new OleDbParameter("@EmailId", OleDbType.VarChar);
oledbParameters[4].Value = obj.Emailid;
oledbParameters[5] = new OleDbParameter("@CompanyLogo", OleDbType.VarChar);
oledbParameters[5].Value = obj.Companylogo;
return conn.executeUpdateQuery(sql, oledbParameters);

}
//this is business layer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace PayRoll.BOL
{
class bolCompanyDetailsMaster
{
DAL.dalCompanyDetailsMaster dalObj= new PayRoll.DAL.dalCompanyDetailsMaster();
private Int32 m_Srlno;
private string m_Branchcode;
private string m_Branchname;
private string m_Address;
private string m_Phoneno;
private string m_Emailid;
private string m_Companylogo;
private string m_StdCode;
private string m_LandLine;
///
/// Constructor bolCompanyDetailsMaster
///
public bolCompanyDetailsMaster()
{
//Initialize the variable here
}
///

/// ColumnName:Srlno
/// Data Type:int
/// Length:0
///

public Int32 Srlno
{
get
{
return m_Srlno;
}
set
{
m_Srlno = value;
}
}
///

/// ColumnName:Branchcode
/// Data Type:VarChar
/// Length:20
///

public string Branchcode
{
get
{
return m_Branchcode;
}
set
{
if (value == "")
{
throw new InvalidData("Branch Code cannot be empty", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
if (value.Length > 20)
{
throw new InvalidData("The Branchcode is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_Branchcode = value;
}
}
///

/// ColumnName:Branchname
/// Data Type:VarChar
/// Length:50
///

public string Branchname
{
get
{
return m_Branchname;
}
set
{
if (value.Length > 50)
{
throw new InvalidData("The Branch Name is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_Branchname = value;
}
}
///

/// ColumnName:Address
/// Data Type:VarChar
/// Length:50
///

public string Address
{
get
{
return m_Address;
}
set
{
if (value.Length > 50)
{
throw new InvalidData("The Address is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_Address = value;
}
}
///

/// ColumnName:Phoneno
/// Data Type:nVarChar
/// Length:20
///

public string Phoneno
{
get
{
return m_Phoneno;
}
set
{
m_Phoneno = value;
}
}
///

/// ColumnName:Emailid
/// Data Type:VarChar
/// Length:50
///

public string Emailid
{
get
{
return m_Emailid;
}
set
{
if (value.Length > 50)
{
throw new InvalidData("The Emailid is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_Emailid = value;
}
}
///

/// ColumnName:Companylogo
/// Data Type:VarChar
/// Length:150
///

public string Companylogo
{
get
{
return m_Companylogo;
}
set
{
if (value.Length > 150)
{
throw new InvalidData("The Companylogo is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_Companylogo = value;
}
}
public string StdCode
{
get
{
return m_StdCode;
}
set
{
if (value.Length > 20)
{
throw new InvalidData("The StdCode is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_StdCode = value;
}
}
public string LandLine
{
get
{
return m_LandLine;
}
set
{
if (value.Length > 20)
{
throw new InvalidData("The LandLine is more length", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
m_LandLine = value;
}
}
public bool Insert ()
{
dalObj.Insert(this);
return true;
}
public bool Update()
{
dalObj.Update(this);
return true;
}
public void get()
{
dalObj.get(this);
}
}
}

//Presentation Layer
BOL.bolCompanyDetailsMaster mcd = new PayRoll.BOL.bolCompanyDetailsMaster();
mcd.Branchcode = txtbranchcode.Text;
mcd.Branchname = txtbranchname.Text;
mcd.Address = txtaddress.Text;
mcd.StdCode = txtStdCode.Text;
mcd.LandLine = txtLandline.Text;
mcd.Phoneno = txtmobileno.Text;
mcd.Emailid = txtemailid.Text;
mcd.Companylogo = destFileName;
mcd.Insert();
MessageBox.Show("Record Saved Successfully", "Payroll", MessageBoxButtons.OK, MessageBoxIcon.Information );

My problem is not update the data into Ms Access Database

I have attached very simple solution to update the MSAccess database. And it is working fine.

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }

}

    public class DBOperation
    {
        public void Update(Student student)
        {
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Murali\Documents\Database1.accdb;Persist Security Info=False;";
            con.Open();
            string sql = "update [table1] set name=@name, age=@age where Id = @id";
            OleDbParameter[] oledbParameters = new OleDbParameter[3];
            oledbParameters[0] = new OleDbParameter("@name",OleDbType.VarChar);
            oledbParameters[0].Value = student.Name;

            oledbParameters[1] = new OleDbParameter("@age", OleDbType.VarChar);
            oledbParameters[1].Value = student.Age;
            oledbParameters[2] = new OleDbParameter("@id", OleDbType.Integer);
            oledbParameters[2].Value = student.Id;

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(oledbParameters);
            cmd.ExecuteNonQuery();
            con.Close();

}
}

In your application, are you getting any exception?

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

14y ago
I have attached very simple solution to update the MSAccess database. And it is working fine.

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
    public class DBOperation
    {
        public void Update(Student student)
        {
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Murali\Documents\Database1.accdb;Persist Security Info=False;";
            con.Open();
            string sql = "update [table1] set name=@name, age=@age where Id = @id";
            OleDbParameter[] oledbParameters = new OleDbParameter[3];
            oledbParameters[0] = new OleDbParameter("@name",OleDbType.VarChar);
            oledbParameters[0].Value = student.Name;
            oledbParameters[1] = new OleDbParameter("@age", OleDbType.VarChar);
            oledbParameters[1].Value = student.Age;
            oledbParameters[2] = new OleDbParameter("@id", OleDbType.Integer);
            oledbParameters[2].Value = student.Id;

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(oledbParameters);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

In your application, are you getting any exception?

how to write the update query in 3tire archetucture by using MS_Access