[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1205
  • Last Modified:

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

0
canuckconsulting
Asked:
canuckconsulting
1 Solution
 
oobaylyCommented:
First, you need to set the update command for the dataadapter.

Remove the ds.AcceptChanges() line in the button click event handler, and un-comment the da.Update(ds) line.

Personally, I'd use ASP.Net to do the databinding for me. That way you're not keeping the DataSet & DataAdapter in the Session variable, which could affect performance. Also, I'd recommend using parameters for the Select command.
// Select command with parameters
da.SelectCommand = new OleDbCommand(
  "select * from Buyer where ID = @id",
  cnDB);
da.SelectCommand.Parameters.AddWithValue("id", Session["BuyerID"]);
 
 
// Set the update command
da.SelectCommand = cmdBuilder.GetUpdateCommand();

Open in new window

0
 
canuckconsultingAuthor Commented:
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

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now