mysql - 从 VB.Net 读取和写入 SQL 信息

Question

我将我的 vb.net 项目链接到一个唯一的 SQL 数据库，该数据库包含预定义的电子邮件地址列表。我的 vb 表单包含“名字”、“姓氏”和“电子邮件”文本框。

如何对 vb.net 进行编程以查找数据库中“电子邮件”文本框中的文本，并将名字和姓氏文本框值添加到与找到的电子邮件字段位于同一行的相应列中？ (填补空白)

到目前为止我的代码:

' Initiate SQL Connection for db4free.net on port: 3306
    MySqlConnection = New MySqlConnection
    MySqlConnection.ConnectionString = "server=db4free.net; Port=3306; user id=username; password=password; database=databasename"
    Try
        MySqlConnection.Open()
    Catch ex As Exception
        MsgBox("The server could not be reached, check that you have internet connectivity and try again.", MsgBoxStyle.Critical, "Connection Error")
    End Try

        --- SQL DATABASE CONNECTION --- '
        Dim Myadaptor As New MySqlDataAdapter
        Dim sqlquery = "SELECT * FROM registration WHERE email='" & emailTextBox.Text & "';"
        Dim command As New MySqlCommand
        command.Connection = MySqlConnection
        command.CommandText = sqlquery
        Myadaptor.SelectCommand = command
        Dim Mydata As MySqlDataReader
        Mydata = command.ExecuteReader

        ' SQL Entry Validation
        If Mydata.HasRows = 0 Then
            MsgBox("Please enter a valid E-Mail address", MsgBoxStyle.Critical, "Invalid Details")
        Else
           --- THE ANSWER TO MY QUESTION (write firstname and lastname into row based on email) ---

提前谢谢您，(我才刚刚开始学习 SQL)。

Answer 1

你会想做这样的事情:

If Mydata.HasRows = 0 Then
    MsgBox("Please enter a valid E-Mail address", MsgBoxStyle.Critical, "Invalid Details")
    Mydata.Close()
Else
    Mydata.Close()
    Dim command As New MySqlCommand
    command.Connection = MySqlConnection
    command.ConnectionText = "UPDATE `registration` SET `firstname` = @firstname,`lastname` = @lastname WHERE `email` = @email"
    command.Prepare()
    command.Parameters.AddWithValue("@firstname", firstnameTextBox.Text)
    command.Parameters.AddWithValue("@lastname", lastnameTextBox.Text)
    command.Parameters.AddWithValue("@email", emailTextBox.Text)
    command.ExecuteNonQuery()
End If

您可能需要调整 SQL 或对 UI 元素的引用以匹配您的设置。

我还将参数绑定(bind)到 SQL 中，而不是通过连接参数来创建字符串，以防止 SQL Injection .