必须声明标量变量@resident

本文关键字：变量 @resident 标量 声明 | 更新日期: 2023-09-27 18:27:34

我已经声明了变量，它正在返回相应的值，但我不明白为什么我仍然会收到这个错误。

  sqlString.Append( " SELECT  Medication.Description + ' - ' + Medication.Strength + ' - ' + Medication.MedicationCode as DrugName, Resident.Title + ' ' + Resident.firstName + ' ' + Resident.Surname AS Resident, MedicationDispenseTimeslot.MedicationDosageBarcode, 0 AS checkBox " );
             sqlString.Append( " FROM Medication INNER JOIN MedicationDispense ON Medication.MedicationID = MedicationDispense.MedicationID INNER JOIN Resident ON MedicationDispense.ResidentID = Resident.ResidentID INNER JOIN MedicationDispenseStatus ON MedicationDispense.MedicationDispenseStatusID = MedicationDispenseStatus.MedicationDispenseStatusID INNER JOIN MedicationDispenseTimeslot ON MedicationDispense.MedicationDispenseID = MedicationDispenseTimeslot.MedicationDispenseID WHERE     (MedicationDispenseStatus.MedicationDispenseStatusID = 2) and  MedicationDispense.MedicationDispenseID in (select MedicationDispenseID from dbo.MedicationDispenseHistory where DATEDIFF(d, 0, DateTimeStamp) =DATEDIFF(d, 0, GETDATE()) and MedicationDispenseStatusID=2) AND MedicationDispense.ResidentID = @residentID " );
             sqlString.Append( " AND   (MedicationDispense.MedicationDispenseStatusID > 1) AND ");
             sqlString.Append( "    1 = (CASE WHEN MedicationDispense.StopDate IS NULL " );
             sqlString.Append( "                 THEN (CASE WHEN MedicationDispense.StartDate <= @periodEndDate THEN 1 ELSE 0 END) " );
             sqlString.Append( "            ELSE " );
             sqlString.Append( "                CASE WHEN (MedicationDispense.StartDate between @periodStartDate and @periodEndDate) " );
             sqlString.Append( "                                OR " );
             sqlString.Append( "                          (MedicationDispense.StopDate between @periodStartDate and @periodEndDate) " );
             sqlString.Append( "                                OR " );
             sqlString.Append( "                          (MedicationDispense.StartDate < @periodStartDate and MedicationDispense.StopDate > @periodEndDate) " );
             sqlString.Append( "    GROUP BY Medication.Description + ' - ' + Medication.Strength + ' - ' + Medication.MedicationCode, Resident.Title + ' ' + Resident.firstName + ' ' + Resident.Surname , MedicationDispenseTimeslot.MedicationDosageBarcode " );
             command = new SqlCommand(sqlString.ToString(), connection);
             command.Parameters.Add( "@residentID", SqlDbType.Int ).Value = residentID.ToString();
             command.Parameters.Add("@periodStartDate", SqlDbType.DateTime).Value = Helper.GetValue(periodStartDate);
             command.Parameters.Add("@periodEndDate", SqlDbType.DateTime).Value = Helper.GetValue(periodEndDate);

编辑

  command = new SqlCommand( sqlString.ToString(), connection );
     SqlDataAdapter dataAdapter = new SqlDataAdapter( command );
     dataAdapter.Fill( ds, "dataTable" );
     return ds;

必须声明标量变量@resident

如果您在串联字符串中编写sql，则会出现此问题，您可以将静态查询编写为串联字符串，因为它将转到sql并直接执行，但在这里，我可以理解的是，如果您看到sqlString.ToString（）的输出，它包含一些变量，如@residentID、@periodEndDate等，当它被执行时，它在contextv中不可用，也没有被定义为参数，这就是为什么你会得到错误必须声明标量变量@resident，即使你将其作为参数传递，但我认为在连接字符串的情况下，你不能传递参数，。

因此，更好地使用存储过程，这是为了一次执行动态查询和多个查询。在存储的参数中，可以定义参数并为其传递值。

检查以了解存储过程如何使用c#工作

如何使用C#执行存储过程

这是一个看不到树的例子：

您构建的SqlCommand命令很好，但从未使用过它。相反，您将创建一个您正在使用的新SqlCommand命令。参数不会添加到此SqlCommand对象中。

相关文章：