Private Sub PopComboBox(JobID As String)
'Dim dt As New DataTable
Dim conStr As String = QCGMAIN
Dim sqCon As New SqlConnection(conStr)
sqCon.Open()
Dim Adapter As New SqlDataAdapter("SELECT ItemID, LineDesc + ' ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
Adapter.Fill(CBdt)
sqCon.Close()
Me.POItems.ValueMember = "ItemID" 'You can just reference the name of the Combobox directly in the grid. (Me.POItems is the name of the column in the DGV)
Me.POItems.DisplayMember = "DisplayMem"
Me.POItems.DataSource = CBdt
'DirectCast(Me.DataGridView1.Columns("POItems"), DataGridViewComboBoxColumn).ValueMember = "ItemID"
'DirectCast(Me.DataGridView1.Columns("POItems"), DataGridViewComboBoxColumn).DisplayMember = "DisplayMem"
'DirectCast(Me.DataGridView1.Columns("POItems"), DataGridViewComboBoxColumn).DataSource = dt
Me.DataGridView1.Columns("POItems").ReadOnly = False 'Now make only this column editable
Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
col.Width = 265
End Sub
'Private WithEvents editingControl As ComboBox
'Sub 1 of 4 DGV Combobox SelectedIndexChanged
Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
'editingControl = TryCast(e.Control, ComboBox)
If DataGridView1.CurrentCell.ColumnIndex = 2 Then
Dim CmbBx As ComboBox = TryCast(e.Control, ComboBox)
If (CmbBx IsNot Nothing) Then
' Remove an existing event-handler, if present, to avoid adding multiple handlers when the editing control is reused.
RemoveHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf editingControl_SelectedIndexChanged)
AddHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf editingControl_SelectedIndexChanged)
End If
End If
End Sub
'Sub 2 of 4 DGV Combobox
Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
'editingControl = Nothing
End Sub
'Sub 3 of 4 DGV Combobox
Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
If IsNothing(DataGridView1.CurrentCell) Then Exit Sub
If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewComboBoxCell Then
DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
End If
If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
End If
End Sub
'Sub 4 of 4 DGV Combobox
Private Sub editingControl_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles editingControl.SelectedIndexChanged
Dim CmbBx As ComboBox = TryCast(sender, ComboBox)
'Dim CmbBx As ComboBox = editingControl
Dim DGV As DataGridView = Me.DataGridView1
Dim row As DataGridViewRow = DGV.CurrentRow
Dim ItemID As String = CmbBx.SelectedValue 'Shows the ValueMember value
'Dim DisplayMem As String = CmbBx.DisplayMember
Dim SageSO As String = (CType(CmbBx.SelectedItem, DataRowView)).Row.ItemArray(2).ToString() 'Pulls the 3rd column (numbered 0-2) in the datarow inside the Combobox (See PopComboBox Sub)
Dim JobID As String = Me.QuoteNoTxtBx.Text & Me.SufxTxtBx.Text
Dim CustPO As String = Nothing
Dim POQTY As String = Nothing
Dim POUnitEa As String = Nothing
Dim POExt As String = Nothing
Dim sqCon As New SqlClient.SqlConnection(QCGMAIN)
Dim sqCmd As New SqlClient.SqlCommand
'Read database
sqCmd.Connection = sqCon 'create the DB connection
sqCon.Open() 'open the connection
sqCmd.CommandText = "SELECT QTY, UnitPrice, Amount FROM SageJobs WHERE SageSO = '" & SageSO & "' AND JobID = '" & JobID & "' AND ItemID = '" & ItemID & "'"
Dim sqReader As SqlDataReader = sqCmd.ExecuteReader() 'execute the SQL command
If sqReader.HasRows Then
While sqReader.Read()
If Not IsDBNull(sqReader.Item("QTY")) Then
POQTY = sqReader.Item("QTY").ToString
End If
If Not IsDBNull(sqReader.Item("UnitPrice")) Then
POUnitEa = sqReader.Item("UnitPrice").ToString
End If
If Not IsDBNull(sqReader.Item("Amount")) Then
POExt = sqReader.Item("Amount").ToString
End If
End While
End If
sqReader.Close() 'always close the reader when you're done with it.
sqCon.Close()
Try
sqCmd = sqCon.CreateCommand()
sqCmd.CommandText = "SELECT CustPO FROM dbo.SageJobs WHERE SageSO = '" & SageSO & "' And CustPO != ''"
sqCon.Open()
Dim RetVal As Object = sqCmd.ExecuteScalar()
If Not IsNothing(RetVal) And Not IsDBNull(RetVal) Then
CustPO = RetVal 'for boolean or RetVal.ToString for string
End If
sqCon.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
'Populate Sage line item info into Grid row:
row.Cells("ItemID").Value = ItemID
row.Cells("CustPO").Value = CustPO
row.Cells("SageSO").Value = SageSO
row.Cells("POQTY").Value = POQTY
row.Cells("POUnitEa").Value = POUnitEa
row.Cells("POExt").Value = POExt
End Sub