locked
Search and delete data in DatagridView RRS feed

  • Question

  • I have a database with two linked tables
    1-I want to modify the search code in all columns without repeating the data
    2-I want to delete a row and save the process

    my project

    https://www.dropbox.com/s/728o1avffkqdajo/salling2.rar?dl=0

    Thursday, January 4, 2018 5:51 PM

Answers

  • @kunmo bu,

    The file which ahmeddc shared can be opened and run, after unzip. This picture is a run-time form.
    And we can open ahmeddc's code (see the below), but it requires a database (Access DB).
    We can register "microsoft.ace.oledb.12.0" but do not have any data. So I ask ahmeddc to provide data.

    Here's code:
    Imports System.Data.OleDb
    
    Public Class Form1
        Public con As New OleDb.OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\web_database.accdb;Jet OLEDB:Database Password=kluytiaSDqwer3210549")
        Dim DataSet1 As New DataSet
        Dim BindingSource1 As BindingSource
        Dim BindingSource2 As BindingSource
        Dim DataAdapter1 As New OleDbDataAdapter
    
    	Sub conn()
    		Dim cmd As OleDbCommand = New OleDbCommand("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail = tb2.ID_acce", con)
    		con.Open()
    		DataAdapter1 = New OleDbDataAdapter(cmd)
    		Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter1)
    		DataSet1 = New DataSet()
    		DataAdapter1.Fill(DataSet1, "tb1,tb2")
    		DataGridView1.DataSource = DataSet1
    		DataGridView1.DataMember = "tb1,tb2"
    		con.Close()
    	End Sub
    
    	Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    		Try
    			conn()
    			DataGridView1.Columns(0).ReadOnly = True
    			DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    			DataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    			DataGridView1.Sort(DataGridView1.Columns(0), System.ComponentModel.ListSortDirection.Ascending)
    			count()
    		Catch ex As Exception
    			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    		End Try
    	End Sub
    
    	Public Sub count()
    		On Error Resume Next
    		Dim records, current As Integer
    		records = BindingSource1.Count
    		current = BindingSource1.Position + 1
    		Label1.Text = current.ToString & " from " & records.ToString
    	End Sub
    
    	Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    		Try
    			If DataGridView1.RowCount <= 0 Then
    				Exit Sub
    			End If
    			DataGridView1.ClearSelection()
    			Dim a As Integer
    			DataGridView1.Rows(DataGridView1.CurrentRow.Index).Selected = True
    			a = DataGridView1.Rows.GetLastRow(DataGridViewElementStates.Selected).ToString + 1
    			DataGridView1.Rows.RemoveAt(DataGridView1.CurrentRow.Index)
    			Validate()
    			DataGridView1.Refresh()
    			DataAdapter1.Update(DataSet1, "tb1,tb2")
    			DataSet1.AcceptChanges()
    			conn()
    			count()
    			MsgBox("ok", 64 + 524288, "delete")
    		Catch ex As Exception
    			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    		End Try
    	End Sub
    
    	Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
            Try
    			If IsNumeric(TextBox1.Text) = False Then
    				Dim DataAdapter As New OleDbDataAdapter("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1,tb2 where clien_mail  LIKE '" & Trim$(TextBox1.Text) &
    				 "%' or model_mail like '" & Trim$(TextBox1.Text) &
    				  "%' or probl_acce like '" & Trim$(TextBox1.Text) &
    				   "%' or main_acce like '" & Trim$(TextBox1.Text) &
    					"%' or typ_acce like '" & Trim$(TextBox1.Text) &
    				 "%'", con)
    				con.Open()
    				DataSet1.Clear()
    				DataAdapter.Fill(DataSet1, "tb1,tb2")
    				con.Close()
    				If Me.BindingContext(DataSet1, "tb1,tb2").Count = 0 Then
    					TextBox1.Text = ""
    					con.Close()
    					Exit Sub
    				Else
    					DataSet1.Clear()
    					DataAdapter.Fill(DataSet1, "tb1,tb2")
    					count()
    					Exit Sub
    				End If
    			End If
    		Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    
        Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
    		Try
    			Validate()
    			DataGridView1.Refresh()
    			DataAdapter1.Update(DataSet1, "tb1,tb2")
    			DataSet1.AcceptChanges()
    			conn()
    			count()
    			MsgBox("ok", 64 + 524288, "save")
    		Catch ex As Exception
    			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    End Class
    
    Regards,

    Ashidacchi

    • Marked as answer by ahmeddc Saturday, July 7, 2018 1:05 PM
    Friday, January 5, 2018 10:56 AM

