VB.NET mySQL insert command

Question

this is my DB : enter image description here

I am trying to add to my presence table a new row, here it is : I don't get any VB errors nor mySQL errors, so I guess I am missing something important to add here.

Can you guys help me please ? Thanks in advance

  Try

        Dim SqlQuery As String = "INSERT INTO presence(id_presence,id,hours,date) " & _
            "SELECT DISTINCT @Id_presence,@Id,@Hours,@Date FROM presence"

        Using con = New MySqlConnection("Server = localhost;Database = accounts; Uid=root; Pwd = password")
            Using SQLcmd = New MySqlCommand(SqlQuery, con)
                con.Open()
                SQLcmd.Parameters.Add(New MySqlParameter("@Id_presence", TextBox1.Text))
                SQLcmd.Parameters.Add(New MySqlParameter("@Id", TextBox2.Text))
                SQLcmd.Parameters.Add(New MySqlParameter("@Hours", TextBox3.Text))
                SQLcmd.Parameters.Add(New MySqlParameter("@Date", TextBox4.Text))
            End Using


        End Using




    Catch ex As Exception

    End Try

Are you trying to duplicate an existing record here?

Steve
– Steve

2014-04-13 09:56:38 +00:00
Commented Apr 13, 2014 at 9:56 — Steve
– Steve, Commented Apr 13, 2014 at 9:56

Steve · Accepted Answer · 2014-04-13 10:09:04Z

3

The correct syntax for adding a NEW record to your table should be

 Dim SqlQuery As String = "INSERT INTO presence(id_presence,id,hours,date) " & _
                          "VALUES (@Id_presence,@Id,@Hours,@Date)"

And, of course, every command should be executed otherwise it is just a uselese lines of code.
You are missing this line after the creation of the parameters.

  SQLcmd.ExecuteNonQuery()

From your image is not possible to be sure, but if the idpresence is an AUTOINCREMENT field, then you should not try to set its value with your command and parameter but let the database do it for you. (Duplicate keys problems could arise if more than one user inserts records at the same time)

As a last thing, your parameters' type are not specified so they don't match the underlying datatable schema. You could use AddWithValue and converting the input textbox values to the correct database type

SQLcmd.Parameters.AddWithValue("@Id_presence", Convert.ToInt32(TextBox1.Text))
SQLcmd.Parameters.AddWithValue("@Id", Convert.ToInt32(TextBox2.Text))
SQLcmd.Parameters.AddWithValue("@Hours", Convert.ToInt32(TextBox3.Text))
SQLcmd.Parameters.AddWithValue("@Date", Convert.ToDateTime(TextBox4.Text))

edited Apr 13, 2014 at 10:09

answered Apr 13, 2014 at 9:58

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ExtremeSwat Over a year ago

Indeed my id_presence has auto-increment. Thanks a lot, you're the vb & mySQL God.

Adel · Accepted Answer · 2014-04-13 09:55:59Z

1

Add SQLcmd.ExecuteNonQuery(); to execute it before End Using

Example

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using 
End Sub

reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

answered Apr 13, 2014 at 9:55

Adel

1,46815 silver badges18 bronze badges

Collectives™ on Stack Overflow

VB.NET mySQL insert command

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related