mysql - 如何修复DataGridView错误: Is it possible that my null field result in MySQL query is the cause?

Question

当我遇到这些错误时，我试图使用 datagridview 显示我的查询结果...

DataGridView 中发生以下异常:

System.ArgumentException: Parameter is not valid.
at System.Drawing.IMage.FromStream(Stream stream, BooleanuseEmbeddedColorManagement,     Boolean validateImageData)
at System.Drawing.ImageConverter.ConvertFrom(ITypeDescriptorContext context,     CultureInfo culture, Object value)
at System.Windows.Forms.Formatter.FormatObjectInternal(Objectvalue, Type targetTyep,     TypeConverter sourceConverter, TypeConverter targetConverter, String formatString,     IFormatProvider formatInfo, Object formattedNullValue)
at System.Windows.Forms.Formatter.FormatObject(Object value, Type targetType,     TypeConverter sourceConverter, TypeConverter targetConverter, String formatString,     IFormatProvider formatInfo, Object formattedNullValue, Object dataSourceNullValue)
at System.Window.Forms.DataGridViewCell.GetFormattedValue(Object value, Int32 rowIndex,     DataGridViewCellStyle& cellStyle, TypeConverter valueTypeConverter,     TypeConverter formattedValueTypeConverter, DataGridViewDataErrorContexts context)

To replace this default dialog please handle the DataError event. 

嗯，当我使用 datagridview 时，我确实收到了类似 8 个这样的对话框，但是，当我使用断点运行逐行检查时，我不会收到任何错误提示!我认为是这个datagridview。我已经尝试删除 datagridview 并只是放置一个新的，但存在同样的问题。

供您引用，这是我将数据放入 datagridview 时使用的代码:

  Public Sub fillDGVBulk()
    Try

        Dim bulkCmd As New MySqlCommand("select * from " _
                                    + "( " _
                                    + "  select controlno, bulk.catid, bulk.bcode, " _
                                    + "  ( " _
                                    + "    bulk.itemqty " _
                                    + "  ) as stock, " _
                                    + "  case when resv.qty is null then " _
                                    + "  '0' else resv.qty end as booked, " _
                                    + "  case when " _
                                    + "  ( " _
                                    + "    bulk.itemqty-sumqty " _
                                    + "  ) is null then " _
                                    + "  bulk.itemqty else (bulk.itemqty-sumqty) end as available, bulk.itemname, bulk.deptcode, bulk.loccode, resv.resdate, resv.timestart, resv.timeend " _
                                    + "  from tbl_items_bulk bulk " _
                                    + "  left join " _
                                    + "  ( " _
                                    + "    select rs.controlno, rs.bcode, rs.qty, sum(rs.qty) as sumqty, rs.resdate, rs.timestart, rs.timeend " _
                                    + "    from tbl_reservations rs " _
                                    + "    where retdate is null " _
                                    + "    and restype='1' " _
                                    + "    and resdate = @date " _
                                    + "    and not " _
                                    + "    ( " _
                                    + "      (timestart between @tStart and @tEnd) or " _
                                    + "      (timeend between @tStart and @tEnd) or " _
                                    + "      (timestart <= @tStart and timeend >= @tEnd) " _
                                    + "    ) " _
                                    + "    or rs.bcode is null " _
                                    + "    and (claimdate is null and retdate is null) " _
                                    + "    group by bcode " _
                                    + "  ) resv " _
                                    + "  on bulk.bcode=resv.bcode " _
                                    + "  where ((bulk.itemqty-sumqty) >= 1) or (bulk.itemqty-sumqty) is null " _
                                    + "  order by resdate, timestart " _
                                    + ") rvs " _
                                    + "where rvs.catid=@catid " _
                                    + "order by controlno", conn)

        With bulkCmd
            .Parameters.AddWithValue("@catid", cbo_catbulk.SelectedValue.ToString)
            .Parameters.AddWithValue("@date", dtp_datebulk.Value.ToString)
            .Parameters.AddWithValue("@tStart", cbo_startbulk.SelectedValue.ToString)
            .Parameters.AddWithValue("@tEnd", cbo_endbulk.SelectedValue.ToString)
        End With

        Dim DAdapter2 As New MySqlDataAdapter()

        DAdapter2.SelectCommand = bulkCmd

        If conn.State = ConnectionState.Open Then
            conn.Close()
            conn.Open()
        Else
            conn.Open()
        End If

        Dim table2 As New DataTable
        DAdapter2.Fill(table2)

        DataGridView2.DataSource = table2

        conn.Close()

        'controlno, catid, bcode, stock, booked, available, resdate, timestart, timeend

        With DataGridView2
            .Columns(0).Visible = False
            .Columns(1).HeaderText = "Category ID"
            .Columns(2).HeaderText = "Barcode"
            .Columns(3).HeaderText = "Stock"
            .Columns(4).HeaderText = "Booked"
            .Columns(5).HeaderText = "Available"
            ' .Columns(5).DefaultCellStyle.BackColor = Color.LightGreen
            .Columns(6).HeaderText = "Item"
            .Columns(7).HeaderText = "Department"
            .Columns(8).HeaderText = "Storage Area"
            .Columns(9).Visible = False
            .Columns(10).Visible = False
            .Columns(11).Visible = False
        End With
    Catch ex As Exception
        MsgBox(ex.Message.ToString + " Error No: " + Err.Number.ToString)
    End Try
End Sub

有人可以帮我吗？提前致谢! :)

Answer 1

我在这里尝试一个疯狂的猜测..

在运行时，虽然此代码将 table2 设置为 DataGridView 的数据源，

DataGridView2.DataSource = table2

这些代码立即运行...

With DataGridView2
            .Columns(0).Visible = False
            .Columns(1).HeaderText = "Category ID"
            .Columns(2).HeaderText = "Barcode"
            .Columns(3).HeaderText = "Stock"
            .Columns(4).HeaderText = "Booked"
            .Columns(5).HeaderText = "Available"
            ' .Columns(5).DefaultCellStyle.BackColor = Color.LightGreen
            .Columns(6).HeaderText = "Item"
            .Columns(7).HeaderText = "Department"
            .Columns(8).HeaderText = "Storage Area"
            .Columns(9).Visible = False
            .Columns(10).Visible = False
            .Columns(11).Visible = False
        End With

这将重命名/隐藏列。

而是重命名 DataGridView2 的列标题文本，我认为这会导致运行时错误，因为它还没有结果集的列。

为什么不在 SELECT 语句上使用 AS 关键字，以便可以重命名列名查询的结果集。

在您的初始选择语句中尝试此操作..

 Dim bulkCmd As New MySqlCommand("select catid AS `Category ID`, bcode AS `Barcode`, stock AS Stock, booked AS `Booked`, available AS `Available`, resdate AS `Item`, timestart AS `Department`, timeend AS `Storage Area` from " _