none
INSERT,UPDATE & DELETE ON MYSQL DATABASE IN ONE BUTTON RRS feed

  • Question

  • Hello again,

    I would like to ask what's the correct code on this? I have a button in a form and I want this button to the the following functions:

    If the data is already exist on the data base it will update the changes made and delete the old one but

    If the data is not exist it will insert on database.

    Thank you for your help.


    • Edited by IAN_code Thursday, June 29, 2017 9:07 AM wrong input
    Thursday, June 29, 2017 9:06 AM

All replies

  • You need to give us more than what you wrote as there are at least a handful of ways to get where you are now. If posting code use the code button.


    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, June 29, 2017 9:46 AM
    Moderator
  • There are a few ways to handle this in a single SQL statement. See the below link for possible solutions:

    https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists

    I'm assuming that you know how to use the MySQL .NET Provider to execute a SQL statement, but if not there are examples in the online documentation:

    https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, June 29, 2017 12:36 PM
  • Im probably doing this the long way, I've pretty much just started writing everything as though it will be databound. You will need the mysql .net connector installed and added as reference, then replace sql connections with mysqlconnections.

    Private Sub button_UpdateSettings_Click(sender As Object, e As EventArgs) Handles button_UpdateSettings.Click Using Conn As New SqlConnection("Data Source=SQLTEST2\STD;Initial Catalog=KPMIT;Integrated Security=True") Using SettingsDA As New SqlDataAdapter("Select * from settings where id=" & TextBox1.Text, Conn) With SettingsDA Dim SettingsDT As New DataTable .MissingSchemaAction = MissingSchemaAction.AddWithKey .FillSchema(SettingsDT, SchemaType.Source) .Fill(SettingsDT) If SettingsDT.Rows.Count = 0 Then ''Add the record Dim NewRow As DataRow = SettingsDT.NewRow NewRow("SomeColumn") = "Some Value" SettingsDT.Rows.Add(NewRow) Dim SettingsCB As New SqlCommandBuilder(SettingsDA) .Update(SettingsDT) SettingsDT.Rows.Clear() .Fill(SettingsDT) Else ''Update the record SettingsDT(0)("SomeColumn") = "SomeValue"

                            Dim SettingsCB As New SqlCommandBuilder(SettingsDA) .Update(SettingsDT) End If End With End Using End Using End Sub



    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi



    • Edited by Gtripodi Thursday, June 29, 2017 3:31 PM
    Thursday, June 29, 2017 3:03 PM
  • @Gtripodi,

    You don't need a DataAdapter. When setting field values use .SetField or modifying use .Field(Of T)(FieldName) to get the value.

    Also when creating a new row you can set values when constructing the row rather than setting field values after the fact.


    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, June 29, 2017 4:02 PM
    Moderator
  • Karen, How would you fill or update the Datatable w/o an adapter? I do get you can use command/params to accomplish what he wants.

    Perhaps you could share your preferred method of updating a sql table using a datatable. If you had to do it using 1 tiny sub and no functions or wacky calls to/from variables or crazy formatting, how would you connect to, bind, and update the sql table from a datatable/bindingsource


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, June 29, 2017 5:54 PM
  • Hi IAN_code,

    I suggest you use MySqlCommandBuilder to update the data, please refer to:

    Dim ds As DataSet
        Dim cmd As MySqlCommandBuilder
        Dim sqldata As MySqlDataAdapter
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                cmd = New MySqlCommandBuilder(sqldata)
                sqldata.Fill(ds, "Test1")
                MessageBox.Show("Update success!")
            Catch ex As Exception
    
            End Try
        End Sub
    
        Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loadata()
        End Sub
        Private Sub loadata()
            Try
                Dim ConnectionString As String = "Server=127.0.0.1;Database=test;Uid=root;Pwd=;"
                Dim con As New MySqlConnection(ConnectionString)
                con.Open()
                sqldata = New MySqlDataAdapter("select ID, columnname,columnsex, columnage from test2", con)
                ds = New DataSet()
                sqldata.Fill(ds, "Test1")
                DataGridView1.DataSource = ds.Tables(0)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    

    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, June 30, 2017 7:20 AM
    Moderator
  • Hi Chery, thanks for the reply but this is for update only. Can you please give me an idea on both updating and inserting in one button function only?Thank you again
    Monday, July 3, 2017 2:08 AM
  • Hi IAN_code,

    Sorry for the late reply.

    Please refer to my post before, you can update and insert new row in the datagridview, you can see it from the gif that I provided before.

    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.

    Wednesday, July 5, 2017 1:31 AM
    Moderator
  • Hi Chery, thanks for the reply but this is for update only. Can you please give me an idea on both updating and inserting in one button function only?Thank you again
    Did you look at the links I posted? There is an example of how to do this.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 5, 2017 3:18 AM
  • Hi IAN_code,

    Do you want to do this? I put three textbox, one button in form.

    I detect the data by ID, if exist, I delete the old data, and insert new one, if don't exist, I just insert new row.

     Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        End Sub
        Private Sub fun()
            Dim dt As New DataTable
            If TextBox1.Text = "" Then
                MessageBox.Show("Please enter ID!")
            Else
                Dim str As String = "Server=127.0.0.1;Database=test;Uid=root;Pwd=;"
                Dim con As New MySqlConnection(str)
                Dim sql As String = "select count(*) from test3 where ID=@ID"
                Try
                    con.Open()
                    Dim cmd As New MySqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@ID", Int32.Parse(TextBox1.Text.ToString()))
                    Dim sqlread As New MySqlDataAdapter(cmd)
                    sqlread.Fill(dt)
                    If dt(0)(0) > 0 Then
                        Dim result As Integer = MessageBox.Show("This Id has exist, do you want to delete this data and enter new one?", "Message", MessageBoxButtons.YesNo)
                        If result = DialogResult.Yes Then
                            Dim sql1 As String = "delete from test3 where ID=@ID"
                            Dim cmd1 As New MySqlCommand(sql1, con)
                            cmd1.Parameters.AddWithValue("@ID", Int32.Parse(TextBox1.Text.ToString()))
                            cmd1.ExecuteNonQuery()
                        Else
                            Return
                        End If
    
                    End If
                    Dim sql2 As String = "insert into test3 value (@ID,@FirstName,@LatsName)"
                    Dim cmd2 As New MySqlCommand(sql2, con)
                    cmd2.Parameters.AddWithValue("@ID", Int32.Parse(TextBox1.Text.ToString()))
                    cmd2.Parameters.AddWithValue("@FirstName", TextBox2.Text.ToString())
                    cmd2.Parameters.AddWithValue("@LatsName", TextBox3.Text.ToString())
                    cmd2.ExecuteNonQuery()
                    con.Close()
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End If
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            fun()
        End Sub

    Best Regrads,

    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.


    Wednesday, July 5, 2017 6:47 AM
    Moderator