vbnet mysql datagrid duplicate data

Question

dimi231080 0 Newbie Poster

12 Years Ago

Hello everybody.I am amking a programm in vb net and mysql.
My question is the following:
i have one main form which it loads ,it loads all the database data in a datagridview.When i press the button"add customer" it opens anew form where i enter the person details.Then when i click the save button on this form,it closes and the main form is again opened and here is the problem:my datagrid view it has instead of one record ,one more...for example if i already have 4 records and add one more that is five...te datagrid will show 10..

I have tried to put when the form2 button save is clicked : myDataGridView.DataSource = Nothing or myDataTable.Clear() or DataGridView.Rows.Clear()
but again the same problem.

.net:vb.net mysql

3 Contributors
7 Replies
709 Views
4 Days Discussion Span
Latest Post 12 Years Ago Latest Post by cgeier

Dani AI

Generated 10 Months Ago

The behaviour you describe (correct rows in the database but duplicated rows in the DataGridView after returning from the add form) almost always means the grid is being rebound to data that is being appended instead of replaced, or the load routine is running more than once. As noted, repeating groups (1,2,3,1,2,3) is the classic sign of double-loading. Also check for filling the same DataTable repeatedly with MySqlDataAdapter.Fill — Fill appends rows when the target table already contains data.

Quick troubleshooting and fixes tied to things already discussed here:

Verify the DB really has only one new record (run SELECT in a client).
Put a breakpoint or MessageBox at the start of your grid-loading method to see how often it runs.
If dbdataset is a class-level DataTable being reused, either Clear/Reset it before Fill, or better: create a fresh DataTable for each refresh. Filling the same DataTable repeatedly will duplicate rows in the view.
Avoid calling DataGridView.Rows.Clear() while the grid is data-bound; unbind first or replace the bound DataTable. Remove the unnecessary sda.Update(dbdataset) in the SELECT routine — it is not needed when only reading.

Safer patterns (use a single Refresh method and ExecuteNonQuery for inserts):

Public Sub RefreshGrid()
    Using conn As New MySqlConnection(connectStr)
        Using cmd As New MySqlCommand("SELECT * FROM doctorsuite.patient", conn)
            Using adapter As New MySqlDataAdapter(cmd)
                Dim dt As New DataTable()
                adapter.Fill(dt)    'fill a FRESH DataTable
                BindingSource1.DataSource = dt
                DataGridView1.DataSource = BindingSource1
            End Using
        End Using
    End Using
End Sub

Public Sub InsertPatient()
    Using conn As New MySqlConnection(connectStr)
        conn.Open()
        Using cmd As New MySqlCommand("INSERT INTO doctorsuite.patient(name,surname,...) VALUES(@name,@surname,...)",
                                      conn)
            cmd.Parameters.AddWithValue("@name", TextBox3.Text)
            ' add other parameters
            cmd.ExecuteNonQuery()   'use ExecuteNonQuery for INSERT
        End Using
    End Using
End Sub

Apply this flow: child form does a parameterized insert and closes; the main form calls one RefreshGrid() that binds a fresh DataTable. Centralize the connection string (as suggested) and consider making p_id an auto-increment field so you do not depend on TextBox1 for the primary key.

All 7 Replies

hericles 289 Master Poster

12 Years Ago

Post some code and then we can really help.
Any the rows repeated as a group? I.e. you have rows 1,2,3,4,1,2,3,4?
Because that is a clear indicator your loading code is runnning twice.
Are you using the datasouce and databind methodss or adding the items to the grid row by row in code?
Anyway, a code sample will help.

cgeier 187 Junior Poster

12 Years Ago

Try adding the following: DataGridView1.DataSource = Nothing to datagridview_load

    Public Sub datagridview_load() 'Loads the data into the datagrid table'

           'prevents duplicates
           DataGridView1.DataSource = Nothing

           mysqlconn = New MySqlConnection
            mysqlconn.ConnectionString = "server=localhost;userid=root;password='';database=doctorsuite"
           Dim sda As New MySqlDataAdapter

          Dim bsource As New BindingSource

                ...
    End Sub

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dimi231080 0 Newbie Poster · Answer 1 · 2014-06-26T22:13:41+00:00

So in the beginning when the form1 loads the sub datagridview_load() is used.As you can see later i explain this sub loads the data from my mysql database.So after that when i press an add button i have,a second form is opened "ΝΕΟΣ_ΑΣΘΕΝΗΣ".So far so good..after that when i fill all the info and press save,instead of insrting just the record i created ,it inserts this plus all the others i have already uploaded in the beginning in my datagridview when i first run the programm...Eg: i have in my database 2 records and i run the program.My datagriview will display correctly these two.After i add a third ,when i will return to form1,that is main screen,instead of 3 it will have 6!
Hope someone can help me....Thank in advance..

ps:Sorru for my code being in my language ,but i thought it would be better that way.Of course it is not all my programm ,i have uploaded the needed parts so that i will not confuse.!

