Trouble inserting data

[shinichi90]

Hi!!

I'm having problems inserting data into mysql through vb.net.

Imports System
Imports System.Data
Imports MySql.Data.MySqlClient

Public Class Form2

    Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click

        Me.Hide()
        Form1.Show()

    End Sub

    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
        Dim conn As MySqlConnection = New MySqlConnection()
        conn.ConnectionString = "Database=fyp;Data Source=localhost;User Id=root;Password=fyp"
        Dim myCommand As New MySqlCommand
        Dim sql As String

        sql = "insert into mcq (Question,OptionA,OptionB,OptionC,OptionD) values ('" & questionObj.Text & "','" & optionA.Text & "','" & optionB.Text & "','" & optionC.Text & "','" & optionD.Text & "')"

        conn.Open()

        myCommand.Connection = conn
        myCommand.CommandText = sql
        conn.Close()
        conn.Dispose()

    End Sub

I've done something similiar like this using IBM DB2 and vb.net 2008 and it works. Currently, I'm using vb.net 2010.

The connection to the database is fine I think because I was able to retrieve the table to be viewed in vb2010. Is the syntax somehow wrong?

Thank you.

Regards,
Shinichi90

 

[jmcilhinney]

You say that you have a problem but you don't actually tell us what the problem is. You show us the code but you don;t tell us what actually happens. If an exception is thrown then you need to tell us what the error message is and which line it occurs on. If no exception occurs then you need to tell us how the actual behaviour differs from what you expect.

That said, the first thing you should do is rewrite your code to use parameters rather than string concatenation. For more info, follow the Blog link in my signature and check out my post on ADO.NET Parameters.

 

[shinichi90]

Ah!

I'm sorry. I didn't realise I did not state what the problem was...

The problem was that when I insert the data, nothing happened. The table was not updated.

But I managed to solved it. I added this: myCommand.ExecuteNonQuery() before the conn.Close(). Is this somewhat bad?

 

[jmcilhinney]

No, that's not bad. That's exactly what you should be doing. I didn;t notice that that was missing because I didn't know what I was looking for because you didn;t specify what the actual problem was. It's the ExecuteNonQuery method that actually executes the SQL INSERT statement. Without that, you aren't actually saving anything, which is why you didn;t see anything saved.

Regardless, you should still change your code to use parameters. It's the proper way to do it for more than one reason and the sooner that you get into good habits the better.