Unable to Update Rows in a SQL Table

Question

I have some code that inserts and updates data into a sql table. I use ExecuteScalar to check if a row already exists with the same UserName and DeviceId. If it exists, we run the update stored procedure, overriding the DataSource, PendingCount and LastUpdated fields. If the row doesn't exist, we use the insert stored procedure. The insert stored procedure works great, but the update stored procedure doesn't seem to do anything. The row remains the same. Here is the code in question:

try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand comm = new SqlCommand(query, sqlConnection))
                    {
                        comm.CommandType = System.Data.CommandType.Text;
                        comm.Parameters.Add(new SqlParameter("@DataSource", pending.DataSource));
                        comm.Parameters.Add(new SqlParameter("@LastUpdated", pending.LastUpdated));
                        comm.Parameters.Add(new SqlParameter("@PendingCount", pending.PendingCount));
                        comm.Parameters.Add(new SqlParameter("@DeviceId", pending.DeviceId));
                        comm.Parameters.Add(new SqlParameter("@UserName", pending.UserName));
                        int rowCount = (int)comm.ExecuteScalar();
                        if (rowCount > 0)
                        {
                            using (SqlCommand sqlComm = new SqlCommand("sp_UpdatePendingAttachments", sqlConnection))
                            {
                                sqlComm.CommandType = System.Data.CommandType.Text;
                                sqlComm.Parameters.Add(new SqlParameter("@DataSource", pending.DataSource));
                                sqlComm.Parameters.Add(new SqlParameter("@LastUpdated", pending.LastUpdated));
                                sqlComm.Parameters.Add(new SqlParameter("@PendingCount", pending.PendingCount));
                                sqlComm.Parameters.Add(new SqlParameter("@DeviceId", pending.DeviceId));
                                sqlComm.Parameters.Add(new SqlParameter("@UserName", pending.UserName));
                            }
                        }
                        else
                        {
                            using (SqlCommand sqlCommand = new SqlCommand("sp_InsertPendingAttachments", sqlConnection))
                            {
                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                sqlCommand.Parameters.Add(new SqlParameter("@DataSource", pending.DataSource));
                                sqlCommand.Parameters.Add(new SqlParameter("@UserName", pending.UserName));
                                sqlCommand.Parameters.Add(new SqlParameter("@DeviceId", pending.DeviceId));
                                sqlCommand.Parameters.Add(new SqlParameter("@PendingCount", pending.PendingCount));
                                sqlCommand.Parameters.Add(new SqlParameter("@LastUpdated", pending.LastUpdated));
                                sqlCommand.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return new BaseResponse();
            }

The stored procedure for updating is "sp_UpdateStoredProcedure":

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_UpdatePendingAttachments]
(
    -- Add the parameters for the stored procedure here
    @DataSource VARCHAR(150) = '',
    @DeviceId VARCHAR(150) = '',
    @UserName VARCHAR(150) = '',
    @PendingCount int = null,
    @LastUpdated DateTime = null
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
    UPDATE PendingAttachments
    SET DataSource = @DataSource, PendingItemsCount = @PendingCount, LastUpdated = @LastUpdated
    WHERE DeviceId = @DeviceId AND UserName = @UserName
END
GO

Any idea why updating rows isn't working? Is it a problem with the stored procedure, or the c# code? Any feedback is appreciated!

Answer 1

You need to use some execution method on the mentioned place in the attached image to execute your Transact-SQL statement. You have used ExecuteScaler() once to figure out the existing record but at the time of updation you have missed adding any execution method.