C# Access not recognize parameters in a query

问题:

I'm trying to write an sql query in C# to Access. the query works fine in the Access software but when I tried to run it through C# with parameters it throws me exception that the parameters are missing. it's like it ignores the parameters I'm giving. this is my method:

public DataTable GetRoomStatusByDate(Room RoomItem, TimeRange TimeItem, WeekDay DayItem, DateTime DateItem)
        {
            string cmdStr = "SELECT Subject, AdditionOrCancellation, Description " + 
                            "FROM (SELECT * " + 
                                "FROM TimeTables TT1 LEFT OUTER JOIN SpecialEvents SE1 " + 
                                "ON TT1.RoomNumber = SE1.RoomNumber AND TT1.StructureNumber = SE1.StructureNumber AND " +
                                "TT1.DayNumber = SE1.DayNumber AND TT1.HourNumber = SE1.HourNumber " + 
                                "UNION " + 
                                "SELECT * " + 
                                "FROM TimeTables TT2 RIGHT OUTER JOIN SpecialEvents SE2 " + 
                                "ON TT2.RoomNumber = SE2.RoomNumber AND TT2.StructureNumber = SE2.StructureNumber " + 
                                "AND TT2.DayNumber = SE2.DayNumber AND TT2.HourNumber = SE2.HourNumber) " +
                            "WHERE (TT1.RoomNumber = @TTRoomNumber OR SE1.RoomNumber = @SERoomNumber) AND (TT1.StructureNumber = @TTStructureNumber OR SE1.StructureNumber = @SEStructureNumber) " +
                            "AND(TT1.HourNumber = @TTHourNumber OR SE1.HourNumber = @SEHourNumber) AND (TT1.DayNumber = @TTDayNumber OR SE1.DayNumber = @@SEDayNumber) " +
                            "AND (SE1.EventDate = @SEEventDate OR SE1.EventDate IS NULL)";
            DataSet ds = new DataSet();
            using (OleDbCommand command = new OleDbCommand(cmdStr))
            {
                command.Parameters.AddWithValue("@TTRoomNumber", RoomItem.Number);
                command.Parameters.AddWithValue("@SERoomNumber", RoomItem.Number);
                command.Parameters.AddWithValue("@TTStructureNumber", RoomItem.Structure);
                command.Parameters.AddWithValue("@SEStructureNumber", RoomItem.Structure);
                command.Parameters.AddWithValue("@TTDayNumber", TimeItem.Number);
                command.Parameters.AddWithValue("@SEDayNumber", TimeItem.Number);
                command.Parameters.AddWithValue("@TTDayNumber", DayItem.Number);
                command.Parameters.AddWithValue("@SEDayNumber", DayItem.Number);
                command.Parameters.AddWithValue("@SEEventDate", DateItem);
                ds = GetMultipleQuery(command);
            }
            DataTable dt = new DataTable();
            try
            {
                dt = ds.Tables[0];
            }
            catch
            { }
            return dt;
        }

I also want to mention I use OLEDB and my connection is surly fine because I have many methods with query and parameters and their all works fine with no problems. this is my only method with a query that doesn't work.

回答1:

OleDb uses positional parameters, not named parameters. Replace the parameter names by ? in SQL. You can leave the parameter name unchanged in AddWithValue, since it is ignored.

...
"WHERE (TT1.RoomNumber = ? OR SE1.RoomNumber = ?) AND (TT1.StructureNumber = ? OR SE1.StructureNumber = ?) " +
"AND(TT1.HourNumber = ? OR SE1.HourNumber = ?) AND (TT1.DayNumber = ? OR SE1.DayNumber = ?) " +
"AND (SE1.EventDate = ? OR SE1.EventDate IS NULL)";

Make sure you add the parameters in the same order they appear in the SQL text.

Also the value passed to AddWithValue must have the right type. A numeric or date string is not automatically converted to the appropriate numeric or DateTime type.