C# 使用 SqlCommand.Parameters 更新表 ASP.NET

C# Update Table using SqlCommand.Parameters ASP.NET

提问by skylerl

Possible Duplicate:
C# Update Table using SqlCommand.Parameters

可能的重复：
使用 SqlCommand.Parameters 的 C# 更新表

I'm trying to update an SQL Server table using SqlCommand, I think it's a syntax error with my T-SQL, but here is what I have so far:

我正在尝试使用 SqlCommand 更新 SQL Server 表，我认为这是我的 T-SQL 的语法错误，但这是我目前所拥有的：

SqlCommand sqlCmd = new SqlCommand(
   "UPDATE yak_tickets 
    SET email = @emailParam, subject = @subjectParam, text = @textParam, 
        statusid = @statusIDParam, ticketClass = @ticketClassParam 
    WHERE id = @ticketIDParam", sqlConn);

The parameters are working as they should, however, the table never gets updated when I run the code. Any help would be appreciated =)

参数正常工作，但是，当我运行代码时，表永远不会更新。任何帮助将不胜感激 =)

Here is the rest of the code:

下面是代码的其余部分：

    #region Parameters
    /* Parameters */
    sqlCmd.Parameters.Add("@ticketIDParam", SqlDbType.BigInt);
    sqlCmd.Parameters["@ticketIDParam"].Value = ticketID;

    sqlCmd.Parameters.Add("@emailParam", SqlDbType.NVarChar);
    sqlCmd.Parameters["@emailParam"].Value = ticketToBeSubmitted.getEmail();

    sqlCmd.Parameters.Add("@subjectParam", SqlDbType.NVarChar);
    sqlCmd.Parameters["@subjectParam"].Value = ticketToBeSubmitted.getSubject();

    sqlCmd.Parameters.Add("@textParam", SqlDbType.Text);
    sqlCmd.Parameters["@textParam"].Value = ticketToBeSubmitted.getTicketContent();

    sqlCmd.Parameters.Add("@statusIDParam", SqlDbType.NVarChar);
    sqlCmd.Parameters["@statusIDParam"].Value = ticketToBeSubmitted.getStatus();

    sqlCmd.Parameters.Add("@ticketClassParam", SqlDbType.NVarChar);
    sqlCmd.Parameters["@ticketClassParam"].Value = ticketToBeSubmitted.getTicketClass();
    #endregion

    #region Try/Catch/Finally
    /* Try/Catch/Finally */

    try
    {
        sqlConn.Open();
        sqlCmd.ExecuteNonQuery();
    }
    catch (SqlException sqlEx)
    {
        sqlErrorLabel.Text = sqlEx.ToString();
        sqlErrorLabel.ForeColor = System.Drawing.Color.Red;
    }
    finally
    {
        sqlConn.Close();
    }

And the method's signature:

以及方法的签名：

  public static void updateTicketInDatabase(Ticket ticketToBeSubmitted, Label sqlErrorLabel, int ticketID)

I've run this through a profiler, and what it does, is the following.

我已经通过分析器运行了它，它的作用如下。

Page Loads ->
Audit Login: -- Sets a bunch of stuff (irrelevant)
SQL:BatchStarting -- SELECT * from yak_tickets
SQL:BatchCompleted -- SELECT * from yak_tickets
Audit Logout


Button Click Event ->
RPC:Completed: -- exec sp_reset_connection
Audit Login: -- Sets some more stuff
SQL:BatchStarting -- SELECT * from yak_tickets
SQL:BatchCompleted -- SELECT * from yak_tickets

Here is the code behind file for the form.

这是表单的文件隐藏代码。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using YakStudios_Support.Includes;

namespace YakStudios_Support.ys_admin
{
    public partial class UpdateTicket : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            int ticketID = Convert.ToInt32(Request.QueryString["ticketID"]); // Grabs the ?ticketid number from the URL
            Ticket ticketBeingUpdated = TicketDatabase.selectTicketFromDatabase(sqlErrorLabel, 1); // Creates a new Ticket object to be used by the form to populate the text boxes

            /* Form Field Population */
            // Email
            emailTxt.Text = ticketBeingUpdated.getEmail();

            // Date Submitted
            dateSubText.Text = ticketBeingUpdated.getDateSubmitted().ToString();

            // Ticket Class
            classDropDown.SelectedValue = ticketBeingUpdated.getTicketClass();

            // Ticket Status
            statusDrop.SelectedValue = ticketBeingUpdated.getStatus();

            // Subject
            subjectTxt.Text = ticketBeingUpdated.getSubject();

            // Text
            textTxt.Text = ticketBeingUpdated.getTicketContent();
        }

        protected void editBtn_Click(object sender, EventArgs e)
        {
            emailTxt.Enabled = true;
            dateSubText.Enabled = true;
            classDropDown.Enabled = true;
            statusDrop.Enabled = true;
            subjectTxt.Enabled = true;
            textTxt.Enabled = true;
        }

        protected void submitBtn_Click(object sender, EventArgs e)
        {
            int ticketID = Convert.ToInt32(Request.QueryString["ticketID"]); // Grabs the ?ticketid number from the URL
            DateTime convertedDate = Convert.ToDateTime(dateSubText.Text);
            Ticket ticketUpdated = new Ticket(emailTxt.Text, convertedDate, subjectTxt.Text, textTxt.Text, statusDrop.SelectedValue, classDropDown.SelectedValue);
            //Ticket ticketUpdated = new Ticket(emailTxt.Text, subjectTxt.Text, textTxt.Text, classDropDown.SelectedValue);
            Response.Write(TicketDatabase.updateTicketInDatabase(ticketUpdated, sqlErrorLabel, 1));
            //Response.Redirect("ticketqueue.aspx");
        }
    }
}

What it seems to me like it's doing, is it's running my select method again, which is what it is supposed to do when the page loads. Is this being caused by the button refreshing the page?

在我看来它正在做的事情是它再次运行我的 select 方法，这就是它在页面加载时应该做的事情。这是由按钮刷新页面引起的吗？

采纳答案by Ta01

Add a break point at ExecuteNonQuery, inspect your sqlCommand object , take a look at the parameters with the sqlcommand and make sure TicketId is being set to what you expect it to, since your update is driven by the ticketId, ensure it is being set correctly, or try running the TSQL equivalent directly on SQL server, my guess is that your ticketId variable isn't being set, code looks fine at first glance.

在 ExecuteNonQuery 处添加一个断点，检查您的 sqlCommand 对象，使用 sqlcommand 查看参数并确保 TicketId 设置为您期望的值，因为您的更新是由 ticketId 驱动的，请确保正确设置，或者尝试直接在 SQL 服务器上运行 TSQL 等效项，我的猜测是您的 ticketId 变量未设置，代码乍一看很好。