[RESOLVED] Help Datagridview error: datagridviewcomboboxcell value is not valid?

[coolwater]

I have a datagridview (bound to a DataTable) that has a combobox column (bound to bindingsource). Inserting data is not a problem. The problem occurs when I edit a row the datagridview throws an exception "datagridviewcomboboxcell value is not valid". The DGV combobox turns blank afterwards.

I posted this code to the DGV DataError Event to find the exact error:

        If (e.Context = DataGridViewDataErrorContexts.Commit) Then
            MessageBox.Show("Commit error")
        End If

        If (e.Context = DataGridViewDataErrorContexts.CurrentCellChange) Then
            MessageBox.Show("Cell change")
        End If
        If (e.Context = DataGridViewDataErrorContexts.Parsing) Then
            MessageBox.Show("parsing error")
        End If
        If (e.Context = DataGridViewDataErrorContexts.LeaveControl) Then
            MessageBox.Show("leave control error")
        End If

        If (TypeOf (e.Exception) Is ConstraintException) Then
            Dim view As DataGridView = CType(sender, DataGridView)
            view.Rows(e.RowIndex).ErrorText = "an error"
            view.Rows(e.RowIndex).Cells(e.ColumnIndex).ErrorText = "an error"

            e.ThrowException = False
        End If

I got this error "Error happened formatting, Display" and after I click ok I get this error "Error happened formatting, preferred Size".

I'm stuck. Can't figure out what I am doing wrong?

[kaliman79912]

Double check that the new value that you are inserting in the field where the ComboBox column is, is included in the combobox's datasource (not the DGV datasource). Are you modifying the record via code?

[coolwater]

The combobox is a lookup. User can only select from the list. User cannot add a new item on the combobox. Weird if I delete the row that I want to edit and then add a new row, I get no errors. But if I just edit a row formatting errors occurs.

[kaliman79912]

By editing the row, does the user simply go in any cell and change a value? no code is involved? Check that the corresponding fields in both tables have the same type and size.

[coolwater]

Yes the user simply go to any cell and edit the values. My DGV is bound to a datatable. To save the data is use sqladapter.update(table). Inserting and deleting data is not a problem. The editing part is causing the errors.

The code I use for DGV insert, update, delete I got it from this link http://www.vbforums.com/showthread.p...ses&highlight=

my DGV has two columns: SLID (bigint), Amount (money)

BTW although I get errors the update command still executed succefully and saved the edited rows. I just can't seem to figure out what throws the exception?

[kaliman79912]

Have you checked that? the size of the fields in each table?

[coolwater]

Sorry for the late reply. Yes I have checked the size of the fields in my tables. Again it is weird my sqladapter commands are working. Insert, delete and update commands are successfully executed. The database is ok. The problem is after the execution of the update command the datagridview throws the said error.

In my program I have an add button and save button. By default my DGV is readonly.

Add button code:

    Private Sub cmdAddDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddDetails.Click

        If dgvDisbursementDetails.RowCount = 0 Then
            isAddDetails = True
        Else
            isAddDetails = False
        End If

        If Me.txtDisbursementID.Text <> "" Then

            dgvDisbursementDetails.ReadOnly = False
            dgvDisbursementDetails.AllowUserToAddRows = True
            dgvDisbursementDetails.AllowUserToDeleteRows = True
            dgvDisbursementDetails(4, dgvDisbursementDetails.NewRowIndex).Selected = True
            cmdSaveDetails.Enabled = True
            cmdAddDetails.Enabled = False
            cmdDeleteDetails.Enabled = False



        End If

    End Sub

My Save button Code: (DGV Error occurs only and after the update)

Private Sub cmdSaveDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSaveDetails.Click
                    If isAddDetails = True Then
                        'Add mode
                        AddCredit()

                        'Add details
                        sqladapter.Update(dtable)
                        dgvDisbursementDetails.AllowUserToAddRows = False
                        dgvDisbursementDetails.AllowUserToDeleteRows = False
                        dgvDisbursementDetails.ReadOnly = True
                        dgvDisbursementDetails.RefreshEdit()
                        dgvDisbursementDetails.Refresh()
                        cmdSaveDetails.Enabled = False
                        cmdAddDetails.Enabled = True
                        cmdDeleteDetails.Enabled = True
                        sqladapter.Update(dtable)
                    Else
                        'Edit mode
                        'Edit details
                        sqladapter.Update(dtable)
                        dgvDisbursementDetails.AllowUserToAddRows = False
                        dgvDisbursementDetails.AllowUserToDeleteRows = False
                        dgvDisbursementDetails.ReadOnly = True
                        dgvDisbursementDetails.RefreshEdit()
                        dgvDisbursementDetails.Refresh()
                        cmdSaveDetails.Enabled = False
                        cmdAddDetails.Enabled = True
                        cmdDeleteDetails.Enabled = True

                    End If
   End Sub

