显然,插入列表包含的项目多于所选值的数量,但似乎无法查看情况。 通过观察他们是一样的,除非我错过了什么?
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();");更多推荐
发布评论