Datagridviewcombobox Refresh

[EDN Admin]

Hi Guys,
I have an application with a datagridview populated from a database. One of the columns is a combobox. When I change the value of the combobox the selectedindexchanged the data updates and the grid refreshes OK. If I try to update another row, I get a NullReferenceException
error refering to the combobox.selectedvalue.
The code is below:-
<span lang="EN-AU
Public Class Form1
Public Shared myconnection As OleDb.OleDbConnection
Public Shared mysql As String
Public Shared Function GetConn() As OleDb.OleDbConnection
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Driver.mdb"
Try
myconnection = New OleDb.OleDbConnection(ConnString)
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return myconnection
End Function

Public Shared Function GetDataset(ByVal mysql)
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(mysql, GetConn)
da.Fill(ds, "ds")
Return ds
End Function

Public Shared Function GetDriverList() As DataSet
Dim mysql As String
Dim ds As DataSet
mysql = "SELECT * FROM Drivers"
ds = GetDataset(mysql)
Return ds
End Function

Dim dt As New DataTable("DriverTable")
Dim ds As New DataSet("Drivers")

Sub GetDataTable()

Create Columns
Dim colid As New DataColumn("id", GetType(String))
Dim colname As New DataColumn("name", GetType(String))
Dim colshift As New DataColumn("shift", GetType(String))
Dim colbarcode As New DataColumn("barcode", GetType(String))
Dim colphone As New DataColumn("phone", GetType(Integer))
Dim colstartdate As New DataColumn("startdate", GetType(String))
Dim colclocknumber As New DataColumn("clocknumber", GetType(String))
Dim colclassification As New DataColumn("classification", GetType(String))
Dim coltask As New DataColumn("task", GetType(String))
Add columns to table
dt.Columns.Add(colid)
dt.Columns.Add(colname)
dt.Columns.Add(colshift)
dt.Columns.Add(colbarcode)
dt.Columns.Add(colphone)
dt.Columns.Add(colstartdate)
dt.Columns.Add(colclocknumber)
dt.Columns.Add(colclassification)
dt.Columns.Add(coltask)
Add DataTable to DataSet
ds.Tables.Add(dt)
End Sub

Sub FillDataTable()
Dim dsDrivers As DataSet
dsDrivers = GetDriverList()
dt.Clear()
Dim dr As DataRow
For i = 0 To 19
Step to add DataRow
dr = ds.Tables("DriverTable").NewRow
dr("id") = dsDrivers.Tables(0).Rows(i)("id")
dr("name") = dsDrivers.Tables(0).Rows(i)("name")
dr("shift") = dsDrivers.Tables(0).Rows(i)("shift")
dr("barcode") = dsDrivers.Tables(0).Rows(i)("barcode")
dr("phone") = dsDrivers.Tables(0).Rows(i)("phone")
dr("startdate") = dsDrivers.Tables(0).Rows(i)("startdate")
dr("clocknumber") = dsDrivers.Tables(0).Rows(i)("clocknumber")
dr("classification") = dsDrivers.Tables(0).Rows(i)("classification")
dr("task") = dsDrivers.Tables(0).Rows(i)("task")
Add Row
ds.Tables("DriverTable").Rows.Add(dr)
Next
binddata to gridview
DataGridView1.DataSource = ds.Tables("DriverTable").Copy
DataGridView1.AutoResizeColumns()
With DataGridView1.Columns("id")
.HeaderText = "ID"
.Visible = False
End With
With DataGridView1.Columns("name")
.HeaderText = "Name"
End With
mysql = "SELECT distinct shift FROM drivers"
Dim dsShift As DataSet
dsShift = GetDataset(mysql)
Try
DataGridView1.Columns.Remove(DataGridView1.Columns("shift"))
Dim colshiftnew As New DataGridViewComboBoxColumn
With colshiftnew
.DisplayStyleForCurrentCellOnly = True
.DataPropertyName = "shift"
.DataSource = dsShift.Tables(0)
.DisplayMember = "shift"
.ValueMember = "shift"
.HeaderText = "Shift"
.Width = 100
End With
DataGridView1.Columns.Insert(2, colshiftnew)
Catch ex As Exception
nothing to do
End Try
With DataGridView1.Columns("barcode")
.HeaderText = "Barcode"
End With
With DataGridView1.Columns("phone")
.HeaderText = "Phone"
End With
With DataGridView1.Columns("startdate")
.HeaderText = "Start Date"
End With
With DataGridView1.Columns("classification")
.HeaderText = "Class"
End With
With DataGridView1.Columns("clocknumber")
.HeaderText = "Clock"
End With
With DataGridView1.Columns("task")
.HeaderText = "Task"
End With
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
GetDataTable()
FillDataTable()
End Sub

Private Sub DataGridView1_CellFormatting(ByVal sender As Object, ByVal e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
If e.ColumnIndex = DataGridView1.Columns(1).Index AndAlso e.Value IsNot Nothing Then
If DataGridView1.Item(DataGridView1.Columns(7).Index, e.RowIndex).Value.ToString = "PERM" Then
e.CellStyle.SelectionForeColor = Color.Blue
e.CellStyle.ForeColor = Color.Blue
ElseIf DataGridView1.Item(DataGridView1.Columns(7).Index, e.RowIndex).Value.ToString = "INST" Then
e.CellStyle.SelectionForeColor = Color.Green
e.CellStyle.ForeColor = Color.Green
Else
e.CellStyle.SelectionForeColor = Color.Red
e.CellStyle.ForeColor = Color.Red
End If
End If

End Sub

Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
Dim combo As ComboBox = CType(e.Control, ComboBox)
If (combo IsNot Nothing) Then
RemoveHandler combo.SelectedIndexChanged, New EventHandler(AddressOf ComboBox_SelectedIndexChanged)
AddHandler combo.SelectedIndexChanged, New EventHandler(AddressOf ComboBox_SelectedIndexChanged)
End If
End Sub

Private Sub ComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim cbshift As ComboBox = CType(sender, ComboBox)
cbshift.SelectedValue = CType(CType(sender, ComboBox).SelectedValue, String) .......................this seems to go missing
mysql = "UPDATE Drivers SET " _
& "shift = " & cbshift.SelectedValue.ToString _
& " WHERE ID = " & DataGridView1.Item(DataGridView1.Columns(0).Index, DataGridView1.CurrentRow.Index).Value
UpdateData(mysql)
RefreshDataGrids()
End Sub

Public Shared Sub UpdateData(ByVal mysql)
Dim cmd As Integer
Dim cmdUpdate As New OleDb.OleDbCommand(mysql, GetConn)
myconnection.Open()
cmd = cmdUpdate.ExecuteNonQuery()
myconnection.Close()
cmdUpdate.Dispose()
End Sub

Sub RefreshDataGrids()
FillDataTable()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click handles a manual grid refresh
FillDataTable()
End Sub

End Class


<span style="font-family:Consolas; font-size:x-small <span style="font-family:Consolas; font-size:x-small I feel its a databinding issue but would appreciate any help.
<span style="font-family:Consolas; font-size:x-small <span style="font-family:Consolas; font-size:x-small Cheers
<span style="font-family:Consolas; font-size:x-small <span style="font-family:Consolas; font-size:x-small Peter
<span style="font-family:Consolas; font-size:x-small <span style="font-family:Consolas; font-size:x-small
<span style="font-family:Consolas; font-size:x-small <span style="font-family:Consolas; font-size:x-small
<span lang="EN-AU <span style="font-family:Consolas; font-size:x-small <span style="font-family:Consolas; font-size:x-small


View the full article