Link to home
Start Free TrialLog in
Avatar of johnmullally
johnmullally

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of b1xml2
b1xml2
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
dabas is correct too