My code for insert, delete and update. (No sqlexception encountered)

    Private Sub InitializeDataAdapter(ByVal mDisbursementID As Long)


        Dim con As SqlConnection = New SqlConnection(CS)

        Try
            Dim delete As New SqlCommand("sproc_AccountingBooksDeleteByDisbursementID", con)
            delete.CommandType = CommandType.StoredProcedure
            delete.Parameters.Add("@AcctID", SqlDbType.BigInt, 8, "AcctID")
            sqladapter.DeleteCommand = delete

            Dim insert As New SqlCommand("sproc_AccountingBooksDebitInsert", con)
            insert.CommandType = CommandType.StoredProcedure
            insert.Parameters.Add("@DisbursementID", SqlDbType.BigInt, 8, "DisbursementID")
            insert.Parameters.Add("@CompanyID", SqlDbType.BigInt, 8, "CompanyID")
            insert.Parameters.Add("@DatePosted", SqlDbType.DateTime, 8, "DatePosted")
            insert.Parameters.Add("@SLID", SqlDbType.BigInt, 8, "SLID")
            insert.Parameters.Add("@Debit", SqlDbType.Money, 8, "Debit")
            insert.Parameters.Add("@AccountingPeriodID", SqlDbType.BigInt, 8, "AccountingPeriodID")
            insert.Parameters.Add("@UserID", SqlDbType.BigInt, 8, "UserID")
            sqladapter.InsertCommand = insert

            Dim update As New SqlCommand("sproc_AccountingBooksDebitUpdate", con)
            update.CommandType = CommandType.StoredProcedure
            update.Parameters.Add("@DisbursementID", SqlDbType.BigInt, 8, "DisbursementID")
            update.Parameters.Add("@CompanyID", SqlDbType.BigInt, 8, "CompanyID")
            update.Parameters.Add("@DatePosted", SqlDbType.DateTime, 8, "DatePosted")
            update.Parameters.Add("@SLID", SqlDbType.BigInt, 8, "SLID")
            update.Parameters.Add("@Debit", SqlDbType.Money, 8, "Debit")
            update.Parameters.Add("@AccountingPeriodID", SqlDbType.BigInt, 8, "AccountingPeriodID")
            update.Parameters.Add("@UserID", SqlDbType.BigInt, 8, "UserID")
            update.Parameters.Add("@Original_AcctID", SqlDbType.BigInt, 8, "AcctID")
            update.Parameters.Add("@Original_DisbursementID", SqlDbType.BigInt, 8, "DisbursementID")
            sqladapter.UpdateCommand = update

            sqladapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try

    End Sub

[coolwater]

I read this article http://www.kebabshopblues.co.uk/2007...omment-page-1/. I have the exact problem. I followed the author's solution but I'm still having the same error. :-(

[coolwater]

I got it. Stupid of me. I found the error. It's in my update storedproc. I failed to put the original ID parameter.

Original SP:

USE [SimpleAcctg]
GO
/****** Object:  StoredProcedure [dbo].[sproc_AccountingBooksDebitUpdate]    Script Date: 11/16/2013 23:09:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_AccountingBooksDebitUpdate]
(
	@DisbursementID bigint,
	@CompanyID bigint,
	@DatePosted datetime,
	@SLID bigint,
	@Debit money,
    @AccountingPeriodID bigint,
	@UserID bigint,
	@Original_AcctID bigint,
	@Original_DisbursementID bigint
)
AS
SET NOCOUNT OFF;

UPDATE [dbo].[tblAccountingBooks] 
SET 
[DisbursementID] = @DisbursementID, 
[CompanyID] = @CompanyID, 
[DatePosted] = @DatePosted, 
[SLID] = @SLID, 
[Debit] = @Debit,
[AccountingPeriodID] = @AccountingPeriodID, 
[UserID] = @UserID 

WHERE (([AcctID] = @Original_AcctID) 
AND ([DisbursementID] = @Original_DisbursementID));

	
SELECT AcctID, DisbursementID, CompanyID, DatePosted, SLID, Debit, AccountingPeriodID, UserID FROM tblAccountingBooks WHERE (DisbursementID = @DisbursementID)

Updated SP:

USE [SimpleAcctg]
GO
/****** Object:  StoredProcedure [dbo].[sproc_AccountingBooksDebitUpdate]    Script Date: 11/16/2013 23:09:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_AccountingBooksDebitUpdate]
(
	@DisbursementID bigint,
	@CompanyID bigint,
	@DatePosted datetime,
	@SLID bigint,
	@Debit money,
    @AccountingPeriodID bigint,
	@UserID bigint,
	@Original_AcctID bigint,
	@Original_DisbursementID bigint
)
AS
SET NOCOUNT OFF;

UPDATE [dbo].[tblAccountingBooks] 
SET 
[DisbursementID] = @DisbursementID, 
[CompanyID] = @CompanyID, 
[DatePosted] = @DatePosted, 
[SLID] = @SLID, 
[Debit] = @Debit,
[AccountingPeriodID] = @AccountingPeriodID, 
[UserID] = @UserID 

WHERE (([AcctID] = @Original_AcctID) 
AND ([DisbursementID] = @Original_DisbursementID));

	
SELECT AcctID, DisbursementID, CompanyID, DatePosted, SLID, Debit, AccountingPeriodID, UserID FROM tblAccountingBooks WHERE (AcctID = @Original_AcctID) AND (DisbursementID = @DisbursementID)