Update Multiple Columns in a Row Using ADO.NET

Question

I am working on a project(not public) and I am trying to update my Database code using ADO.NET. I have written working code for insert, retrieve all, retrieve by ID, and retrieve by status, the one I can't seem to figure out is the Update. I've done quite a bit of searching and the code I have is representative of the information I have found and tried adapting to my program. I am just using garbage data in order to try and see the update before using any sort of tangible data.

Below is my query code inside a Class dedicated for queries.

public Ticket UpdateTicket(int id, string status, int customerId, int helpDeskStaffId, string problemDesc, string resolution, string followUpRequired, string followUpComplete, DateTime ticketDate, DateTime resolvedDate)
    {
        Ticket ticket = new Ticket(id, status, customerId, helpDeskStaffId, problemDesc, resolution, followUpRequired, followUpComplete, ticketDate, resolvedDate);
        SqlCommand cmdInsert;
        SqlConnection conn = null;
        try
        {
            string connectString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            conn = new SqlConnection(connectString);
            "UPDATE Ticket SET Status = @Status, HelpDeskStaffId = @HelpDeskStaffId, ProblemDesc = @ProblemDesc, Resolution = @Resolution, FollowUpRequired = @FollowUpRequired, FollowUpComplete = @FollowUpComplete, TicketDate = @TicketDate, ResolvedDate = @ResolvedDate, CustomerId = @CustomerId WHERE TicketId=@id";
            conn.Open();
            cmdInsert = new SqlCommand(sql2, conn);
            cmdInsert.Parameters.AddWithValue("@id", id);
            cmdInsert.Parameters.AddWithValue("@Status", status);
            cmdInsert.Parameters.AddWithValue("@HelpDeskStaffId", helpDeskStaffId);
            cmdInsert.Parameters.AddWithValue("@ProblemDesc", problemDesc);
            cmdInsert.Parameters.AddWithValue("@Resolution", resolution);
            cmdInsert.Parameters.AddWithValue("@FollowUpRequired", followUpRequired);
            cmdInsert.Parameters.AddWithValue("@FollowUpComplete", followUpComplete);
            cmdInsert.Parameters.AddWithValue("@TicketDate", ticketDate);
            cmdInsert.Parameters.AddWithValue("@ResolvedDate", resolvedDate);
            cmdInsert.Parameters.AddWithValue("@CustomerId", customerId);

        }
        catch (SqlException ex)
        {
            Console.Error.WriteLine(ex.Message);
        }
        finally
        {
            if (conn != null)
                conn.Close();
        }
        return ticket;
    }

No data is being update inside the row correlating to the ID being entered.

TicketUtil ticketUtil = new TicketUtil();
            Ticket ticket = ticketUtil.UpdateTicket(6, "Open", 1, 3, "Broken Pencils", "Buy New One", "No", "No", DateTime.Today, new DateTime(1753, 1, 1));

My end goal is to be able to hardcode an update using the code above this line, and then use that with some console prompts to allow for updating of the information. However, without even being able to hardcode the solution, I cant even think about the user input version yet.

Where are you executing the command? You also seem to be updating with the existing value + the new value? Is that really what you want to do? — Alex K.
– Alex K., Commented Apr 18, 2017 at 15:10
@alexK. Sorry, forgot to mention the execution is taking place in the Program(driver/Main) class. I see now that the values are gathered twice, I was trying to input the method used in one of the other ADO.NET threads. — The_fatness
– The_fatness, Commented Apr 18, 2017 at 15:17
I mean where is cmdInsert.ExecuteNonQuery() ? If you don't execute the expected behaviour is no execution after all. — Alex K.
– Alex K., Commented Apr 18, 2017 at 15:19
@AlexK. Still learning, that line + the suggested change from Zohar Peled solved the issue. — The_fatness
– The_fatness, Commented Apr 18, 2017 at 15:24

Zohar Peled · Accepted Answer · 2017-04-18 15:23:14Z

Your update statement seems to be wrong. Try this instead:

UPDATE Ticket 
SET Status = @Status, 
HelpDeskStaffId = @HelpDeskStaffId, 
ProblemDesc = @ProblemDesc, 
Resolution = @Resolution, 
FollowUpRequired = @FollowUpRequired, 
FollowUpComplete = @FollowUpComplete, 
TicketDate = @TicketDate, 
ResolvedDate = @ResolvedDate, 
CustomerId = @CustomerId 
WHERE TicketId=@id;

Update
As Alex K. wrote in his comment, you are not executing the command.

Here is what I think your code should look like:

public Ticket UpdateTicket(int id, string status, int customerId, int helpDeskStaffId, string problemDesc, string resolution, string followUpRequired, string followUpComplete, DateTime ticketDate, DateTime resolvedDate)
{
    var ticket = new Ticket(id, status, customerId, helpDeskStaffId, problemDesc, resolution, followUpRequired, followUpComplete, ticketDate, resolvedDate);

    var connectString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;            
    var sql2 = "UPDATE Ticket SET Status = @Status, HelpDeskStaffId = @HelpDeskStaffId, ProblemDesc = @ProblemDesc, Resolution = @Resolution, FollowUpRequired = @FollowUpRequired, FollowUpComplete = @FollowUpComplete, TicketDate = @TicketDate, ResolvedDate = @ResolvedDate, CustomerId = @CustomerId WHERE TicketId=@id";

    using(var conn = new SqlConnection(connectString))
    {
        using(var cmd = new SqlCommand(sql2, conn))
        {
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@HelpDeskStaffId", helpDeskStaffId);
        cmd.Parameters.AddWithValue("@ProblemDesc", problemDesc);
        cmd.Parameters.AddWithValue("@Resolution", resolution);
        cmd.Parameters.AddWithValue("@FollowUpRequired", followUpRequired);
        cmd.Parameters.AddWithValue("@FollowUpComplete", followUpComplete);
        cmd.Parameters.AddWithValue("@TicketDate", ticketDate);
        cmd.Parameters.AddWithValue("@ResolvedDate", resolvedDate);
        cmd.Parameters.AddWithValue("@CustomerId", customerId);
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            Console.Error.WriteLine(ex.Message);
        }
        }
    }
    return ticket;
}

Thank you and thank AlexK. for taking the time to respond. It is very much appreciated.

Collectives™ on Stack Overflow

Update Multiple Columns in a Row Using ADO.NET

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related