c#必须声明标量变量"@SomeThing"在插入查询

本文关键字：quot @SomeThing 插入 查询 标量 变量 声明 | 更新日期: 2023-09-27 18:14:43

我正在研究。net c#，我想在表中插入一些数据。

我在Visual Studio中编写查询，而不使用存储过程。

这是我的查询

SqlCommand cmd2 = new SqlCommand("insert into Device_Events (Device_ID, Event_ID, Occurrence_Time, Recovery_Time) values (@Device_ID , @Event_ID, @Occurrence_Time, @Recovery_Time)", con);

这是我用来传递值的c#代码

// For Events
string formatString = "yyMMddHHmmss";
DateTime Occurrence_Time, Recovery_Time;
string strOccurrence = Meter_data.Substring(161, 12);
string strRecovery = Meter_data.Substring(173, 12);
cmd2.Parameters.AddWithValue("@Device_ID", device_Id);
cmd2.Parameters.AddWithValue("@Event_ID", event_Id);
if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
{
    if (DateTime.TryParseExact(strRecovery, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Recovery_Time))
    {
        cmd2.Parameters.AddWithValue("@Occurrence_Time", SqlDbType.DateTime).Value = Occurrence_Time;
        cmd2.Parameters.AddWithValue("@Recovery_Time", SqlDbType.DateTime).Value = Recovery_Time;
    }
}
int Device_Events_rows_executed = cmd2.ExecuteNonQuery();
Console.WriteLine("Rows Executed: '{0}'", Device_Events_rows_executed);

它没有进入if部分，我得到一个名为Must declare the scalar variable "@Occurrence_Time"的异常

我也读了这个链接，但无法找到任何帮助

<<p> 更新代码/strong>

经过建议，我添加了以下代码

            cmd2.Parameters.Add("@Device_ID", SqlDbType.VarChar, 50).Value = device_Id;
            cmd2.Parameters.Add("@Event_ID", SqlDbType.VarChar, 50).Value = event_Id;
            cmd2.Parameters.Add("@Occurrence_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;
            cmd2.Parameters.Add("@Recovery_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;
            string formatString = "yyMMddHHmmss";
            DateTime Occurrence_Time, Recovery_Time;
            string strOccurrence = Meter_data.Substring(161, 12);
            string strRecovery = Meter_data.Substring(173, 12);
            if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
            {
                cmd2.Parameters["@Occurrence_Time"].Value = Occurrence_Time;
            }
            if (DateTime.TryParseExact(strRecovery, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Recovery_Time))
            {
                cmd2.Parameters["@Recovery_Time"].Value = Recovery_Time;
            }

虽然它没有显示错误或异常但它仍然没有进入if条件

如有任何帮助，不胜感激

c#必须声明标量变量"@SomeThing"在插入查询

您总是需要声明所有参数，但您可以传递null或默认值，以防丢失日期:

if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
{               
    cmd2.Parameters.AddWithValue("@Occurrence_Time", SqlDbType.DateTime).Value = Occurrence_Time;                
}
else 
{
    cmd2.Parameters.AddWithValue("@Occurrence_Time", SqlDbType.DateTime).Value = null;
}

如前所述，由于您的条件流，您将在某些代码路径中尝试执行cmd2，而不向命令添加参数。

参数必须添加到命令中，即使它们没有值。我建议在开始时设置所有参数(使用Add()而不是AddWithValue()):

cmd2.Parameters.Add("@Device_ID", SqlDbType.VarChar, 50).Value = device_Id;
cmd2.Parameters.Add("@Event_ID", SqlDbType.VarChar, 50).Value = event_Id;
cmd2.Parameters.Add("@Occurrence_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;
cmd2.Parameters.Add("@Recovery_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;

然后，如果字符串被正确解析为日期，则更新参数的值:

if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
{
    cmd2.Parameters["@Occurrence_Time"].Value = Occurrence_Time;
}
if (DateTime.TryParseExact(strRecovery, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Recovery_Time))
{
    cmd2.Parameters["@Recovery_Time"].Value = Recovery_Time;
}

编辑

你似乎很有信心，数据将永远是正确的，所以你不妨只用ParseExact代替TryParseExact:

var occuranceDate = DateTime.ParseExact(Meter_data.Substring(161, 12), "yyMMddHHmmss", CultureInfo.InvariantCulture);
var recoveryDate = DateTime.ParseExact(Meter_data.Substring(173, 12), "yyMMddHHmmss", CultureInfo.InvariantCulture);
cmd2.Parameters.Add("@Device_ID", SqlDbType.VarChar, 50).Value = device_Id;
cmd2.Parameters.Add("@Event_ID", SqlDbType.VarChar, 50).Value = event_Id;
cmd2.Parameters.Add("@Occurrence_Time", SqlDbType.DateTime, 50).Value = occuranceDate'
cmd2.Parameters.Add("@Recovery_Time", SqlDbType.DateTime, 50).Value = recoveryDate;
int Device_Events_rows_executed = cmd2.ExecuteNonQuery();
Console.WriteLine("Rows Executed: '{0}'", Device_Events_rows_executed);
相关文章：