vb.net 数据绑定 DataGridView 中的 ComboBox 列

ComboBox Column within databound DataGridView

提问by PowerMan2015

Im back again with another DataGidView question

我又回来了另一个 DataGidView 问题

I am creating an application that allows the user to create a ticket for products purchased

我正在创建一个应用程序，允许用户为购买的产品创建票

This form consists of a DataGidViewwhich allows the user to record the product details along with price and quantity information.

此表单包含一个 DataGidView，允许用户记录产品详细信息以及价格和数量信息。

Currently the DataGidView is data bound and data is saved using a data adaptor and command builder at runtime

目前 DataGidView 是数据绑定的，数据是在运行时使用数据适配器和命令生成器保存的

The columns are created automatically and then i set properties such as formatting options as follows

列是自动创建的，然后我如下设置格式选项等属性

Private Sub SetTicketList()

    Try
        Con.ConnectionString = CropTrackMod.strConn
        SQLAdaptor.SelectCommand = New SqlClient.SqlCommand("SELECT ID, StockRef, Weight, EstimatedPrice, DespatchedQuantity,EstimatedTransportPer,VATRate, EstimatedTransportTotal,EstimatedVAT, EstimatedLineTotal,TicketRef FROM TicketDetail where ticketref ='x'", Con)
        builder = New SqlClient.SqlCommandBuilder(SQLAdaptor)
        Con.Open()

        Dim myTable As DataTable = New DataTable
        SQLAdaptor.Fill(myTable)

        dgvTicketDetail.DataSource = myTable

        'ID Column
        dgvTicketDetail.Columns(0).Visible = False

        'StockRef
        dgvTicketDetail.Columns(1).HeaderText = "StockRef"
        dgvTicketDetail.Columns(1).CellType.

        'Weight
        dgvTicketDetail.Columns(2).HeaderText = "Weight"
        dgvTicketDetail.Columns(2).DefaultCellStyle.Format = "0"
        dgvTicketDetail.Columns(2).DefaultCellStyle.NullValue = "0"

        'Price Per Unit
        dgvTicketDetail.Columns(3).HeaderText = "Price"
        dgvTicketDetail.Columns(3).DefaultCellStyle.Format = "0.00"
        dgvTicketDetail.Columns(3).DefaultCellStyle.NullValue = "0.00"

        'Quantity
        dgvTicketDetail.Columns(4).HeaderText = "Quantity"
        dgvTicketDetail.Columns(4).DefaultCellStyle.Format = "0"
        dgvTicketDetail.Columns(4).DefaultCellStyle.NullValue = "0"

        'Transport Cost Per Unit
        dgvTicketDetail.Columns(5).HeaderText = "TransportCostPer"
        dgvTicketDetail.Columns(5).DefaultCellStyle.Format = "0.00"
        dgvTicketDetail.Columns(5).DefaultCellStyle.NullValue = "0.00"

        'VAT Rate
        dgvTicketDetail.Columns(6).HeaderText = "VAT Rate"
        dgvTicketDetail.Columns(6).DefaultCellStyle.Format = "0.00"
        dgvTicketDetail.Columns(6).DefaultCellStyle.NullValue = "0.00"
        dgvTicketDetail.Columns(6).ReadOnly = True

        'Transport Total
        dgvTicketDetail.Columns(7).HeaderText = "Transport Total"
        dgvTicketDetail.Columns(7).DefaultCellStyle.Format = "0.00"
        dgvTicketDetail.Columns(7).DefaultCellStyle.NullValue = "0.00"
        dgvTicketDetail.Columns(7).ReadOnly = True

        'VAT Total
        dgvTicketDetail.Columns(8).HeaderText = "VAT Total"
        dgvTicketDetail.Columns(8).DefaultCellStyle.Format = "0.00"
        dgvTicketDetail.Columns(8).DefaultCellStyle.NullValue = "0.00"
        dgvTicketDetail.Columns(8).ReadOnly = True

        'line Total
        dgvTicketDetail.Columns(9).HeaderText = "Total"
        dgvTicketDetail.Columns(9).DefaultCellStyle.Format = "0.00"
        dgvTicketDetail.Columns(9).DefaultCellStyle.NullValue = "0.00"
        dgvTicketDetail.Columns(9).ReadOnly = True


        'line Total
        dgvTicketDetail.Columns(10).HeaderText = "TicketRef"
        dgvTicketDetail.Columns(10).Visible = False
    Finally
        If Con.State = ConnectionState.Open Then
            Con.Close()
        End If
    End Try

