更新MySQL数据库的问题.... 帮助?[英] Problem updating MySQL database.... Help?

本文是小编为大家收集整理的关于更新MySQL数据库的问题.... 帮助?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

好的,所以我有一个名为 in-out 的 Mysql dsetup,我编写了一个 vb.net 客户端程序来从数据库中的表中获取信息,将其显示在数据网格视图中,然后允许用户对其进行编辑信息并在服务器上更新.

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

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

在我开始这一步之前,我在检索类似

的信息时不断收到错误消息<块引用>

主键的重复默认条目 = '0'

这意味着表中与此错误相关的列不能有多个默认值"0",但现在已经消失了......(即使我没有更改任何内容)

这是重新创建我的数据库布局的脚本.只需在 MySql WorkBench 或 MySql Query Browser 中运行它(或者你用来管理你的 Sql 数据库的任何东西).请告诉我我做错了什么,因为我需要在 12 月之前完成这个程序!(从现在起 4 个月)

http://www.megaupload.com/?d=VA639MP5

这是我的更新代码:(以防万一问题出在我的程序而不是数据库上)

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
  • 服务器名称:"本地主机"
  • Sql 管理器:MySql Workbench 5.2.34 CE

如果您需要任何其他详细信息,请告诉我,因为现在我想不出您需要提供任何帮助的其他任何事情:P

谢谢!

推荐答案

您好像遇到了某种事务问题...

尝试在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 相应的列...

本文地址:https://www.itbaoku.cn/post/597353.html

问题描述

Ok so 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). Please tell me what I'm doing wrong because I need to get this program done by December! (4 months from now)

http://www.megaupload.com/?d=VA639MP5

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

If you need any other details just let me know because right now I can't think of anything else that you would need to provide any assistance :P

Thanks!

推荐答案

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

相关标签/搜索