ExecuteNonQuery works, DataAdapter doesn't

I am able to Update and Delete using ExecuteNonQuery, but unable to do so using the DataAdapter - strange. Experienced problems when following a Tutorial that used wizards, dataadapter, datagrid, and SQL Server.  But to best diagnose, I transitioned to simple code (first attempt) and added a very simple new table with just 2 fields and no rules or relations in order to rule out dependencies, special database table restriction, or relation problems. The second attempt is the result of debug effort - still short on clues.  

In both attempts the DELETE fails with the DataAdapter/Command Builder but the ExecuteNonQuery works.

first attempt:
private void Page_Load(object sender, System.EventArgs e)
{
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
                                             "integrated security=SSPI;data source=JOHN;" +
                                             "persist security info=False;initial catalog=Northwind";
        conn.Open();
                  
        string strSQL1 = "SELECT ShipAddressID, ShipAddress FROM dbo.ShipAddress";
        DataSet ds = new DataSet("dsNorthwind");
        SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);

        // Delete with DataAdapter does not work
        SqlCommandBuilder cb = new SqlCommandBuilder(da);  
        da.Fill(ds, "ShipAddress");
        ds.Tables["ShipAddress"].Rows.RemoveAt(0);
        da.Update(ds, "ShipAddress");

        // Delete with SQL Command works
        string strSQL3 = "DELETE FROM ShipAddress WHERE ShipAddress='Third Address'";
        SqlCommand cmd3 = new SqlCommand(strSQL3, conn);
        int count = cmd3.ExecuteNonQuery();
                  
        conn.Close();
}


second attempt:
private void Page_Load(object sender, System.EventArgs e)
{
    try
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
                                             "integrated security=SSPI;data source=JOHN;" +
                                             "persist security info=False;initial catalog=Northwind";
        conn.Open();
                  
        string strSQL1 = "SELECT ShipAddressID, ShipAddress " +
                                "FROM dbo.ShipAddress";
        DataSet ds = new DataSet("dsNorthwind");
        SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);
        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;      // removing doesn't help

        // Delete with DataAdapter does not work
        SqlCommandBuilder cb = new SqlCommandBuilder(da);  
        da.UpdateCommand = cb.GetUpdateCommand();       // removing doesn't help
        da.DeleteCommand = cb.GetDeleteCommand();         // removing doesn't help
        da.Fill(ds, "ShipAddress");
        DataRow dr = ds.Tables["ShipAddress"].Rows[0];
        string ShipAddress1 = (string)dr["ShipAddress"];             // gets 'First Address'
        int numRows1 = ds.Tables["ShipAddress"].Rows.Count;   // equals 4  
        ds.Tables["ShipAddress"].Rows.RemoveAt(0);

        da.Update(ds, "ShipAddress");
        dr = ds.Tables["ShipAddress"].Rows[0];
        string ShipAddress2 = (string)dr["ShipAddress"];           // now gets 'Second Address'  
        int numRows2 = ds.Tables["ShipAddress"].Rows.Count;  // now equals 3

        ds.Tables["ShipAddress"].Reset();
        da.Fill(ds, "ShipAddress");            
        dr = ds.Tables["ShipAddress"].Rows[0];      
        string ShipAddress3 = (string)dr["ShipAddress"];            //  back to 'First Address' since  da.Update  did nothing
        int numRows3 = ds.Tables["ShipAddress"].Rows.Count;   // back to 4

        Response.Write("Count=" + numRows1.ToString() + "/" + numRows2.ToString()+
                                "/" + numRows3.ToString() + " Address=" + ShipAddress1 + "/" +
                                ShipAddress2 + "/" + ShipAddress3);
       // above prints:  "Count=4/3/4 Address=First Address /Second Address /First Address"

        // Delete with SQL Command works
        string strSQL3 = "DELETE FROM ShipAddress WHERE ShipAddress='Third Address'";
        SqlCommand cmd3 = new SqlCommand(strSQL3, conn);
        int count = cmd3.ExecuteNonQuery();
                  
        conn.Close();
    }
    catch (Exception caught)
    {
        Response.Write(caught.ToString());  // does not execute
    }
}

I also tried experimenting with SQL Server to no avail.  Spent a lot of time on this already.  This problem shows up on 2 different computers.  

Thanks for your help.
johnmullallyAsked:
Who is Participating?
 
b1xml2Commented:
private void Page_Load(object sender, System.EventArgs e)
{
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
                                             "integrated security=SSPI;data source=JOHN;" +
                                             "persist security info=False;initial catalog=Northwind";
        conn.Open();
               
        string strSQL1 = "SELECT ShipAddressID, ShipAddress FROM dbo.ShipAddress";
        DataSet ds = new DataSet("dsNorthwind");
        SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);

        // Delete with DataAdapter does not work
        SqlCommandBuilder cb = new SqlCommandBuilder(da);  
        da.Fill(ds, "ShipAddress");
            ds.Tables["ShipAddress"].Rows[0].Delete();
        da.Update(ds, "ShipAddress");
        ds.AcceptChanges();

       
       
        conn.Close();
}

In order to get the record to be removed from the database, you must mark the row as deleted. You do this by calling the Delete() method which changes the DataRowState to Deleted. This will allow the DeleteCommand to pick up the record and delete it accordingly.

Using the Remove() and RemoveAt() removes the row from the DataTable and does not allow the adapter to find the row to be deleted
0
 
DabasCommented:
Hi johnmullally:
>         SqlCommandBuilder cb = new SqlCommandBuilder(da);  
Creating a ComandBuilder is not enough!
You still need to use it to create the DeleteCommand

da.DeleteCommand = cb.GetDeleteCommand


Dabas
0
 
b1xml2Commented:
dabas is correct too
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.