Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    4
    Thanks
    0
    Thanked 2 Times in 1 Post

    C# SqlCommandBuilder not performing delete

    Im going insane trying to figure this one out.

    Im populating a DataTable from my database.
    A DataGridView DataSource is set to my DataTable.

    I insert, update and delete rows from the DataTable which in turn updates the DataGridView.

    Im using the below to update my changes back to the database where i pass in the modified DataTable.
    Insert and Update are working but Delete does not remove the deleted rows from the database table.

    Code:
    private static void updateDatabase(DataTable dt)
    {
        SqlDataAdapter myAdapter = new SqlDataAdapter();
        SqlCommand myCommand = new SqlCommand("SELECT * from dbo.DisplayList", sqlCon);
        SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
    
        myAdapter.SelectCommand = myCommand;
    
        myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
        myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
        myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
    
        myAdapter.Update(dt);
    }
    The myAdapter.DeleteCommand.CommandText is:
    Code:
    DELETE FROM [dbo].[DisplayList] WHERE (([PropertyID] = @p1) AND 
    ([DisplayID] = @p2) AND ([DisplayName] = @p3) AND ((@p4 = 1 AND 
    [TerminalID] IS NULL) OR ([TerminalID] = @p5)))
    Anyone have any ideas whats wrong with the above or to help investigation how i can find out what the @p values are being converted to?

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,739
    Thanks
    41
    Thanked 191 Times in 190 Posts
    I hardly ever use the dataadapter- I use straight up cmd objects and stored procs... but...

    so that delete command you posted... I assume that is captured off a break point on myAdapter.DeleteCommand = etc ?

    so my question is this... that is not a valid sql statement...
    Code:
    DELETE FROM [dbo].[DisplayList] WHERE (([PropertyID] = @p1) AND 
    ([DisplayID] = @p2) AND ([DisplayName] = @p3) AND ((@p4 = 1 AND 
    [TerminalID] IS NULL) OR ([TerminalID] = @p5)))
    it should pop an error, unless you have those values @p1,@p2, etc... if you DO have those values, have you tried running that query in SQLManagement studio?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #3
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    4
    Thanks
    0
    Thanked 2 Times in 1 Post
    That SQL is retrieved by using MessageBox.Show(myAdapter.DeleteCommand.CommandText);

    I believe the SqlCommandBuilder automatically replaces the @p values with actual values when it runs.

    The update and inserts show the same @p values but they work fine.

  • #4
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    4
    Thanks
    0
    Thanked 2 Times in 1 Post
    Finally figured it out.

    When deleting the rows from the datatable i was using:
    myDataTable.Rows.RemoveAt(rowindex);
    This completly removes it from the datatable so when the SqlCommandBuilder came around to deleting it from the database it never knew it existed in the first place.

    For it to work you need to flag the row as deleted but not actually delete it.
    myDataTable.Rows[rowindex].Delete();

    By doing this it all worked.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •