c# - SQL 查询错误

Question

我正在尝试创建一个在 DataGrid 中显示某些信息的报告，但我在使用 SQL 方法时遇到了问题。我显示的字段来自所有不同的表。当我在 MySqlWorkbench 中运行查询时，它可以工作，但是当我尝试在 visual studio 中运行该程序时，出现错误。

private static string SearchSQL
        {
            get
            {
                return @" SELECT fleetchecklist.*,
                                cl.ChecklistNo As CheckListNo, 
                                v.VehicleOwnerID AS VehicleOwnerID, 
                                v.VehicleOwner AS VehicleOwnerName, 
                                v.Reg As VehicleReg,
                                t.TrailerOwnerID AS TrailerOwnerID, 
                                t.TrailerOwner AS TrailerOwnerName, 
                                t.Reg As TrailerReg,
                                vdlc.Description AS VehicleDriverLicenseClassName,
                                tdlc.Description AS TrailerDriverLicenseClassName,
                                m1.MaintenanceNo AS MaintenanceNo,
                                m1.Date AS Date
                         FROM fleetchecklist 
                         LEFT JOIN maintenance m1 ON m1.LinkedID 
                         LEFT JOIN Vehicle v ON m1.LinkedID = v.ID
                         LEFT JOIN Trailer t ON m1.LinkedID = t.ID 
                         left join Employee ve ON v.VehicleOwnerID = ve.ID
                         LEFT JOIN Employee te ON t.TrailerOwnerID = te.ID
                         LEFT JOIN driverlicenseclass vdlc ON ve.DriverLicenseClassID = vdlc.ID
                         LEFT JOIN driverlicenseclass tdlc ON te.DriverLicenseClassID = tdlc.ID
                         LEFT JOIN GeneralSmall gs ON m1.TypeID = gs.ID
                         LEFT JOIN fleetchecklist cl ON m1.ChecklistID = cl.ID
                         WHERE m1.Company_ID = ?compid ";
            }
        }

我得到错误:

Column 'CheckListID' does not belong to table .

当我尝试将 CheckListID 的代码 m1.ChecklistID As CheckListID, 添加到 SQL 中时，出现错误:

Object cannot be cast from DBNull to other types.

错误发生在 CheckListID 行的这个方法中:

protected override void FillObject(DataRow dr)
        {
            ID = Convert.ToInt32(dr["ID"]);
            CheckListID = Convert.ToInt32(dr["CheckListID"]);
            LinkedItemID = Convert.ToInt32(dr["LinkedItemID"]);
            ItemTitle = Convert.ToString(dr["ItemTitle"]);
            Checked = Convert.ToBoolean(dr["Checked"]);
            Defect = Convert.ToBoolean(dr["Defect"]);
            Resolved = Convert.ToBoolean(dr["Resolved"]);
            ResolvedDate = dr["ResolvedDate"].DateTimeOrNull();
            ResolvedBy = dr["ResolvedBy"].IntOrNull();
            if(dr["Comment"] != DBNull.Value)
              Comment = Convert.ToString(dr["Comment"]);
            if (dr["ResolvedComment"] != DBNull.Value)
              ResolvedComment = Convert.ToString(dr["ResolvedComment"]);
        }

Answer 1

您正在从 fleetchecklist 中进行选择，然后在末尾再次加入。此外，LEFT JOIN maintenance m1 ON m1.LinkedID 没有加入任何内容。

SELECT fleetchecklist.*, 
cl.ChecklistNo As CheckListNo, 
v.VehicleOwnerID AS VehicleOwnerID, v.VehicleOwner AS VehicleOwnerName, v.Reg As VehicleReg,
t.TrailerOwnerID AS TrailerOwnerID, t.TrailerOwner AS TrailerOwnerName, t.Reg As TrailerReg, 
vdlc.Description AS VehicleDriverLicenseClassName,
tdlc.Description AS TrailerDriverLicenseClassName, 
m1.MaintenanceNo AS MaintenanceNo, m1.Date AS Date
FROM fleetchecklist cl
LEFT JOIN maintenance m1 ON cl.ID = m1.ChecklistID 
LEFT JOIN Vehicle v ON m1.LinkedID = v.ID
LEFT JOIN Trailer t ON m1.LinkedID = t.ID 
LEFT JOIN Employee ve ON v.VehicleOwnerID = ve.ID
LEFT JOIN Employee te ON t.TrailerOwnerID = te.ID
LEFT JOIN driverlicenseclass vdlc ON ve.DriverLicenseClassID = vdlc.ID
LEFT JOIN driverlicenseclass tdlc ON te.DriverLicenseClassID = tdlc.ID
LEFT JOIN GeneralSmall gs ON m1.TypeID = gs.ID
WHERE m1.Company_ID = ?compid

也没有无意义的别名

SELECT fleetchecklist.*, 
cl.ChecklistNo, 
v.VehicleOwnerID, v.VehicleOwner AS VehicleOwnerName, v.Reg As VehicleReg,
t.TrailerOwnerID, t.TrailerOwner AS TrailerOwnerName, t.Reg As TrailerReg, 
vdlc.Description AS VehicleDriverLicenseClassName,
tdlc.Description AS TrailerDriverLicenseClassName, 
m1.MaintenanceNo, m1.Date
FROM fleetchecklist cl
LEFT JOIN maintenance m1 ON cl.ID = m1.ChecklistID 
LEFT JOIN Vehicle v ON m1.LinkedID = v.ID
LEFT JOIN Trailer t ON m1.LinkedID = t.ID 
LEFT JOIN Employee ve ON v.VehicleOwnerID = ve.ID
LEFT JOIN Employee te ON t.TrailerOwnerID = te.ID
LEFT JOIN driverlicenseclass vdlc ON ve.DriverLicenseClassID = vdlc.ID
LEFT JOIN driverlicenseclass tdlc ON te.DriverLicenseClassID = tdlc.ID
LEFT JOIN GeneralSmall gs ON m1.TypeID = gs.ID
WHERE m1.Company_ID = ?compid