SqlDataAdapter not firing RowUpdated or RowUpdating during successful Insert

Question

I have the following code:

DataSet dataSet = new DataSet();
                bool result;
                using (SqlConnection connection = new SqlConnection(command.Connection.ConnectionString))
            {
            connection.Open();                             

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            adapter.RowUpdated += OnRowUpdated;
            adapter.RowUpdating += AdapterOnRowUpdating;


            adapter.Fill(dataSet);

            // Code to modify data in the DataSet here.
            // Without the SqlCommandBuilder, this line would fail.
            adapter.UpdateCommand = builder.GetUpdateCommand();

            int i = adapter.Update(dataSet);
            result = i > 0;
        }
        return result;

this code is suppose to take my SqlCommand object and submit it to the update method of the SqlDataAdapter. The SqlCommand could be an INSERT, UPDATE or DELETE which is executed using the adapter.Update() method.

The command executes successfully but the adapter.Update() method returns 0 rows. I have checked the database and I can see that it was successful. The RowUpdated and RowsUpdating events are also not firing. I'm not sure whats going on. Please help.

Thank you in advance.

EDIT:

 public void OnRowUpdated(object sender, SqlRowUpdatedEventArgs sqlRowUpdatedEventArgs)
        {
            switch (sqlRowUpdatedEventArgs.StatementType)
            {
                case StatementType.Insert:
                case StatementType.Update:
                case StatementType.Delete:
                case StatementType.Batch:
                    if (sqlRowUpdatedEventArgs.Status != UpdateStatus.ErrorsOccurred)
                        SqlReporting.LogSqlCommand(sqlRowUpdatedEventArgs.Command);
                    break;
            }
        }

EDIT: Im using the following query to Insert:

INSERT INTO Users
                         (Username, Firstname, Lastname, RoleId, ReceiveRoleAlerts, StatusId, Password, Fingerprint, AuthenticityCheck, CreatedByUserId, CreationDate, ModifiedByUserId, 
                         ModificationDate)
SELECT        @Username AS Expr1, @Firstname AS Expr2, @Lastname AS Expr3, @RoleId AS Expr4, @ReceiveRoleAlerts AS Expr5, @StatusId AS Expr6, @Password AS Expr7, 
                         @Fingerprint AS Expr8, @AuthenticityCheck AS Expr9, @CreatedByUserId AS Expr10, @CreationDate AS Expr11, @ModifiedByUserId AS Expr12, 
                         @ModificationDate AS Expr13
WHERE        (NOT EXISTS
                             (SELECT        Username, Firstname, Lastname, RoleId, ReceiveRoleAlerts, StatusId, Password, Fingerprint, AuthenticityCheck, CreatedByUserId, CreationDate, 
                                                         ModifiedByUserId, ModificationDate
                               FROM            Users AS Users_1
                               WHERE        (Username = @Username)));

score 1 · Accepted Answer · answered Jun 01 '12 at 07:59

1

Use

adapter.RowUpdating += new SqlRowUpdatingEventHandler(AdapterOnRowUpdating); 
adapter.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

instead of

adapter.RowUpdated += OnRowUpdated;
adapter.RowUpdating += AdapterOnRowUpdating;

answered Jun 01 '12 at 07:59

Romil Kumar Jain

20,239
9
63
92

Thank you for your quick reply. I have changed the code to reflect your suggestion but unfortunately it is still same issue. I get a successful Insert, but no events are launched and adapter.Update() returns 0 even though the Insert was successful. – Toret Jun 01 '12 at 08:08
I'm using sql server 2008 express. I'm posting the implementation now – Toret Jun 01 '12 at 08:32

SqlDataAdapter not firing RowUpdated or RowUpdating during successful Insert

1 Answers1