INSERT语句的选择列表包含的项目少于插入列表。(The select list for the INSERT statement contains fewer items than the ins

INSERT语句的选择列表包含的项目少于插入列表。(The select list for the INSERT statement contains fewer items than the insert list.)

显然，插入列表包含的项目多于所选值的数量，但似乎无法查看情况。 通过观察他们是一样的，除非我错过了什么？

public static DateTime CreateMedicationDispenseLocationHistory( int medicationDispenseID, int locationID, int staffID, DateTime date ) { SqlConnection connection = new SqlConnection( Utilities.DBConnectionString() ); connection.Open(); CreateMedicationDispenseLocationHistory( medicationDispenseID, locationID, staffID, date, connection, null ); connection.Close(); connection.Dispose(); return date; } public static DateTime CreateMedicationDispenseLocationHistory( int medicationDispenseID, int locationID, int staffID, DateTime date, SqlConnection connection, SqlTransaction transaction ) { Locations location = new Locations(); MedicationList medication = new MedicationList(); StringBuilder sqlString = new StringBuilder(); SqlCommand command; sqlString.Append("INSERT INTO [MedicationDispenseLocationHistory] ("); sqlString.Append("MedicationDispenseID, " ); sqlString.Append("LocationID, " ); sqlString.Append("StaffID, " ); sqlString.Append("DateTimeStamp" ); sqlString.Append(") SELECT SCOPE_IDENTITY();"); command = new SqlCommand( sqlString.ToString(), connection ); if( ( transaction != null ) ) command.Transaction = transaction; command.Parameters.Add( "@MedicationDispenseID", SqlDbType.Int ).Value = medication.MedicationDispenseID; command.Parameters.Add( "@LocationID", SqlDbType.Int ).Value = location.LocationID; command.Parameters.Add( "@StaffID", SqlDbType.Int, 500 ).Value = Helper.GetValue( GlobalVariables.loggedInStaff.StaffID ); command.Parameters.Add( "@DateTimeStamp", SqlDbType.DateTime ).Value = Helper.GetDBDateValue(DateTime.Now); //command.Parameters.Add("@stopDate", SqlDbType.DateTime).Value = Helper.GetValue(medicationDispense.StopDate); medication.MedicationDispenseID = Convert.ToInt32( command.ExecuteScalar() ); return date; }

Apparently the insert list contains more items than the the number of selected values but cant seem to see where that is the case. by observation they are the same unless I am missing something?

public static DateTime CreateMedicationDispenseLocationHistory( int medicationDispenseID, int locationID, int staffID, DateTime date ) { SqlConnection connection = new SqlConnection( Utilities.DBConnectionString() ); connection.Open(); CreateMedicationDispenseLocationHistory( medicationDispenseID, locationID, staffID, date, connection, null ); connection.Close(); connection.Dispose(); return date; } public static DateTime CreateMedicationDispenseLocationHistory( int medicationDispenseID, int locationID, int staffID, DateTime date, SqlConnection connection, SqlTransaction transaction ) { Locations location = new Locations(); MedicationList medication = new MedicationList(); StringBuilder sqlString = new StringBuilder(); SqlCommand command; sqlString.Append("INSERT INTO [MedicationDispenseLocationHistory] ("); sqlString.Append("MedicationDispenseID, " ); sqlString.Append("LocationID, " ); sqlString.Append("StaffID, " ); sqlString.Append("DateTimeStamp" ); sqlString.Append(") SELECT SCOPE_IDENTITY();"); command = new SqlCommand( sqlString.ToString(), connection ); if( ( transaction != null ) ) command.Transaction = transaction; command.Parameters.Add( "@MedicationDispenseID", SqlDbType.Int ).Value = medication.MedicationDispenseID; command.Parameters.Add( "@LocationID", SqlDbType.Int ).Value = location.LocationID; command.Parameters.Add( "@StaffID", SqlDbType.Int, 500 ).Value = Helper.GetValue( GlobalVariables.loggedInStaff.StaffID ); command.Parameters.Add( "@DateTimeStamp", SqlDbType.DateTime ).Value = Helper.GetDBDateValue(DateTime.Now); //command.Parameters.Add("@stopDate", SqlDbType.DateTime).Value = Helper.GetValue(medicationDispense.StopDate); medication.MedicationDispenseID = Convert.ToInt32( command.ExecuteScalar() ); return date; }

最满意答案

在INSERT INTO语句的末尾添加分号

sqlString.Append("); SELECT SCOPE_IDENTITY();");

否则，以下SELECT SCOPE_IDENTITY（）将被解释为INSERT的SELECT值列表

INSERT INTO ....... SELECT

完成答案（给予以下评论作者的信任） 不要忘记为参数添加值占位符

sqlString.Append(") VALUES (@MedicationDispenseID,@LocationID,@StaffID,@DateTimeStamp);"); sqlString.Append("SELECT SCOPE_IDENTITY();");

Add a semicolon at the end of the INSERT INTO statement

sqlString.Append("); SELECT SCOPE_IDENTITY();");

Otherwise the following SELECT SCOPE_IDENTITY() will be interpreted as the SELECT list of values for the INSERT

INSERT INTO ....... SELECT

To complete the answer (giving credit to the authors of the comments below) Do not forget to add the values placeholders for your parameters

sqlString.Append(") VALUES (@MedicationDispenseID,@LocationID,@StaffID,@DateTimeStamp);"); sqlString.Append("SELECT SCOPE_IDENTITY();");