End Sub

One of the columns is for the stock reference, at the minute i am able to free type this field, but ultimately i would like this to be a combobox column where the user can select from a list of stock references from another table.

其中一列用于股票参考，现在我可以自由输入该字段，但最终我希望这是一个组合框列，用户可以从另一个表的股票参考列表中进行选择。

I have researched this issue and have found a number of examples which relate to creating a column at runtime but i dont feel that this would work in this situation as if i created the column manually then this would not be bound to the datasource which i want to save the information to.

我已经研究了这个问题，并发现了许多与在运行时创建列相关的示例，但我不认为这在这种情况下会起作用，就像我手动创建列那样，那么这不会绑定到我想要的数据源将信息保存到。

MANUAL COLUMN CREATION

手动创建列

Dim dgvc As DataGridViewComboBoxCell
dgvc = DataGridView1.Rows(0).Cells(2)

if DataGridView1.Rows(0).Cells(1).Value = "Hyman" then
    dgvc.Items.Add("Fe")
    dgvc.Items.Add("Fi")
elseif DataGridView1.Rows(0).Cells(3).Value = "Giant" then
    dgvc.Items.Add("Fo")
    dgvc.Items.Add("Fum")
End if

I originally planned on manually adding the items to the combobox but i have seen examples of being able to databind the items. I suppose this is another issue for another time

我最初计划手动将项目添加到组合框，但我已经看到能够对项目进行数据绑定的示例。我想这是另一个时间的另一个问题

Any help is always appreciated.

任何帮助总是受到赞赏。

Thanks in advance guys

提前谢谢各位

回答by Good Job Coding

I have just done something similar that I feel is more flexible, which was inspired by this so I thought that I would share. The main advantage is that you don't need to switch off the auto generation of the columns. This is after I have already set the datagridviews (dgvPickList) datasource to a datatable and also loaded my list of locations in the datatable dtLocations. It replaces the second column, which was bound to the field "Location"

我刚刚做了一些类似的事情，我觉得更灵活，这是受此启发，所以我想我会分享。主要优点是您不需要关闭列的自动生成。这是在我已经将 datagridviews (dgvPickList) 数据源设置为数据表并在数据表 dtLocations 中加载了我的位置列表之后。它替换了绑定到字段“位置”的第二列

Dim comboBoxColumn As DataGridViewComboBoxColumn = New DataGridViewComboBoxColumn()
comboBoxColumn.HeaderText = "Location"
comboBoxColumn.DataPropertyName = "Location"
comboBoxColumn.DataSource = dtLocations
comboBoxColumn.ValueMember = dtLocations.Columns(0).ColumnName
comboBoxColumn.DisplayMember = dtLocations.Columns(1).ColumnName

dgvPickList.Columns.RemoveAt(1)
dgvPickList.Columns.Insert(1, comboBoxColumn)

Hope that's useful.

希望这是有用的。

回答by PowerMan2015

I have resolved the issue by disabling the autogenerate columns with the datagridview and manually adding each column. This has allowed me to create a combobox column and add choices to this. Further improvements to this will be to add a datasource to the combobox column so that it automatically loads the items from the stock table

我已经通过使用 datagridview 禁用自动生成列并手动添加每列来解决该问题。这使我能够创建一个组合框列并为此添加选项。对此的进一步改进将是向组合框列添加一个数据源，以便它自动从库存表中加载项目

the code is as follows

代码如下

