Could not insert the data using vb.net into Mysql

Question

I am trying to insert data into MySQL using VB.

When I use these textboxes to insert the data the data gets added, but I don't want to enter the text into textboxes but directly add the underlying information just by press of button (update). It is giving a syntax error to check MySQL version. It it a query error? I don't know how to do this. Please help. Here is my code.

 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Mysqlconn = New MySqlConnection

        Mysqlconn.ConnectionString = "server=localhost;userid=root;port=85;password=*****;database=data"
        Dim reader As MySqlDataReader


        Try
            Mysqlconn.Open()
            Dim query As String
            query = "INSERT INTO 'data'.'etable'('eid','Name','Surname','Age')values('7','Andy','Roddick','35')"
            command = New MySqlCommand(query, Mysqlconn)
            reader = command.ExecuteReader


            MessageBox.Show("Data Saved")

            Mysqlconn.Close()

        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
            Mysqlconn.Dispose()

        End Try

    End Sub
End Class

Why are not including "Run code snippet" with a compiled language? Don't you see that it doesn't work. — user2480047
– user2480047, Commented Nov 30, 2015 at 16:37

Joel Coehoorn · Accepted Answer · 2015-11-30 17:07:15Z

Try this, which fixes some other issues and potential issues as well:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    Dim query As String = "INSERT INTO `data`.`etable`(eid,Name,Surname,Age)" & _
                         " VALUES (@eid, @Name, @Surname, @Age)"
    Using con As New MySqlConnection("server=localhost;userid=DONT_USE_ROOT!;port=85;password=*****;database=data"), _
          cmd As New MySqlCommand(query, con)

        'Guessing at parameter types/lengths here.
        cmd.Parameters.Add("@eid", MySqlDbType.Int32).Value = 7
        cmd.Parameters.Add("@Name", MySqlDbType.VarChar, 20).Value = "Andy"
        cmd.Parameters.Add("@Surname", MySqlDbType.VarChar, 25).Value =  "Roddick"
        cmd.Parameters.Add("@Age", MySqlDbType.Int32).Value = 35

        conn.Open()
        If cmd.ExecuteNonQuery() > 0 Then MessageBox.Show("Data Saved")

    End Using   
End Sub

Notice I also removed the exception handler. The Dispose() call in your old handler is now no longer needed (the Using block takes care of this), and I tend to advocate handling exceptions at a higher level than where they are thrown... though in this case you're already in the button event. What I really recommend here is moving the database code to it's own class, so this would all be in a separate method in that class. Then you could still have your exception handler here in the button click event, and the only thing in the Try block would be calling that method.

It's also very important to be in the habit of using query parameters for data that goes into sql queries, in order to prevent sql injection attacks. What you had wasn't vulnerable to attack yet, but it didn't lend any confidence that it wouldn't be vulnerable soon.

Hey Joel in this case if I ought to add all the ages of all the employees in the database and then display it in the VB through a messagebox or textbox then what changes will I have to do(through click of a button)

Joel Coehoorn · Accepted Answer · 2015-11-30 16:53:38Z

1

The correct character to enclose table name and field names is the backtick not the single quote. Use ALT+096 on your numeric keypad to insert it.

 query = "INSERT INTO  `data`.`etable`(`eid`,`Name`,`Surname`,`Age`) "  & _
         "values('7','Andy','Roddick','35')"

Said that, check if you database table has the field eid and Age of type varchar. If the fields are numeric (as the name seems to imply) then your query should be changed to

 query = "INSERT INTO  `data`.`etable`(`eid`,`Name`,`Surname`,`Age`) "  & _
         "values(7,'Andy','Roddick',35)"

You code also contains some bad practice that need to be removed to avoid future problems

 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

     Dim query As String
     query = "INSERT INTO  `data`.`etable`(`eid`,`Name`,`Surname`,`Age`) "  & _
             "values(7,'Andy','Roddick',35)"
     Using Mysqlconn = New MySqlConnection
     Using command = New MySqlCommand(query, Mysqlconn)
        Mysqlconn.ConnectionString = "server=localhost;userid=root;port=85;password=*****;database=data"
        Try
            Mysqlconn.Open()
            Dim rowsAffected = command.ExecuteNonQuery()
            if rowsAffected > 0 Then 
               MessageBox.Show("Data Saved")
            End If
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        End Try
    End Using
    End Using
End Sub

First enclose all disposable objects in a Using statement to be sure that they are closed and disposed also in case of exceptions then do not use ExecuteReader for INSERT, UPDATE and DELETE queries, instead the correct method to use is ExecuteNonQuery

edited Nov 30, 2015 at 16:53

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

answered Nov 30, 2015 at 16:36

Steve

217k22 gold badges242 silver badges296 bronze badges

5 Comments

AnishLinux Over a year ago

Thanks really,actually I am new to this database thing,I am learning Vb as I have to use gambas3 for application development.This was a silly error(single quote) though....thanks and it worked.

Steve Over a year ago

Look also to the other answer. In your case you use constants so it is acceptable to write them directly in the command text string. If you have user input to pass then remember to use always parameters as explained there

AnishLinux Over a year ago

Hey Steve in this case if I ought to add all the ages of all the employees in the database and then display it in the VB through a messagebox or textbox then what changes will I have to do(through click of a button)

Steve Over a year ago

I am sorry but I can't help you on that. Never worked with that. For the second question, I suggest to post a new question. You will get more attention than posting a comment in answered question.

AnishLinux Over a year ago

Hey steve....I have phrased a new question which says==> "I want to add the column field in mysql and then send the result to VB to display it".Please check it and let me know if you have any solution.....Thanks

Collectives™ on Stack Overflow

Could not insert the data using vb.net into Mysql

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related