Sqlcommand Parameters not executing

Question

I am encountering a strange problem when attempting to execute a DELETE query agains a SQL Server table using VB.NET, SQL Command, and Parameters.

I have the following code:

Try    
    sqlCommand.Transaction = transaction1
    sqlCommand.Connection = conn
    sqlCommand.CommandText = sqlQuery
    sqlCommand.Parameters.Add("@userID", SqlDbType.Int).Value = Convert.ToInt32(userID)
    sqlCommand.Parameters.Add("@groupID", SqlDbType.Int).Value = Convert.ToInt32(groupID)
    ''#Delete the user from the group.
    MessageBox.Show("User: " + Convert.ToString(userID) + "    Group: " + Convert.ToString(groupID))
    MessageBox.Show("Param, UserID: " + sqlCommand.Parameters.Item(0).Value.ToString)
    MessageBox.Show("Param, GroupID: " + sqlCommand.Parameters.Item(1).Value.ToString)
    return_deleteUser = sqlCommand.ExecuteNonQuery()
Catch ex As Exception
    transaction1.Rollback()
    Dim hr As Integer = Marshal.GetHRForException(ex)
    MsgBox("Removal of user from group has failed: " + ex.Message() & hr)
End Try

Which executes the following SQL Query:

Dim sqlQuery As String = "DELETE FROM MHGROUP.GROUPMEMS WHERE USERNUM =@userID AND GROUPNUM =@groupID"

My problem is that when the code executes, there is no error reported at all. I have ran SQL Profiler and the query doesn't appear in the trace list. The three messageboxes that I have added all return the correct values, and if I was to execute the SQL query against the table with the values the query succeeds. Both the userID and groupID are 3-digit integers.

Can anyone suggest why the code is not working as intended, or any further debugging that I can use to step through the code? Ideally I would love to see the completed SQL query with the parameters completed, but I haven't found out how to do this.

EDIT: I have the following later in the code to check if the execute's all processed successfully:

If return_insertEvent > 0 And return_updateUser > 0 And return_nextSID > 0 And return_deleteUser > 0 Then
    MessageBox.Show("Success")
    return_removeADGroup = RemoveUserFromGroup(userID, groupName)
    MessageBox.Show("Remove FS User from AD Group: " + return_removeADGroup)
    transaction1.Commit()
    transaction2.Commit()
    transaction3.Commit()
    transaction4.Commit()
    returnResult = 1
Else
    transaction1.Rollback()
    transaction2.Rollback()
    transaction3.Rollback()
    transaction4.Rollback()
    returnResult = 0
End If

If you require any further information please don't hesitate in contacting me.

Answer 1

You are missing a Transaction.Commit

Update in respone to additional info added to question:

Why do you have 4 transactions? Since their commit and rollbacks are all executed together, you only need one transaction. I suggest you use a TransactionScope

You can assign the current transaction to ADO.NET Command objects:

ADO.NET and System.Transactions

Transaction Processing in ADO.NET 2.0

Answer 2

I might guess that your calling proc has the values of userid and groupid backwards. If the DELETE doesn't find a matching record, it will complete successfully, but not do anything. I suggest wrapping your delete up in a stored procedure. Then you can add code to test if the parameter values are getting through correctly.

Create Procedure UserDelete
@userid int, @groupID int
As
BEGIN
    Select @userid as UID, @groupID as GID INTO TESTTABLE;
    DELETE FROM MHGROUP.GROUPMEMS WHERE USERNUM =@userID AND GROUPNUM =@groupID;
END

Run your code then go check the contents of TESTTABLE.

FWIW: I don't like trying to get the whole parameter declaration in one line. Too much going on for me. I like this...

Dim pUID as New Parameter("@userid", SqlDbType.Int)
pUID.Value = userid
cmd.Parameters.Add(pUID)

Answer 3

After some time debugging and sql tracing, I have found out that the stupid application that the DB belongs to treats the group members differently, the groups reside in a administration database, but the users membership to the group resides in another database.

Thank you to everyone above who provided there time and thoughts in assisting with the code. I have changed the code as recomended to use only two transactions and two connections (1 for the admin and sub-database). The code is much nicer now and is that bit easier to read.

Thanks again,

Matt