Updating a table from a Form DGV

Question

I have a DGV which loads from a stored procedure in my application:

Dim conn As New SqlConnection(My.Resources.FCLRptConn)
            Dim cmd As New SqlCommand("spFCLEditAgentAssignments", conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim ds As New DataSet
            Dim DA As New SqlDataAdapter(cmd)

            cmd.Parameters.AddWithValue("@ActiveIndicator", Me.tscmbActiveIndicator.ComboBox.SelectedValue)

            DA.Fill(ds)

            Me.dgvAgentAssignment.DataSource = ds.Tables(0)

Before this I added a DataGridViewComboBox column that binds to one of the columns in the DGV:

Dim conn As New SqlConnection(My.Resources.FCLRptConn)
            Dim cmd As New SqlCommand("spFCLLUVTeamAssignment", conn)
            Dim da As New SqlDataAdapter(cmd)
            Dim TeamAssign As New DataTable

            da.Fill(TeamAssign)

            With FCLTeam
                .DataPropertyName = "FCLTeamID"
                .Name = "FCLTeamID"
                .DataSource = TeamAssign
                .DisplayMember = "FCLTeamName"
                .ValueMember = "FCLTeamID"
                .AutoSizeMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            End With

When the table is loaded I come up with an "Object reference not set to an instance of an object" error. This occurs in this block of code:

 cmd.CommandText = "UPDATE tblFCLAgentAssignment SET [" & ColumnName & "] = '" & _
                             ConvertVBBooleanToSQLBit(dgvAgentAssignment.CurrentRow.Cells(ColumnName).Value) & _
                            "' WHERE RefID = '" & dgvAgentAssignment.CurrentRow.Cells("RefID").Value & "'"

The above code is used to update fields or cells that are updated through the DGV.

After I skip past the error I can updated the 'FCLTeamID' field with no issues. What could I be doing wrong on load?

Answer 1

 If Not String.IsNullOrEmpty(ColumnName) Then
            cmd.CommandText = "UPDATE tblFCLAgentAssignment SET [" & ColumnName & "] = '" & _
                                         ConvertVBBooleanToSQLBit(dgvAgentAssignment.CurrentRow.Cells(ColumnName).Value) & _
                                        "' WHERE RefID = '" & dgvAgentAssignment.CurrentRow.Cells("RefID").Value & "'"
        End If

This took care of the ColumnName issue where if it was empty it would not try to update the table