C#连接SQL Server查询与更新的参数错误排查求助

遇到的错误 #

1. 进入工单编辑页面时触发：

The parameterized query '(@ID nvarchar(4000))SELECT TOP (1000) [ID],[tNumber],[tStatus], ' expects the parameter '@ID', which was not supplied.

2. 修改代码后出现的新错误：

'The variable name '@tDateUpdated' has already been declared. Variable names must be unique within a query batch or stored procedure. Must declare the scalar variable "@tNumnber".'

逐个错误排查与修复 #

第一个错误（查询页面参数缺失） #

看初始查询代码，问题出在ID参数的获取和传递环节：

1. 参数未正确传递：检查前端跳转至编辑页时，URL是否携带了?ID=xxx参数，若缺失则Request.Query["ID"]会返回空值。
2. 空值未校验：未判断ID是否为空就传入SQL，导致参数未有效供给。
3. 类型不匹配：数据库中ID是数值类型，但代码直接将字符串传入参数，可能引发隐式类型转换问题。

修复后的查询代码片段：

String ID = Request.Query["ID"];
// 新增空值与格式校验
if(string.IsNullOrEmpty(ID) || !int.TryParse(ID, out int ticketId))
{
    errorMessage = "无效或缺失的ID参数";
    return;
}

try
{
    String connectionString = "Data Source=SQLTableDataSource;Integrated Security=True;TrustServerCertificate=True;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        String sql = "SELECT TOP (1000) [ID],[tNumber],[tStatus], [tApplication], [tType], [tDateLogged], ISNULL ([tDateUpdated], ' ') [tDateUpdated], ISNULL ([tDateClosed],' ') [tDateClosed], [tDateLastChecked], [tUserLogged], [tCurrentlyWith], [tLoggedOrAdHocTTL] FROM [JB_DB].[dbo].[Tble_COF_IT_UK_Tickets] WHERE ID=@ID;";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            // 明确传入int类型参数，避免类型不匹配
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ticketId;
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    ticketInfo.ID = reader.GetInt32(0).ToString();
                    ticketInfo.tNumber = reader.GetString(1);
                    ticketInfo.tStatus = reader.GetString(2);
                    ticketInfo.tApplication = reader.GetString(3);
                    ticketInfo.tType = reader.GetString(4);
                    ticketInfo.tDateLogged = reader.GetString(5);
                    ticketInfo.tDateUpdated = reader.GetString(6);
                    ticketInfo.tDateClosed = reader.GetString(7);
                    ticketInfo.tDateLastChecked = reader.GetString(8);
                    ticketInfo.tUserLogged = reader.GetString(9);
                    ticketInfo.tCurrentlyWith = reader.GetString(10);
                    ticketInfo.tLoggedOrAdHocTTL = reader.GetString(11);
                }
            }
        }
    }
}
catch (Exception ex)
{
    errorMessage = ex.Message;
}

第二个错误（OnPost更新方法参数问题） #

OnPost代码存在多处拼写错误与冗余操作：

1. 参数拼写不一致：SQL语句中用@tNumnber，但代码中添加的参数是@tNumbar，正确应为@tNumber。
2. 重复添加参数：连续两次调用command.Parameters.AddWithValue("@tDateUpdated", ...)，导致SQL报错变量重复声明。
3. 未定义的参数：SQL语句中包含@tDateLastUpdated，但代码未添加该参数。
4. 冗余赋值：连续两次给ticketInfo.tDateUpdated赋值，属于无效代码。

修复后的OnPost代码：

public void OnPost()
{
    ticketInfo.ID = Request.Form["ID"];
    ticketInfo.tNumber = Request.Form["tNumber"];
    ticketInfo.tStatus = Request.Form["tStatus"];
    ticketInfo.tApplication = Request.Form["tApplication"];
    ticketInfo.tType = Request.Form["tType"];
    ticketInfo.tDateLogged = Request.Form["tDateLogged"];
    ticketInfo.tDateUpdated = Request.Form["tDateUpdated"];
    ticketInfo.tDateClosed = Request.Form["tDateClosed"];
    ticketInfo.tCurrentlyWith = Request.Form["tCurrentlyWith"];
    ticketInfo.tLoggedOrAdHocTTL = Request.Form["tLoggedOrAdHocTTL"];
    // 根据业务需求补充tDateLastUpdated的值，示例设为当前时间
    ticketInfo.tDateLastUpdated = DateTime.Now.ToString();

    try
    {
        String connectionString = @"Data Source=UK-TF-MUK-SQL01\DEV;Initial Catalog=JB_DB;Integrated Security=True;TrustServerCertificate=True;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            String Sql = @"UPDATE Tble_COF_IT_UK_Tickets 
                          SET tNumber=@tNumber, tStatus=@tStatus, tApplication=@tApplication, 
                              tType=@tType, tDateLogged=@tDateLogged, tDateUpdated=@tDateUpdated, 
                              tDateClosed=@tDateClosed, tDateLastUpdated=@tDateLastUpdated, 
                              tCurrentlyWith=@tCurrentlyWith, tLoggedOrAdHocTTL=@tLoggedOrAdHocTTL 
                          WHERE ID=@ID;";

            using (SqlCommand command = new SqlCommand(Sql, connection))
            {
                command.Parameters.AddWithValue("@tNumber", ticketInfo.tNumber);
                command.Parameters.AddWithValue("@tStatus", ticketInfo.tStatus);
                command.Parameters.AddWithValue("@tApplication", ticketInfo.tApplication);
                command.Parameters.AddWithValue("@tType", ticketInfo.tType);
                command.Parameters.AddWithValue("@tDateLogged", ticketInfo.tDateLogged);
                command.Parameters.AddWithValue("@tDateUpdated", ticketInfo.tDateUpdated);
                command.Parameters.AddWithValue("@tDateClosed", ticketInfo.tDateClosed);
                command.Parameters.AddWithValue("@tDateLastUpdated", ticketInfo.tDateLastUpdated);
                command.Parameters.AddWithValue("@tCurrentlyWith", ticketInfo.tCurrentlyWith);
                command.Parameters.AddWithValue("@tLoggedOrAdHocTTL", ticketInfo.tLoggedOrAdHocTTL);
                
                // 校验ID有效性
                if(!int.TryParse(ticketInfo.ID, out int ticketId))
                {
                    errorMessage = "无效的ID格式";
                    return;
                }
                command.Parameters.Add("@ID", SqlDbType.Int).Value = ticketId;

                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        errorMessage = ex.Message;
        return;
    }
}

额外优化建议 #

1. 避免硬编码连接字符串，将其移至appsettings.json配置文件中。
2. 使用command.Parameters.Add()替代AddWithValue，明确指定参数类型，减少隐式转换风险。
3. 对所有用户输入参数做合法性校验，防止空值、非法格式数据进入数据库。

内容的提问来源于stack exchange，提问作者IT_Programmer