Dear Sir,
I have an error `Operation did not succeed because the program cannot commit or quit a cell value change. `
If I comment out code CellValidating event in form2.
then the error does not appear but I use one form 'form2' as a view and new transaction please guide me.
Thanks
code in form1
Imports System.ComponentModel
Imports System.Configuration
Imports System.Data.OleDb
Imports System.Reflection
Imports Dapper
Public Class Form1
Private InvoicesService As New InvoicesService()
Private bindingSource As BindingSource = Nothing
Private _criteriasBindingList As New BindingList(Of Invoices)()
Public Sub New()
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
' To reduce the flickering...
DataGridView1.GetType().
GetProperty("DoubleBuffered",
BindingFlags.Instance Or BindingFlags.NonPublic).
SetValue(DataGridView1, True)
DataGridView1.ReadOnly = True
LoadData()
DataGridView1.Columns.Add(New DataGridViewLinkColumn With {
.Name = "Colview",
.Text = "👁🗨",
.HeaderText = "",
.UseColumnTextForLinkValue = True,
.ActiveLinkColor = System.Drawing.Color.MediumAquamarine,
.LinkColor = System.Drawing.Color.MediumAquamarine,
.VisitedLinkColor = System.Drawing.Color.MediumAquamarine,
.LinkBehavior = LinkBehavior.NeverUnderline,
.ReadOnly = True,
.DataPropertyName = "Colview",
.Width = 20})
End Sub
Private Sub LoadData()
Dim list = InvoicesService.GetInvoices()
_criteriasBindingList = New BindingList(Of Invoices)(CType(list, IList(Of Invoices)))
bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
DataGridView1.DataSource = bindingSource
End Sub
Private Function GetSELECT() As Invoices
Return If(DataGridView1.SelectedCells.Count = 0, Nothing, TryCast(DataGridView1.SelectedCells(0).OwningRow.DataBoundItem, Invoices))
End Function
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
Dim dgv = DirectCast(sender, DataGridView)
Dim bs = TryCast(bindingSource.DataSource, BindingList(Of Invoices))
If e.RowIndex >= 0 AndAlso
dgv.Columns(e.ColumnIndex) Is DataGridView1.Columns("colview") Then
Dim SelectDgv = GetSELECT()
Dim Invno = DirectCast(DataGridView1.SelectedRows(0).DataBoundItem, Invoices)
Dim frm = New Form2(SelectDgv)
If (TryCast(Application.OpenForms("Form2"), Form2)) IsNot Nothing Then
'Form is already open
MessageBox.Show("Form is already open")
Else
' Form is not open
'MessageBox.Show("Form is not open")
frm.Show()
End If
End If
End Sub
Private Sub Btnnew_Click(sender As Object, e As EventArgs) Handles Btnnew.Click
Dim frm = New Form2()
If (TryCast(Application.OpenForms("Form2"), Form2)) IsNot Nothing Then
'Form is already open
MessageBox.Show("Form is already open")
Else
' Form is not open
'MessageBox.Show("Form is not open")
`error below code for new ``Operation did not succeed because the program cannot commit or quit a cell value change. ``
frm.Show()
End If
End Sub
End Class
Public Class Invoices
Public Property INVNO() As String
Public Property INVDATE() As Date
Public Property InvoicesDetail() As New List(Of InvoicesDetail)()
End Class
Public Class InvoicesDetail
<Browsable(False)>
Public Property INVNO() As String
Public Property PRODUCTNAME() As String
Public Property UNIT() As String
Public Property QTY() As Integer
Public Class DTOBILLInvoices
Public Property NAMEINV() As String
Public Property INVNO() As String
Public Property INVDATE() As Date
Public Property TOTPRSINV() As Double
Public Property PAYMENT() As Double
Public Property BALANCE() As Double
Public Property STATUS() As String
End Class
End Class
Public Module DbContext
Public Function GetOledbConnectionString() As String
Return ConfigurationManager.AppSettings("GetOledbConnectionString")
End Function
Public Function GetConnection(name As String) As OleDbConnection
If name = "GetOledbConnectionString" Then
Return New OleDbConnection(GetOledbConnectionString())
Else
Return New OleDbConnection(GetOledbConnectionString())
End If
End Function
End Module
Public Class InvoicesService
Private ReadOnly _conn As OleDbConnection
Private _connectionString As String = DbContext.GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Public Function GetInvoices() As IEnumerable(Of Invoices)
Dim sql = "SELECT * FROM Invoices"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Invoices)(sql).ToList()
End Using
End Function
Public Function GetInvoicesdetail(ByVal Invno As String) As IEnumerable(Of InvoicesDetail)
Dim sql = $"SELECT * FROM InvoicesDetail WHERE Invno = '{Invno}'"
Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
Return _conn.Query(Of InvoicesDetail)(sql).ToList()
End Using
End Function
End Class
code in form2
Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.Reflection
Imports Dapper
Public Class Form2
Private dictProducts As Dictionary(Of String, Products)
Dim InvoicesService As New InvoicesService()
Private BindingSource1 As BindingSource = Nothing
Private BindingSource2 As BindingSource = Nothing
Public Sub New(Invno As Invoices)
Me.New
DataGridView1.GetType().
GetProperty("DoubleBuffered",
BindingFlags.Instance Or BindingFlags.NonPublic).
SetValue(DataGridView1, True)
DataGridView1.AllowUserToAddRows = False
BindingSource2 = New BindingSource With {.DataSource = New BindingList(Of InvoicesDetail)(CType(InvoicesService.GetInvoicesdetail(Invno.INVNO), IList(Of InvoicesDetail)))}
'error below code for view``Operation did not succeed because the program cannot commit or quit a cell value change. ``
DataGridView1.DataSource = BindingSource2
For Each column As DataGridViewColumn In DataGridView1.Columns
column.SortMode = DataGridViewColumnSortMode.NotSortable
Next column
End Sub
Public Sub New()
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
' To reduce the flickering...
DataGridView1.GetType().
GetProperty("DoubleBuffered",
BindingFlags.Instance Or BindingFlags.NonPublic).
SetValue(DataGridView1, True)
updateDataSource()
End Sub
Protected Sub updateDataSource()
dictProducts = ProductsService.GetDistinctProducts().
ToDictionary(Function(p) p.ToString(), Function(p) p)
BindingSource1 = New BindingSource With {.DataSource = New BindingList(Of Products)}
DataGridView1.DataSource = BindingSource1
DataGridView1.AutoGenerateColumns = False
DataGridView1.AllowUserToAddRows = False
DataGridView1.Columns("UNIT").ReadOnly = True
BindingSource1.AddNew()
End Sub
Private Sub DataGridView1_EditingControlShowing(
sender As Object,
e As DataGridViewEditingControlShowingEventArgs) Handles _
DataGridView1.EditingControlShowing
If TypeOf e.Control Is TextBox Then
Dim dgv = DirectCast(sender, DataGridView)
Dim tb = DirectCast(e.Control, TextBox)
Dim cellRect As Rectangle = DataGridView1.GetCellDisplayRectangle(DataGridView1.CurrentCell.ColumnIndex, DataGridView1.CurrentCell.RowIndex, True)
If dgv.CurrentCell.ColumnIndex = dgv.Columns("PRODUCTNAME").Index Then
If tb.AutoCompleteCustomSource.Count = 0 Then
tb.AutoCompleteSource = AutoCompleteSource.CustomSource
tb.AutoCompleteCustomSource.AddRange(dictProducts.Keys.ToArray())
End If
tb.AutoCompleteMode = AutoCompleteMode.Suggest
Else
tb.AutoCompleteMode = AutoCompleteMode.None
End If
End If
'End If
End Sub
Private Sub DataGridView1_CellValidating(
sender As Object,
e As DataGridViewCellValidatingEventArgs) _
Handles DataGridView1.CellValidating
Dim dgv = DirectCast(sender, DataGridView)
If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
e.RowIndex <> dgv.NewRowIndex Then
Dim key As String
If dgv.IsCurrentCellInEditMode Then
Dim p = dictProducts.Values.
FirstOrDefault(Function(x) x.PRODUCTNAME.
Equals(e.FormattedValue.
ToString(), StringComparison.InvariantCultureIgnoreCase))
If p IsNot Nothing Then
key = p.ToString()
Else
key = e.FormattedValue.ToString()
End If
Else
key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
End If
If String.IsNullOrEmpty(key) OrElse
Not dictProducts.ContainsKey(key) Then
Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Products)
boundItem.UNIT = Nothing
dgv.Rows(e.RowIndex).ErrorText = "Invalid Product!"
dgv.UpdateCellValue(dgv.Columns("UNIT").Index, e.RowIndex)
e.Cancel = True
End If
End If
End Sub
Private Sub DataGridView1_CellEndEdit(
sender As Object,
e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
Dim dgv = DirectCast(sender, DataGridView)
Dim src = DirectCast(dgv.DataSource, BindingSource)
If src.Count = e.RowIndex Then Return
If e.ColumnIndex = dgv.Columns("PRODUCTNAME").Index AndAlso
Not String.IsNullOrEmpty(dgv.Rows(e.RowIndex).ErrorText) Then
Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
Dim p As Products = Nothing
Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Products)
If String.IsNullOrEmpty(boundItem.UNIT) AndAlso
dictProducts.TryGetValue(key, p) Then
boundItem.UNIT = p.UNIT
dgv.UpdateCellValue(dgv.Columns("UNIT").Index, e.RowIndex)
dgv.Rows(e.RowIndex).ErrorText = Nothing
End If
End If
End Sub
End Class
Public Class Products
Public Property PRODUCTNAME() As String
Public Property UNIT() As String
Public Property QTY() As Double
Public Overrides Function ToString() As String
Return $"{PRODUCTNAME}".Trim()
End Function
End Class
Public Class ProductsService
Private ReadOnly _conn As OleDbConnection
Private _connectionString As String = DbContext.GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Friend Shared Function GetDistinctProducts() As IEnumerable(Of Products)
Dim sql = "SELECT DISTINCT PRODUCTNAME,UNIT
FROM Products
ORDER BY PRODUCTNAME"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Products)(sql)
End Using
End Function
End Class
Link database
