?
Solved

ExecuteNonQuery works, DataAdapter doesn't

Posted on 2005-05-14
3
Medium Priority
?
1,118 Views
Last Modified: 2008-03-06
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.
0
Comment
Question by:johnmullally
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
b1xml2 earned 1000 total points
ID: 14004734
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
 
LVL 27

Expert Comment

by:Dabas
ID: 14004777
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
 
LVL 23

Expert Comment

by:b1xml2
ID: 14004781
dabas is correct too
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question