dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Problem updating DataSource using DataAdapter

  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Problem updating DataSource using DataAdapter

    Hi All,

    I'm having a problem updating DataSource using DataAdapter.Update method. I have an app that allows user to read data from the database and display it on the DataGridView. The user can insert, delete, update the data in DataGridView and hit the "Update" button to save the changes.

    Below is the listing of my code:

    string dbConString = "Data Source = MYVISTA\\SQLEXPRESS; Initial Catalog = ChinChinDB; Integrated Security = SSPI";

    string dbUpdate = "UPDATE " + txtTable.Text + " SET Dish = @Dish, Price = @Price, Description = @Description WHERE Dish = @Dish";
    string dbInsert = "INSERT INTO " + txtTable.Text + " (Dish, Price, Description) VALUES (@Dish, @Price, @Description)";
    string dbDelete = "DELETE FROM " + txtTable.Text + " WHERE Dish = @Dish";

    con.ConnectionString = dbConString;

    daGetTables.UpdateCommand = cmdUpdate;
    daGetTables.InsertCommand = cmdInsert;
    daGetTables.DeleteCommand = cmdDelete;

    daGetTables.UpdateCommand.CommandText = dbUpdate;
    daGetTables.UpdateCommand.Connection = con;
    daGetTables.UpdateCommand.Parameters.Add("@Dish", SqlDbType.VarChar, 50, "Dish");
    daGetTables.UpdateCommand.Parameters.Add("@Price", SqlDbType.Float, 10, "Price");
    daGetTables.UpdateCommand.Parameters.Add("@Description", SqlDbType.VarChar, 200, "Description");

    daGetTables.InsertCommand.CommandText = dbInsert;
    daGetTables.InsertCommand.Connection = con;
    daGetTables.InsertCommand.Parameters.Add("@Dish", SqlDbType.VarChar, 50, "Dish");
    daGetTables.InsertCommand.Parameters.Add("@Price", SqlDbType.Float, 10, "Price");
    daGetTables.InsertCommand.Parameters.Add("@Description", SqlDbType.VarChar, 200, "Description");

    daGetTables.DeleteCommand.CommandText = dbDelete;
    daGetTables.DeleteCommand.Connection = con;
    daGetTables.DeleteCommand.Parameters.Add("@Dish", SqlDbType.VarChar, 50, "Dish");

    try
    {
    daGetTables.Fill(dtTables);
    daGetTables.Update(dtTables);
    }
    catch(SqlException sqlEx)
    {
    lboxActivities.Items.Add(rowCountUpdateTables + ". " + sqlEx.Message.ToString());
    rowCountUpdateTables++;
    }
    finally
    {
    //Do Something
    }
    ------------------------------------------------------------------------

    Insert and Delete are working fine, except Update. Whenever a row is updated and the update button is clicked, I get a "DBConcurrencyException" error.

    Does anybody know what I'm missing?

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome to DevX

    I believe you have to add the ConflictOption.OverwriteChanges to the CommandBuilder object, to be able to update rows. I had the same problem, added the conflictoption and all problems disapeared.

    MySqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    Hi Hack

    Thanks for your reply. That was my first time posting, so please let me know if I'm not using the proper way to post code listings

    Anyway, the problem still exists. I added these:
    Code:
    SqlCommandBuilder cmdBuild = new SqlCommandBuild(daGetTables);
    .
    .
    .
    try
    {
         cmdBuild.ConflictOption = ConflictOption.OverwriteChanges;
         daGetTables.Update(dtTables);
    }
    catch(DBConcurrencyException ex)
    {
         //Do Something
    }
    finally
    {
         //Do Something Else
    }
    When clicking on the "Update" button, I'm still getting the "DBConcurrencyException" error

    One thing that I notice is, it only happens if I try to update the column specified in the WHERE clause. For example:

    dbUpdate = "UPDATE <TableName> SET FirstName = @FirstName, LastName = @LastName WHERE FirstName = @FirstName";

    If I update the FirstName in the DataGridView and click on Update, then it gives me that error. but if I only update the LastName, and leaving FirstName the same, then the Update process works just fine.

    Any idea what is wrong here?

  4. #4
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    Your trying to identify the record by the field you've updated. If you want to reliably identify a single row for updating you should be using a field you will not be changing.

    ...joe

  5. #5
    Join Date
    Mar 2008
    Posts
    3
    Yeah, I was thinking about that too. I'll try to create a PK column so that I can change the other 2 columns.

    Thanks.

Similar Threads

  1. CMR problem
    By vikassheelgupta in forum Java
    Replies: 0
    Last Post: 07-22-2005, 05:35 AM
  2. Java Applet Compiler problem?
    By mdl in forum Java
    Replies: 3
    Last Post: 03-07-2005, 03:34 AM
  3. Problem with Updating a column in an ASP.Net Datagrid
    By David Hirschfeld in forum .NET
    Replies: 0
    Last Post: 09-17-2002, 12:15 PM
  4. Replies: 0
    Last Post: 12-13-2001, 01:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center