vb.net - 有没有办法在datagridview中显示所有材料名称，即使另一列是空的？

Question

在我的库存程序中，我的数据库中有 3 个表。1. Input Column(id,Material,Quantity) 2. Output Column(id,Material,Quantity) 3. MaterialList (Table) 我可以在哪里输入材料的名称 Column(id,Material)

在我的剩余库存表格中，我想显示所有 MaterialList 的剩余库存，包括没有库存的材料。

例如： From MaterialList From Input and Output Quantity Material list Remaining Stocks item1 150 item2
item3
item4 200

我试过这个，但没有希望。

   conn.Open()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=SOUTHEAST;database=reportingsystem"
        Dim searchquery As String = "select reportingsystem.rawmaterialswarehouseandrawmaterials.Rawmaterials, reportingsystem.rawmaterialswarehouseandrawmaterials.safetystocks, (Sum(reportingsystem.rawmaterialsinput.Quantity) - sum(reportingsystem.rawmaterialsoutput.Quantity)) as 'Remaining Stocks' from reportingsystem.rawmaterialsinput, reportingsystem.rawmaterialsoutput, reportingsystem.rawmaterialswarehouseandrawmaterials where reportingsystem.rawmaterialsinput.RawMaterial = reportingsystem.rawmaterialswarehouseandrawmaterials.Rawmaterials" ' JOIN reportingsystem.rawmaterialsinput.RawMaterial ON reportingsystem.rawmaterialswarehouseandrawmaterials.Rawmaterials = reportingsystem.rawmaterialsinput.RawMaterial ORDER BY reportingsystem.rawmaterialswarehouseandrawmaterials.Rawmaterials"
        'Select rawmaterialsinput.DeliveryDate as 'Delivery Date', rawmaterialswarehouseandrawmaterials.Rawmaterials, (sum(rawmaterialsinput.Quantity) - sum(rawmaterialsoutput.Quantity)) as 'Quantity' , rawmaterialswarehouseandrawmaterials.safetystocks from reportingsystem.rawmaterialsinput, reportingsystem.rawmaterialsoutput, reportingsystem.rawmaterialswarehouseandrawmaterials where rawmaterialsinput.RawMaterial = rawmaterialsoutput.RawMaterial
        '"Select rawmaterialswarehouseandrawmaterials.Rawmaterials as 'Raw Materials', rawmaterialswarehouseandrawmaterials.safetystocks as 'Safety Stock', (sum(rawmaterialsinput.Quantity) - sum(rawmaterialsoutput.Quantity)) as 'Remaining Stocks' where rawmaterialsinput.RawMaterial = rawmaterialswarehouseandrawmaterials.Rawmaterials FROM reportingsystem.rawmaterialswarehouseandrawmaterials, reportingsystem.rawmaterialsinput, reportingsystem.rawmaterialsoutput"
        'Select rawmaterialswarehouseandmaterials.Rawmaterials, (sum(rawmaterialsinput.Quantity) - sum(rawmaterialsoutput.Quantity)) as 'Remaining Stocks', rawmaterialswarehouseandrawmaterials.safetystocks FROM reportingsystem.rawmaterialswarehouseandrawmaterials, reportingsystem.rawmaterialsinput, reportingsystem.rawmaterialsoutput JOIN input ON rawmaterialsinput.RawMaterial = rawmaterialswarehouseandrawmaterials.Rawmaterials
        Dim commander As New MySqlCommand(searchquery, conn)
        Dim adapter As New MySqlDataAdapter(commander)

        monitoringdata.Clear()

        adapter.Fill(monitoringdata)

        MonitoringDGV.DataSource = monitoringdata

        For Each row As DataGridViewRow In MonitoringDGV.Rows
            Dim quantity As Integer = Val(row.Cells(1).Value)
            Dim safetystocks As Integer = Val(row.Cells(2).Value)
            If safetystocks < quantity Then
                row.DefaultCellStyle.BackColor = Color.Red

            ElseIf safetystocks < (quantity * 1.2) Then

                row.DefaultCellStyle.BackColor = Color.Green


            ElseIf safetystocks > (quantity * 1.2) Then
                row.DefaultCellStyle.BackColor = Color.Green

            End If
        Next
        conn.Close()   

我真正需要的是在第 1 列中显示所有材料名称，并在第 2 列中显示剩余库存。

例子：

物料剩余库存 项目 1 1 项目 2 4 项目 3 项目 4 项目
5 18 项目 6 30 项目 7

标签： vb.netdatagridview