SqlCommandExecuteNonQuery() issue
Hi
I've got a stored proc which I've tested that updates or inserts records based on a few simple parameters. I've also tested it that it doesn't insert or update if an invalid I'd id is tried for example
Everything works fine in query anaylser - it says the number of records affected, e.g. 1, 0, etc. Plus I've got no select statement in my stored proc either. But whatever I do, when I execute the storedproc with the SqlCommand.ExecuteNonQuery() and examine the result it's always "1". Even if no records are affected.
I've tried using set nocount off and selecting @@recordcount but to no avail. I'm probably missing something really small, so your help would be appreciated thanks!
SPROC:
BEGIN
IF(@CommunicationId = 0)
BEGIN
INSERTINTO
Communication(GroupId, PersonId, IsPrimaryContactMethod, CommunicationValue, CommunicationType, Note, CommunicationStatus)
VALUES
(@GroupId, @PersonId, @IsPrimaryContactMethod, @CommunicationValue, @CommunicationType, @Note, @CommunicationStatus)
END
ELSE
BEGIN
UPDATE
Communication
SET
GroupId = @GroupId,
PersonId = @PersonId,
IsPrimaryContactMethod = @IsPrimaryContactMethod,
CommunicationValue = @CommunicationValue,
CommunicationType = @CommunicationType,
Note = @Note,
CommunicationStatus = @CommunicationStatus
WHERE
CommunicationId = @CommunicationId
END
END
Code:
publicbool UpdateCommunication(Communication communication)
{
int i = 0;
using (SqlCommand sqlCmd = _cmdHelper.GetCommand("Communication_UPDATE"))
{
sqlCmd.Parameters.Add("@CommunicationId", SqlDbType.Int).Value = communication.CommunicationId;
sqlCmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = communication.GroupId;
sqlCmd.Parameters.Add("@PersonId", SqlDbType.Int).Value = communication.PersonId;
sqlCmd.Parameters.Add("@IsPrimaryContactMethod", SqlDbType.Bit).Value = communication.IsPrimaryContactMethod;
sqlCmd.Parameters.Add("@CommunicationValue", SqlDbType.VarChar,255).Value = communication.CommunicationValue;
sqlCmd.Parameters.Add("@CommunicationType", SqlDbType.TinyInt).Value = communication.CommunicationType;
sqlCmd.Parameters.Add("@Note", SqlDbType.VarChar,255).Value = communication.Note;
sqlCmd.Parameters.Add("@CommunicationStatus", SqlDbType.TinyInt).Value = communication.CommunicationStatus;
i = sqlCmd.ExecuteNonQuery();
}
return i == 1;
}
|
