更新 MySQL 数据库时出错：主键的重复默认条目 = '0'

发布于 2024-12-01 11:45:37 字数 2008 浏览 1 评论 0原文

我有一个名为 in-out 的 MySQL dsetup，并且编写了一个 vb.NET 客户端程序来从数据库中的表中获取信息，将其显示在数据网格视图中，然后允许用户编辑此信息并在服务器上更新它。

现在，我将服务器托管在我的网关笔记本电脑上，并从同一台笔记本电脑连接到它，因此我使用 localhost 作为服务器名称。我的问题是，当我进入程序并更改信息并单击更新时，没有任何反应...信息保持不变，但没有错误、语法失败或程序崩溃的迹象。

我尝试在我家的另一台计算机上运行它，得到了相同的结果。我可以顺利地访问这些信息，但更新它是我遇到麻烦的地方。如果我的代码有问题，它会显示某种错误或要求我调试我的脚本，这会让解决起来更容易，因此我确信它与我的数据库有关。

在进行这一步之前，我在检索类似以下内容的信息时不断收到错误

主键的重复默认条目=“0”

这意味着与此错误相关的表中的列不能有多个默认值 '0'，但现在这种情况已经消失了......（即使我没有更改任何内容）

这是将重新创建我的数据库布局的脚本。只需在 MySQL WorkBench 或 MySQL 查询浏览器（或者您用来管理 SQL 数据库的任何工具）中运行它即可。

这是我的更新代码：（以防万一问题出在我的程序而不是数据库）

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

其他详细信息：

操作系统：Windows 7 Professional x64
软件：Visual Basic 2010 Express
服务器名称：'Localhost'
SQL 管理器：MySQL Workbench 5.2.34 CE

原文

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 now... (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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ˉ厌 2024-12-08 11:45:37

似乎您遇到了某种事务问题...

尝试在 ExecuteNonQuery() 编辑之后添加 myCommand.Connection.Close();

- 作为每个评论：

一些学习 SQL 的链接：

编辑2：

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

由于没有足够的有关数据模型的详细信息，上述 UPDATE 语句假定列 user_id 和 message_id 和 >creator 一起唯一地标识一行...并相应地更新 timestamp 和 status 列...

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:

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...

回复收藏 0 原文

~没有更多了~