asked on

Unable to update DataSet using OleDbDataAdapter

c# web application in 2008 connecting to an Access 2007 db. I am retrieving data from the db and displaying details in text boxes. On the form I have an update asp button which i would like to persist changed made by the user to the database.

Sounded easy peasy a couple hours ago. Any help gratefully received!

protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds;
 
            if (Session["ds"] == null)
            {
                string sql = "select * from Buyer where ID = " + Session["BuyerID"];
                OleDbConnection cnDB = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Development\\BBC_WWD\\BBC_WWD\\App_Data\\BBC_WWD.accdb");
                OleDbDataAdapter da = new OleDbDataAdapter();
                OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);                
                ds = new DataSet();
 
                da.SelectCommand = new OleDbCommand(sql, cnDB);
 
                cnDB.Open();
                da.Fill(ds);
 
                DataColumn[] keys = { ds.Tables[0].Columns[0] };
                ds.Tables[0].PrimaryKey = keys;
 
                Session["ds"] = ds;
                Session["da"] = da;
            }
            else
                ds = (DataSet)Session["ds"];
 
            txtFirst.Text = ds.Tables[0].Rows[0]["First Name"].ToString();
            txtLast.Text = ds.Tables[0].Rows[0]["Last Name"].ToString();
            txtPassword.Text = ds.Tables[0].Rows[0]["Password"].ToString();
        }
 
        protected void Button1_Click(object sender, EventArgs e)
        {
            OleDbDataAdapter da = (OleDbDataAdapter)Session["da"];
            DataSet ds = (DataSet)Session["ds"];
 
            ds.Tables[0].BeginLoadData();
            ds.Tables[0].Rows[0]["First Name"] = txtFirst.Text;
            txtFirst.Text = ds.Tables[0].Rows[0]["First Name"].ToString();
            ds.Tables[0].Rows[0]["Last Name"] = txtLast.Text;
            ds.Tables[0].Rows[0]["Password"] = txtPassword.Text;
            ds.Tables[0].EndLoadData();
            ds.AcceptChanges();
            //da.Update(ds);
 
            Session["ds"] = ds;
            Session["da"] = da;
        }

Open in new window

ASKER CERTIFIED SOLUTION

oobayly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

canuckconsulting

ASKER

Hello and sorry for the late reply! I have tried this but see no difference at all. The code is now as follows. Can you think of anything else to try?

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["BuyerID"] == null)
                Page.Response.Redirect("Login.aspx");
 
            DataSet ds;
 
            if (Session["ds"] == null)
            {
                string sql = "select * from Buyer where ID = " + Session["BuyerID"];
                OleDbConnection cnDB = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Development\\BBC_WWD\\BBC_WWD\\App_Data\\BBC_WWD.accdb");
                OleDbDataAdapter da = new OleDbDataAdapter();
                OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);    
               
                ds = new DataSet();
 
                da.SelectCommand = new OleDbCommand(sql, cnDB);
                da.UpdateCommand = cmdBuilder.GetUpdateCommand();
 
                cnDB.Open();
                da.Fill(ds);
 
                DataColumn[] keys = { ds.Tables[0].Columns[0] };
                ds.Tables[0].PrimaryKey = keys;
 
                Session["ds"] = ds;
                Session["da"] = da;
            }
            else
                ds = (DataSet)Session["ds"];
 
            txtFirst.Text = ds.Tables[0].Rows[0]["First Name"].ToString();
            txtLast.Text = ds.Tables[0].Rows[0]["Last Name"].ToString();
            txtPassword.Text = ds.Tables[0].Rows[0]["Password"].ToString();
        }
 
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            OleDbDataAdapter da = (OleDbDataAdapter)Session["da"];
            DataSet ds = (DataSet)Session["ds"];
 
            ds.Tables[0].BeginLoadData();
            ds.Tables[0].Rows[0]["First Name"] = txtFirst.Text;
            txtFirst.Text = ds.Tables[0].Rows[0]["First Name"].ToString();
            ds.Tables[0].Rows[0]["Last Name"] = txtLast.Text;
            ds.Tables[0].Rows[0]["Password"] = txtPassword.Text;
            ds.Tables[0].EndLoadData();
            da.Update(ds);
 
            Session["ds"] = ds;
            Session["da"] = da;
        }

Open in new window