Here's the problem:

I've got a datagrid that uses a datagridviewcombobox column that I want to populate with enumerated values from a MYSQL database - specifically, when the program starts, the DataGridView is populated with the names of students (Get1Click) - I then want to add a string variable from a dropdowncombo to indicated their home language (Japanese, Chinese, etc.) I'm using the dataadapter, dataset, etc. as below -
everything works fine until I want to update the datagridStore1Click), and then
nothing happens - am I missing some kind of binding or something?

I perused the Datagrid sample from MSN and my only words are, does it really need to be that complicated?? I can't imagine that this is so difficult, can anybody help??

Imports System.Data.Odbc

Public Class Form1

    Dim ceconn As New Odbc.OdbcConnection("Driver={MySQL ODBC 5.1 Driver};server=localhost;database=db_cecg0910;user=root;password=*****;")
    Dim comboda As OdbcDataAdapter
    Dim combods As New DataSet
    Dim cetblstring As String = ""
    Dim scAutoComplete As New AutoCompleteStringCollection

    'when form closes, connection is closed
    Private Sub ceDataForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        ceconn.Close()
    End Sub

    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is ComboBox Then
            With DirectCast(e.Control, ComboBox)
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
                .AutoCompleteCustomSource = scAutoComplete
            End With
        End If
    End Sub

    Private Sub Store1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Store1.Click

        Dim cecmdbuilder As New Odbc.OdbcCommandBuilder(comboda)
        Dim i As Integer
        Try
            i = comboda.Update(combods, cetblstring)
            MsgBox("Records Updated= " & i)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub Get1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Get1.Click

        Dim strcomboConn As String
        Dim comboconn As OdbcConnection
        Dim strdatamode As String = "Student"

        'determine which datamode the user has selected
        Select Case strdatamode

            Case "Student"

                'cesql = "SELECT * FROM tbl_stud"
                cetblstring = "tbl_stud"


        End Select

        strcomboConn = "Driver={MySQL ODBC 5.1 Driver};server=localhost;database=db_cecg0910;user=root;password=bunny;"
        comboconn = New OdbcConnection(strcomboConn)

        comboda = New OdbcDataAdapter("Select * from tbl_stud", comboconn)
        comboda.Fill(combods, "tbl_stud")
        DataGridView1.DataSource = combods.Tables("tbl_stud")

        Dim combocmd As New OdbcCommand("Select FIELD_L1NAME from tbl_L1", comboconn)
        Dim combodr As OdbcDataReader
        comboconn.Open()
        combodr = combocmd.ExecuteReader
        Do While combodr.Read
            scAutoComplete.Add(combodr.GetString(0))
        Loop
        comboconn.Close()

        Dim dgvcbc As New DataGridViewComboBoxColumn
        With dgvcbc
            .DataPropertyName = "FIELD_L1NAME"
            .DataSource = scAutoComplete
            .HeaderText = "FIELD_L1NAME"
        End With
        DataGridView1.Columns.Remove("FIELD_STUDENTL1")
        DataGridView1.Columns.Insert(2, dgvcbc)

    End Sub
End Class

Edited 3 Years Ago by Reverend Jim: Fixed formatting

thanks, adatapost; I'll read about bb tags and resubmit the code -

[Imports System.Data.Odbc]

[Public Class Form1]

’declare public variables for ODBCDataAdapter, Dataset, Tblstring]

[Dim ceconn As New Odbc.OdbcConnection("Driver={MySQL ODBC 5.1 [Driver};server=localhost;database=db_cecg0910;user=root;password=*****;")]
[Dim comboda As OdbcDataAdapter]
[Dim combods As New DataSet]
[Dim cetblstring As String = ""]
[Dim scAutoComplete As New AutoCompleteStringCollection]

[‘I’m not really sure what this does, but apparently it plays a role in replacing the] [‘regular Datagridview column with the DataGridViewDropDownComboBox]

[Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e [As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles] [DataGridView1.EditingControlShowing]
[If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is] [ComboBox Then]
[With DirectCast(e.Control, ComboBox)]
[.DropDownStyle = ComboBoxStyle.DropDown]
[.AutoCompleteMode = AutoCompleteMode.SuggestAppend]
[.AutoCompleteSource = AutoCompleteSource.CustomSource]
[.AutoCompleteCustomSource = scAutoComplete]
[End With]
[End If]
[End Sub]

[‘this is the code to write the values in the DataGridView back to the database using] [the Command Builder object]

[Private Sub Store1_Click(ByVal sender As System.Object, ByVal e As [System.EventArgs) Handles Store1.Click]

[Dim cecmdbuilder As New Odbc.OdbcCommandBuilder(comboda)]
[Dim i As Integer]
[Try]
[i = comboda.Update(combods, cetblstring)]
[MsgBox("Records Updated= " & i)]
[Catch ex As Exception]
[MsgBox(ex.Message)]
[End Try]

[End Sub]

**Note: the above code works fine for DataGridViews with no dropdowncombobox columns**

[‘this code populates the DataGridView from a database including populating the dropdowncombobox control from a dataset]

[Private Sub Get1_Click(ByVal sender As System.Object, ByVal e As [System.EventArgs) Handles Get1.Click]

[Dim strcomboConn As String]
[Dim comboconn As OdbcConnection]
[Dim strdatamode As String = "Student"]

['cesql = "SELECT * FROM tbl_stud"]
[cetblstring = "tbl_stud"]

[‘this code uses the Connection, DataAdapter, and Dataset object to populate the] [Datagridview]

[strcomboConn = "Driver={MySQL  ODBC 5.1 [Driver};server=localhost;database=db_cecg0910;user=root;password=bunny;"]
[comboconn = New OdbcConnection(strcomboConn)]
[comboda = New OdbcDataAdapter("Select * from tbl_stud", comboconn)]
[comboda.Fill(combods, "tbl_stud")]
[DataGridView1.DataSource = combods.Tables("tbl_stud")]

[‘this code uses the DataReader to get a second set of data from the database to be] [used to populate the DataGridViewComboBoxColumn]

[Dim combocmd As New OdbcCommand("Select FIELD_L1NAME from tbl_L1", [comboconn)]
[Dim combodr As OdbcDataReader]
[comboconn.Open()]
[combodr = combocmd.ExecuteReader]
[Do While combodr.Read]
[scAutoComplete.Add(combodr.GetString(0))]
[Loop]
[comboconn.Close()]

[‘this code removes and replaces a column from the DataGridView with a] [DataGridViewComboBoxColumn]

[Dim dgvcbc As New DataGridViewComboBoxColumn]
[With dgvcbc]
[.DataPropertyName = "FIELD_L1NAME"]
[.DataSource = scAutoComplete]
[.HeaderText = "FIELD_L1NAME"]
[End With]
[DataGridView1.Columns.Remove("FIELD_STUDENTL1")]
[DataGridView1.Columns.Insert(2, dgvcbc)]
[End Sub]
[End Class]

**Problem: When I select values for the drop down column, and try to save using StoreClick (above), I get an “Object reference not set error”

Comments
You got it.

Very good. Do not use [ ] with each statement.

Like : [ code=vb.net ] ... [/ code ]

Imports System.Data.Odbc

Public Class Form1
    ...
    ...
End Class
This article has been dead for over six months. Start a new discussion instead.