Error in SQL query

Question

I'm trying to create a report that displays some information in a DataGrid but I am having a problem with the SQL method. The fields I am displaying are coming from all different tables. When I run the query in MySqlWorkbench it works but when I try run the program in visual studio I get an error.

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 ";
            }
        }

I get the error:

Column 'CheckListID' does not belong to table .

and when I try add code m1.ChecklistID As CheckListID, for the CheckListID into the SQL I get the error:

Object cannot be cast from DBNull to other types.

The error happens in this method on the CheckListID line:

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"]);
        }

check this out http://stackoverflow.com/questions/6098646/object-cannot-be-cast-from-dbnull-to-other-types# — Raj C, Jun 15 '15 at 16:32
did you check the `maintenance` table.. what is the primary key field for that table.. also the error is telling you that you can't cast a `DBNULL` value as an Integer you need to do some checking prior to assinging CheckListID in your FillObject method as well as manually check the table schema of the Maintenane table — MethodMan, Jun 15 '15 at 16:33
`WHERE m1.Company_ID = ?compid ";` this line should be a valid parameter.. does MySql support `@` such as `SqlServer`..? I would also add to the Where clause where `AND m.CompanyID IS NOT NULL` — MethodMan, Jun 15 '15 at 16:35
@MethodMan CheckListID is definitely part of the maintenance table — kw91, Jun 16 '15 at 08:59

score 0 · Answer 1 · answered Aug 21 '15 at 13:42

You are selecting from fleetchecklist then joining it again at the end. Also LEFT JOIN maintenance m1 ON m1.LinkedID isn't joining it on anything.

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

Also without pointless Aliases

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

Error in SQL query

1 Answers1