Dear Sir ,
I'm Trying to Update record database in datagridview with column checkbox and selection row with database MSACCESS via dapper.
So I want after I filter then I update the database based on the DataGridView column checkbox and also can I apply a database update with a
selection row in the DatagridView?. Column checkbox with can checkbox all and also manually column checkbox that we choose.
There is an addition of being able to uncheckbox the datagridview column.
Please Guide Me
Thanks
as per the previous link : previous link
Link database MSACCESS
Public Class Form1
Private _criteriasBindingList As New SortableBindingList(Of Person)()
Private bindingSource As BindingSource = Nothing
Dim PersonService As New PersonService()
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Person.accdb;Persist Security Info=False;"
End Function
Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click
Try
_criteriasBindingList = New SortableBindingList(Of Person)(CType(bindingSource.DataSource, IList(Of Person)))
Dim i As Integer = 0
If _criteriasBindingList.Count > 0 Then
For Each person As Person In _criteriasBindingList
Dim Update = New Person With {
.TIME = TXTTIME.Text,
.OTHERS1 = TXTOTHERS1.Text,
.OTHERS2 = TXTOTHERS2.Text,
.NAMEPERSON = person.NAMEPERSON,
.DATE = person.DATE}
PersonService.UpdatePerson(Update)
_criteriasBindingList.Item(i).TIME = TXTTIME.Text
' Checking Other1 TextBox.
If Not String.IsNullOrEmpty(TXTOTHERS1.Text) Then
_criteriasBindingList.Item(i).OTHERS1 = TXTOTHERS1.Text
End If
' Checking Other2 TextBox.
If Not String.IsNullOrEmpty(TXTOTHERS2.Text) Then
_criteriasBindingList.Item(i).OTHERS2 = TXTOTHERS2.Text
End If
i = i + 1
Next
bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
DataGridView1.DataSource = bindingSource
MessageBox.Show("Person In successfully updated")
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Myapp", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
loaddata()
End Sub
Private Sub loaddata()
_criteriasBindingList = New SortableBindingList(Of Person)(CType(PersonService.GetPERSON(), IList(Of Person)))
bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
DataGridView1.DataSource = bindingSource 'Set the data source.
End Sub
Private Sub myFilter(str1 As String, str2 As String)
loaddata()
If (String.IsNullOrEmpty(str1) AndAlso String.IsNullOrEmpty(str2)) Then
bindingSource.DataSource = _criteriasBindingList
ElseIf (String.IsNullOrEmpty(str1)) Then
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.DATE.ToString("dd-MM-yyyy").Contains(str2)).ToList()
ElseIf (String.IsNullOrEmpty(str2)) Then
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.NAMEPERSON.ToLower().Contains(str1)).ToList()
Else
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.NAMEPERSON.ToLower().Contains(str1) AndAlso c.DATE.ToString("dd-MM-yyyy").Contains(str2)).ToList()
End If
End Sub
Private Sub TextBox1_keyup(sender As Object, e As KeyEventArgs) Handles TXTNAME.KeyUp
Dim str1 = TXTNAME.Text.Trim().ToLower()
Dim str2 = TXTDATE.Text.Trim().ToLower().Replace("/ /", "")
myFilter(str1, str2)
End Sub
Private Sub TextBox2_keyup(sender As Object, e As KeyEventArgs) Handles TXTDATE.KeyUp
Dim str1 = TXTNAME.Text.Trim().ToLower()
Dim str2 = TXTDATE.Text.Trim().ToLower().Replace("/ /", "")
myFilter(str1, str2)
End Sub
End Class
Public Class Person
Public Property NAMEPERSON As String
Public Property AGE As Integer
Public Property [POSITION] As String
Public Property [DATE] As DateTime
Public Property DAYS As String
Public Property TIME() As String
Public Property OTHERS1 As String
Public Property OTHERS2 As String
End Class
Public Class PersonService
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Person.accdb;Persist Security Info=False;"
End Function
Private ReadOnly _conn As OleDbConnection
Private _connectionString As String = GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Public Function GetPERSON() As IEnumerable(Of Person)
Dim sql = "SELECT PERSON.ID AS [ID],MASTERID.NAMEPERSON AS [NAMEPERSON],PERSON.AGE AS [AGE],[PERSON.POSITION] AS [POSITION],PERSON.DATE AS [DATE],format(PERSON.DATE,'dddd') AS [DAYS],PERSON.TIME AS [TIME],PERSON.Others1 AS [OTHERS1],PERSON.Others2 AS [OTHERS2],PERSON.Others3 AS [OTHERS3] FROM PERSON INNER JOIN MASTERID ON PERSON.ID = MASTERID.ID"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Person)(sql).ToList()
End Using
End Function
Public Sub UpdatePerson(ByVal Obj As Person)
Dim sql = String.Empty
If Not String.IsNullOrEmpty(Obj.OTHERS1) AndAlso Not String.IsNullOrEmpty(Obj.OTHERS2) Then
sql = $"UPDATE PERSON INNER JOIN MASTERID ON (PERSON.ID = MASTERID.ID) Set PERSON.TIME = '{Obj.TIME}', PERSON.OTHERS1 = '{Obj.OTHERS1}', PERSON.OTHERS2 = '{Obj.OTHERS2}' WHERE MASTERID.NAMEPERSON = '{Obj.NAMEPERSON}' AND PERSON.DATE = #{Obj.DATE}# ;"
ElseIf Not String.IsNullOrEmpty(Obj.OTHERS1) Then
sql = $"UPDATE PERSON INNER JOIN MASTERID ON (PERSON.ID = MASTERID.ID) Set PERSON.TIME = '{Obj.TIME}',PERSON.OTHERS1 = '{Obj.OTHERS1}' WHERE MASTERID.NAMEPERSON = '{Obj.NAMEPERSON}' AND PERSON.DATE = #{Obj.DATE}# ;"
ElseIf Not String.IsNullOrEmpty(Obj.OTHERS2) Then
sql = $"UPDATE PERSON INNER JOIN MASTERID ON (PERSON.ID = MASTERID.ID) Set PERSON.TIME = '{Obj.TIME}',PERSON.OTHERS2 = '{Obj.OTHERS2}' WHERE MASTERID.NAMEPERSON = '{Obj.NAMEPERSON}' AND PERSON.DATE = #{Obj.DATE}# ;"
End If
' If query is generated call the Dapper Execute method.
If Not String.IsNullOrEmpty(sql) Then
Using _conn = New OleDbConnection(GetOledbConnectionString())
_conn.Execute(sql)
End Using
End If
End Sub
End Class