All replies

  • Hello,

    Please provide more details as many here will not download your project via the link provided.

    In plain English explain the search mentioned above.

    In regards to delete a row/save, please indicate where the data is being saved e.g. ms-access, sql-server and how are you accessing data e.g. DataAdapter, DataTable(s) without a DataSet (using a connection and command), TableAdapter etc.

    More details are best.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 4, 2018 10:54 PM
  • Hi,

    Thank you sharing your project. But I would like to ask you to:
    (1) share data (ole.db).
        (please remember to modify/edit your vital data before sharing)
    (2) specify you issue more clearly.
        Does error/exception occur in run-time?  Can't you remove rows in DataGridView? etc.

    Regards,

    Ashidacchi

    Thursday, January 4, 2018 11:06 PM
  • Hi ahmeddc,

    I download your project and unzip this, but I get one unavailable project, there is no solution, please provide your code directly here.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 5, 2018 10:13 AM
  • @kunmo bu,

    The file which ahmeddc shared can be opened and run, after unzip. This picture is a run-time form.
    And we can open ahmeddc's code (see the below), but it requires a database (Access DB).
    We can register "microsoft.ace.oledb.12.0" but do not have any data. So I ask ahmeddc to provide data.

    Here's code:
    Imports System.Data.OleDb
    
    Public Class Form1
        Public con As New OleDb.OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\web_database.accdb;Jet OLEDB:Database Password=kluytiaSDqwer3210549")
        Dim DataSet1 As New DataSet
        Dim BindingSource1 As BindingSource
        Dim BindingSource2 As BindingSource
        Dim DataAdapter1 As New OleDbDataAdapter
    
    	Sub conn()
    		Dim cmd As OleDbCommand = New OleDbCommand("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail = tb2.ID_acce", con)
    		con.Open()
    		DataAdapter1 = New OleDbDataAdapter(cmd)
    		Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter1)
    		DataSet1 = New DataSet()
    		DataAdapter1.Fill(DataSet1, "tb1,tb2")
    		DataGridView1.DataSource = DataSet1
    		DataGridView1.DataMember = "tb1,tb2"
    		con.Close()
    	End Sub
    
    	Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    		Try
    			conn()
    			DataGridView1.Columns(0).ReadOnly = True
    			DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    			DataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    			DataGridView1.Sort(DataGridView1.Columns(0), System.ComponentModel.ListSortDirection.Ascending)
    			count()
    		Catch ex As Exception
    			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    		End Try
    	End Sub
    
    	Public Sub count()
    		On Error Resume Next
    		Dim records, current As Integer
    		records = BindingSource1.Count
    		current = BindingSource1.Position + 1
    		Label1.Text = current.ToString & " from " & records.ToString
    	End Sub
    
    	Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    		Try
    			If DataGridView1.RowCount <= 0 Then
    				Exit Sub
    			End If
    			DataGridView1.ClearSelection()
    			Dim a As Integer
    			DataGridView1.Rows(DataGridView1.CurrentRow.Index).Selected = True
    			a = DataGridView1.Rows.GetLastRow(DataGridViewElementStates.Selected).ToString + 1
    			DataGridView1.Rows.RemoveAt(DataGridView1.CurrentRow.Index)
    			Validate()
    			DataGridView1.Refresh()
    			DataAdapter1.Update(DataSet1, "tb1,tb2")
    			DataSet1.AcceptChanges()
    			conn()
    			count()
    			MsgBox("ok", 64 + 524288, "delete")
    		Catch ex As Exception
    			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    		End Try
    	End Sub
    
    	Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
            Try
    			If IsNumeric(TextBox1.Text) = False Then
    				Dim DataAdapter As New OleDbDataAdapter("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1,tb2 where clien_mail  LIKE '" & Trim$(TextBox1.Text) &
    				 "%' or model_mail like '" & Trim$(TextBox1.Text) &
    				  "%' or probl_acce like '" & Trim$(TextBox1.Text) &
    				   "%' or main_acce like '" & Trim$(TextBox1.Text) &
    					"%' or typ_acce like '" & Trim$(TextBox1.Text) &
    				 "%'", con)
    				con.Open()
    				DataSet1.Clear()
    				DataAdapter.Fill(DataSet1, "tb1,tb2")
    				con.Close()
    				If Me.BindingContext(DataSet1, "tb1,tb2").Count = 0 Then
    					TextBox1.Text = ""
    					con.Close()
    					Exit Sub
    				Else
    					DataSet1.Clear()
    					DataAdapter.Fill(DataSet1, "tb1,tb2")
    					count()
    					Exit Sub
    				End If
    			End If
    		Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    
        Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
    		Try
    			Validate()
    			DataGridView1.Refresh()
    			DataAdapter1.Update(DataSet1, "tb1,tb2")
    			DataSet1.AcceptChanges()
    			conn()
    			count()
    			MsgBox("ok", 64 + 524288, "save")
    		Catch ex As Exception
    			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    End Class
    
    Regards,

    Ashidacchi

    • Marked as answer by ahmeddc Saturday, July 7, 2018 1:05 PM
    Friday, January 5, 2018 10:56 AM