MySQL VB.Net在MySQL中删除和更新数据的代码是什么？（使用参数化查询）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27824638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the code for VB.Net to delete and update data in MySQL ? (Use parameterized Query)
提问by user3169200
What is the code for VB.Net to delete and update data in MySQL ? (Use parameterized Query)
VB.Net在MySQL中删除和更新数据的代码是什么？（使用参数化查询）
This is my code for Update :
这是我的更新代码：
Dim Query As String
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=;")
con.Open()
Query = "UPDATE userreg SET UserName ='" + txtUserName.Text + "',"
Query = Query + "Age = " + txtAge.Text
Query = Query + " WHERE idUserReg = " + txtUserRegId.Text
Dim cmd As MySqlCommand = New MySqlCommand(Query, con)
MsgBox(Query)
Dim i As Integer = cmd.ExecuteNonQuery()
If (i > 0) Then
lblMsg.Text = "Record is Successfully Updated"
Else
lblMsg.Text = "Record is not Updated"
End If
con.Close()
And this is for Delete Code :
这是用于删除代码：
Dim Query As String
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=;")
con.Open()
Query = "Delete FROM userreg WHERE idUserReg =" + txtUserRegId.Text
Dim cmd As MySqlCommand = New MySqlCommand(Query, con)
MsgBox(Query)
Dim i As Integer = cmd.ExecuteNonQuery()
If (i > 0) Then
lblMsg.Text = "Record is Successfully Deleted"
Else
lblMsg.Text = "Record is not Deleted"
End If
con.Close()
But when I want update and delete data, there is no change.
但是当我想要更新和删除数据时，没有任何变化。
回答by Leopold Stotch
I'll help you out since I see from your other thread you're struggling.
我会帮助你，因为我从你的另一个线程中看到你正在挣扎。
This function will Update/Delete from the DB using parameterized queries:
此函数将使用参数化查询从数据库更新/删除：
Public Function ParameterizedNonQueryCommand(ByRef NonQuery As String, ByVal Parameters As List(Of MySqlParameter), _
Optional ByVal Connection As MySql.Data.MySqlClient.MySqlConnection = Nothing) As Integer
Dim comm As New MySql.Data.MySqlClient.MySqlCommand(NonQuery, Connection)
Try
For Each param As MySql.Data.MySqlClient.MySqlParameter In Parameters
comm.Parameters.Add(param)
Next
If comm.Connection.State <> ConnectionState.Open Then comm.Connection.Open()
comm.Prepare()
Return comm.ExecuteNonQuery()
Catch ex As Exception
Return -1
Finally
comm.Connection.Close()
comm.Dispose()
End Try
End Function
Example usage to delete the user with UserID 1 from a table:
从表中删除用户 ID 为 1 的用户的示例用法：
Dim query as string = "DELETE FROM USERS WHERE USERID = ?UserID"
Dim Params As New List(Of MySqlParameter)
Params.Add(New MySqlParameter("UserID", 1))
Dim oConnection as MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=;")
ParameterizedNonQueryCommand(query, Params, oConnection)
Basically, the function accepts the query as a string parameter. In your query string, where you need to add a value, just pass in a parameter placeholder with '?', then you specify the value to replace it with in a MySqlParameter.
基本上，该函数接受查询作为字符串参数。在需要添加值的查询字符串中，只需传入带有“?”的参数占位符，然后在 MySqlParameter 中指定要替换的值。
