我对我的SQL Server返回的错误感到非常困惑-这是什么引起的?
我正在运行的C#代码是:
List<CalendarEvent> events = new List<CalendarEvent>(); var db = Database.Open("myDatabase"); string username = HttpContext.Current.Request.Cookies.Get("username").Value; var listOfGroups = db.Query("SELECT GroupID FROM Membership WHERE UserID = (SELECT UserID from Users WHERE Username = @0 )", username); foreach(var groupID in listOfGroups) { var result = db.Query( @"SELECT e.event_id, e.title, e.description, e.event_start, e.event_end, e.group_id, e.recurring FROM event e JOIN Membership m ON m.GroupID = e.group_id WHERE e.recurring = 0 AND m.GroupID = @0 AND e.event_start >= @1 AND e.event_end <= @2 UNION ALL SELECT e.event_id, e.title, e.description, DATEADD(week, w.weeks, e.event_start), DATEADD(week, w.weeks, e.event_end), e.group_id, e.recurring FROM event e JOIN Membership m ON m.GroupID = e.group_id CROSS JOIN ( SELECT row_number() OVER (ORDER BY Object_ID) AS weeks FROM SYS.OBJECTS ) AS w WHERE e.recurring = 1 AND m.GroupID = $3 AND e.event_start >= @4 AND e.event_end <= @5", groupID, start, end, groupID, start, end ); foreach(var record in result) { CalendarEvent cevent = new CalendarEvent(); cevent.id = record.event_id; cevent.title = record.title; cevent.description = record.description; cevent.start = record.event_start; cevent.end = record.event_end; cevent.recurring = record.recurring; events.Add(cevent); } } return events; }
错误:
异常详细信息:System.ArgumentException:不存在从对象类型WebMatrix.Data.DynamicRecord到已知的托管提供程序本机类型的映射。
是什么原因造成的,我该如何解决?
我想做的是返回我希望在其一周以及此后52周返回的所有重复发生= 0的事件,以及所有重复发生= = 1的事件。
看来您的问题出在您的groupID输入参数上。
groupID
var listOfGroups = db.Query("SELECT GroupID FROM Membership WHERE UserID = (SELECT UserID from Users WHERE Username = @0 )", username);
您listOfGroups作为WebMatrix.Data.DynamicRecord对象的集合回来了。你似乎只想要int的值,当你groupID在foreach(var groupID in listOfGroups)声明。
listOfGroups
WebMatrix.Data.DynamicRecord
int
foreach(var groupID in listOfGroups)
尝试代替最后一行:
AND e.event_end <= @5", groupID, start, end, groupID, start, end
和:
AND e.event_end <= @5", (int)groupID.GroupID, start, end, (int)groupID.GroupID, start, end
这应该从每个动态查询结果的第一列(也是唯一列)中提取值,该值就是您要获取的int组ID。
编辑: DynamicRecord查询结果的更正值调用语法。