dimi231080 0 Newbie Poster · Answer 2 · 2014-06-26T22:14:06+00:00

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            AMKA_combobox_load()
            name_combobox_load()

            '--------------updates the navigator when a user is added------'
            filldatasetandview()
            bindfields()
            showposition()
            '--------------------------------------------------------------'

            searchpreference_load()

            '-------------------Loads the data into the datagrid table-----'


            datagridview_load()
            '--------------------------------------------------------------'
        End Sub



    Public Sub datagridview_load() 'Loads the data into the datagrid table'

            mysqlconn = New MySqlConnection
            mysqlconn.ConnectionString = "server=localhost;userid=root;password='';database=doctorsuite"
            Dim sda As New MySqlDataAdapter

            Dim bsource As New BindingSource


            Try

                mysqlconn.Open()
                MessageBox.Show("ok")
                Dim query As String
                query = "select * from doctorsuite.patient "
                command = New MySqlCommand(query, mysqlconn)
                sda.SelectCommand = command
                sda.Fill(dbdataset)
                bsource.DataSource = dbdataset
                DataGridView1.DataSource = bsource
                sda.Update(dbdataset)

                mysqlconn.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try


        End Sub


second form:
    Imports MySql.Data.MySqlClient
    Public Class ΝΕΟΣ_ΑΣΘΕΝΗΣ

        Dim mysqlconn As MySqlConnection
        Dim command As MySqlCommand

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            '-------------------insert a new patient to database-----------'
            add_new_patient()
            '--------------------------------------------------------------'

            '--------------updates the navigator when a user is added------'
            Form1.filldatasetandview()
            Form1.bindfields()
            Form1.showposition()
            '--------------------------------------------------------------'

            Form1.DataGridView1.Rows.Clear()

            Form1.Show()
        End Sub

        Public Sub add_new_patient() 'sub for adding a new record in database'
            mysqlconn = New MySqlConnection
            mysqlconn.ConnectionString = "server=localhost;userid=root;password='';database=doctorsuite"
            Dim sda As New MySqlDataAdapter
            Dim dbdataset As New DataTable
            Dim bsource As New BindingSource
            Dim reader As MySqlDataReader
            Try
                mysqlconn.Open()
                MessageBox.Show("ok")
                Dim query As String
                query = "insert into  doctorsuite.patient(p_id,name,surname,fathername,birthdate,address,town,postcode,country,tel1,tel2,mobile1,mobile2,fax1,fax2,sex,weight,height,BMI,email,tameio,iatros_parapempon,AMKA) values ('" & TextBox1.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "','" & TextBox11.Text & "','" & TextBox12.Text & "','" & TextBox13.Text & "','" & TextBox14.Text & "','" & TextBox15.Text & "','" & TextBox16.Text & "','" & TextBox17.Text & "','" & TextBox18.Text & "','" & TextBox19.Text & "','" & TextBox20.Text & "','" & TextBox21.Text & "','" & TextBox25.Text & "','" & TextBox23.Text & "','" & TextBox24.Text & "','" & TextBox2.Text & "')"


                command = New MySqlCommand(query, mysqlconn)
                reader = command.ExecuteReader

                mysqlconn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try


        End Sub








    End Class

dimi231080 0 Newbie Poster · Answer 3 · 2014-06-26T22:15:17+00:00

So in the beginning when the form1 loads the sub datagridview_load() is used.As you can see later i explain this sub loads the data from my mysql database.So after that when i press an add button i have,a second form is opened "ΝΕΟΣ_ΑΣΘΕΝΗΣ".So far so good..after that when i fill all the info and press save,instead of insrting just the record i created ,it inserts this plus all the others i have already uploaded in the beginning in my datagridview when i first run the programm...Eg: i have in my database 2 records and i run the program.My datagriview will display correctly these two.After i add a third ,when i will return to form1,that is main screen,instead of 3 it will have 6!
Hope someone can help me....Thank in advance..

ps:Sorry for some of my code being in my language ,but i thought it would be better that way.Of course it is not all my programm ,i have uploaded the needed parts so that i will not confuse.!

dimi231080 0 Newbie Poster · Answer 4 · 2014-06-26T22:49:50+00:00

Ps:i am Using aain the sub datavrIdview-load Because i want when returnIng to tHe main screen tje datagrid to be updated live,so i do not have to close thE forma nd reopen it.And yes,the duplicate entries are in datagrid and not in my sql table,beCise i use the aBove datagridview-load sub for tje reeason i explained.

cgeier 187 Junior Poster · Answer 5 · 2014-07-01T20:47:20+00:00

I noticed that you have the following in your code: Dim dbdataset As New DataTable. The name "dbdataset" is a little misleading, because you define it as a DataTable, not a DataSet.

It is better to create a Module to declare your connection string in, which all of the other forms can use. Then if any of the information changes, you only have to change it once.

Module1.vb

Module Module1
    Public connectStr = "server=localhost;userid=root;password='';database=doctorsuite"

End Module

You can also reset the DataTable, before filling it again:

Private dbDataTable As New DataTable

        ...

Private Sub datagridview_load()

    'prevent duplicates
    DataGridView1.DataSource = Nothing

    If dbDataTable.IsInitialized Then
        dbDataTable.Reset()
    End If

    Dim mysqlconn As New MySqlConnection
    mysqlconn.ConnectionString = connectStr

    Dim sda As New MySqlDataAdapter

    Dim bsource As New BindingSource

        ...

    Try

        ...

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

End Sub

Additionally, it is best to use parameterized queries. See this post for more info.

vbnet mysql datagrid duplicate data

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers