C＃更新表使用ASP.NET SqlCommand.Parameters [英] C# Update Table using SqlCommand.Parameters ASP.NET

问题描述

可能重复：结果
的使用SqlCommand.Parameters

我试图更新的SQL Server使用的SqlCommand表，我觉得这是我的T-SQL语法错误，但这里是我迄今为止：

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 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();
    }

和方法的签名：

  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?

推荐答案

目前的ExecuteNonQuery添加一个破发点，检查你的SqlCommand对象，采取看看同的SqlCommand的参数，并确保TicketId被设置为你所期望它，因为你的更新是由ticketId驱动，确保它被设置正确，或者尝试直接SQL服务器上运行的TSQL等价，我的猜测是你ticketId变量没有被设置，代码乍看上去很好。

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.

这篇关于C＃更新表使用ASP.NET SqlCommand.Parameters的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！