Error updating MySQL database: DUPLICATE DEFAULT ENTRY FOR PRIMARY KEY = '0'

I have a MySQL dsetup called in-out and I have written a vb.NET client program to get information from the table in the database, display it in a data grid view, and then allow to user to edit this information and update it on the server.

Right now I am hosting the server on my Gateway laptop and also connecting to it from the same laptop therefore I'm using localhost as the server name. My problem is that when I go into the program and change the information and click update, nothing happens... The information stays the same yet there is no sign of an error, syntax failure, or program crash.

I've tried running this on another computer in my house and I get the same results. I can access the information without a hitch but updating it is where I run into trouble. If there was a problem with my code it would have displayed some sort of error or asked me to debug my script, which would have made it a lot easier to solve, therefore I am certain that it has something to do with my database.

Before I got to this step, I kept getting an error when retrieving the information that said something like

DUPLICATE DEFAULT ENTRY FOR PRIMARY KEY = '0'

Which means that the columns in the table related to this error cannot have more than one default value of '0', but that's gone n开发者_开发问答ow... (Even though I didn't change anything)

Here is the script that will recreate my database layout. Just run it in MySQL WorkBench or MySQL Query Browser (or what ever your using to manage your SQL Database).

Here's my update code: (just in case the problem lies in my program not the database)

Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click
   Dim conn As New MySqlConnection
    Dim myCommand As New MySqlCommand

    '#######
    conn.ConnectionString = "server=" & frmLogin.txtserver.Text & ";" _
& "user id=" & frmLogin.txtusername.Text & ";" _
& "password=" & frmLogin.txtpassword.Text & ";" _
& "database=in_out"
    '#######

    myCommand.Connection = conn
    myCommand.CommandText = "INSERT INTO event(user_id, message_id, timestamp, status, creator)" _
     & "VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)"

    myCommand.Parameters.AddWithValue("?UserID", myUserID)
    myCommand.Parameters.AddWithValue("?MessageID", cbomessage.SelectedValue)
    myCommand.Parameters.AddWithValue("?Status", cbostatus.SelectedItem)
    myCommand.Parameters.AddWithValue("?Creator", myUserID)

    Try
        conn.Open()
        myCommand.ExecuteNonQuery()
    Catch myerror As MySqlException
        MsgBox("There was an error updating the database: " & myerror.Message)
    End Try
    refreshStatus(dgvstatus)

End Sub

Additional Details:

• OS: Windows 7 Professional x64
• Software: Visual Basic 2010 Express
• Server Name: 'Localhost'
• SQL Manager: MySQL Workbench 5.2.34 CE

---

Seems that you have some sort of transaction problem going on...

try to add myCommand.Connection.Close(); after the ExecuteNonQuery()

EDIT - as per comment:

Some links to learn SQL:

• http://www.w3schools.com/sql/default.asp
• http://www.sqlcourse.com/index.html
• http://www.sql-tutorial.net/
• http://www.mysqltutorial.org/

EDIT 2:

UPDATE event SET
timestamp = NOW(), 
status = ?Status 
WHERE user_id = ?UserID AND message_id = ?MessageID AND creator = ?Creator;

Since there is not enough details about the data model the above UPDATE statement assumes that the columns user_id and message_id and creator together identify a row uniquely... and update the timestamp and status columns accordingly...