Private Sub SetTicketListTemp()

    Try
        Con.ConnectionString = CropTrackMod.strConn
        SQLAdaptor.SelectCommand = New SqlClient.SqlCommand("SELECT ID, StockRef, Weight, EstimatedPrice, DespatchedQuantity,EstimatedTransportPer,VATRate, EstimatedTransportTotal,EstimatedVAT, EstimatedLineTotal,TicketRef FROM TicketDetail where ticketref ='x'", Con)
        builder = New SqlClient.SqlCommandBuilder(SQLAdaptor)
        Con.Open()

        Dim myTable As DataTable = New DataTable
        SQLAdaptor.Fill(myTable)

        dgvTicketDetail.AutoGenerateColumns = False
        dgvTicketDetail.DataSource = myTable

        'ID Column
        Dim col1 As New DataGridViewTextBoxColumn
        col1.DataPropertyName = "ID"
        col1.HeaderText = "ID"
        col1.Name = "ID"
        col1.Visible = False
        dgvTicketDetail.Columns.Add(col1)


        'StockRef
        Dim col2 As New DataGridViewComboBoxColumn
        col2.DataPropertyName = "StockRef"
        col2.HeaderText = "StockRef"
        col2.Name = "StockRef"

        col2.Items.Add("StockItem1")
        col2.Items.Add("StockItem2")

        dgvTicketDetail.Columns.Add(col2)


        'Weight
        Dim col3 As New DataGridViewTextBoxColumn
        col3.DataPropertyName = "Weight"
        col3.HeaderText = "Weight"
        col3.Name = "Weight"
        col3.DefaultCellStyle.Format = "0"
        col3.DefaultCellStyle.NullValue = "0"
        dgvTicketDetail.Columns.Add(col3)

        'Price Per Unit
        Dim col4 As New DataGridViewTextBoxColumn
        col4.DataPropertyName = "EstimatedPrice"
        col4.HeaderText = "Price"
        col4.Name = "Price"
        col4.DefaultCellStyle.Format = "0.00"
        col4.DefaultCellStyle.NullValue = "0.00"
        dgvTicketDetail.Columns.Add(col4)

        'Quantity
        Dim col5 As New DataGridViewTextBoxColumn
        col5.DataPropertyName = "DespatchedQuantity"
        col5.HeaderText = "Quantity"
        col5.Name = "Quantity"
        col5.DefaultCellStyle.Format = "0"
        col5.DefaultCellStyle.NullValue = "0"
        dgvTicketDetail.Columns.Add(col5)

        'Transport Cost Per Unit
        Dim col6 As New DataGridViewTextBoxColumn
        col6.DataPropertyName = "EstimatedTransportPer"
        col6.HeaderText = "TransportCostPer"
        col6.Name = "TransportCostPer"
        col6.DefaultCellStyle.Format = "0.00"
        col6.DefaultCellStyle.NullValue = "0.00"
        dgvTicketDetail.Columns.Add(col6)


        'VAT Rate
        Dim col7 As New DataGridViewTextBoxColumn
        col7.DataPropertyName = "VATRate"
        col7.HeaderText = "VAT Rate"
        col7.Name = "VATRate"
        col7.DefaultCellStyle.Format = "0.00"
        col7.DefaultCellStyle.NullValue = "0.00"
        col7.ReadOnly = True
        dgvTicketDetail.Columns.Add(col7)

        'Transport Total
        Dim col8 As New DataGridViewTextBoxColumn
        col8.DataPropertyName = "EstimatedTransportTotal"
        col8.HeaderText = "Transport Total"
        col8.Name = "TransportTotal"
        col8.DefaultCellStyle.Format = "0.00"
        col8.DefaultCellStyle.NullValue = "0.00"
        col8.ReadOnly = True
        dgvTicketDetail.Columns.Add(col8)

        'VAT Total
        Dim col9 As New DataGridViewTextBoxColumn
        col9.DataPropertyName = "EstimatedVAT"
        col9.HeaderText = "VAT Total"
        col9.Name = "VATotal"
        col9.DefaultCellStyle.Format = "0.00"
        col9.DefaultCellStyle.NullValue = "0.00"
        col9.ReadOnly = True
        dgvTicketDetail.Columns.Add(col9)

        'line Total
        Dim col10 As New DataGridViewTextBoxColumn
        col10.DataPropertyName = "EstimatedLineTotal"
        col10.HeaderText = "Total"
        col10.Name = "Total"
        col10.DefaultCellStyle.Format = "0.00"
        col10.DefaultCellStyle.NullValue = "0.00"
        col10.ReadOnly = True
        dgvTicketDetail.Columns.Add(col10)



        'TicketRef
        Dim col11 As New DataGridViewTextBoxColumn
        col11.DataPropertyName = "TicketRef"
        col11.HeaderText = "TicketRef"
        col11.Name = "TicketRef"
        col11.Visible = False
        dgvTicketDetail.Columns.Add(col11)

    Finally
        If Con.State = ConnectionState.Open Then
            Con.Close()
        End If
    End Try

End Sub

hope this helps anyone else that gets stuck

希望这可以帮助其他陷入困境的人

Good Luck

祝你好运