我正在尝试更新MSAccess中的表,该表的字段的数据类型为“文本”。但是,当我运行代码时,它在UPDATE语句中显示sysntax错误。这是我的vb代码:

昏暗的用户作为字符串
        暗淡的密码作为字符串
        Dim dtT作为新数据表

    Dim cmd As New OleDb.OleDbCommand

    user = Me.TextBox1.Text
    password = Me.TextBox2.Text


    If Not cnn.State = ConnectionState.Open Then

        cnn.Open()
    End If
    Try
        Dim daA As New OleDb.OleDbDataAdapter("SELECT *FROM adlogin WHERE password='" & Me.TextBox2.Text & "'", cnn)

        ' MsgBox("STUDENT SAVED!!", MsgBoxStyle.MsgBoxRight)

        daA.Fill(dtT)
        Me.DG1.DataSource = dtT


        'password = DG1.Item(0, 0).Value
        'ss1 = DG1.Item(1, 0).Value

        If user = DG1.Item(1, 0).Value And password = DG1.Item(0, 0).Value Then


            cmd.Connection = cnn
            cmd.CommandText = "UPDATE adlogin SET password ='" & Me.TextBox3.Text & "' WHERE user =" & Me.TextBox1.Text
            System.Console.WriteLine(cmd.CommandText)

            Dim result = MsgBox("Change Administrator password!!! Are you sure?", MsgBoxStyle.YesNo)

            If result = DialogResult.Yes Then
                cmd.ExecuteNonQuery()
                MsgBox("PassWord Changed", MsgBoxStyle.MsgBoxRight)
                Panel1.Hide()
            End If


        Else
            MsgBox("INVALID PASSWORD", MsgBoxStyle.Critical)

        End If
        cnn.Close()

    Catch ex As Exception
        MsgBox("INVALID PASSWORD " & ex.Message, MsgBoxStyle.Critical)
    End Try

最佳答案

切勿使用字符串连接来创建SQL命令。始终使用参数
这将解决两个问题:
在字符串中使用单引号,但最重要的是,避免使用SQL Injection Attacks

Dim cmd As New OleDb.OleDbCommand
user = Me.TextBox1.Text
password = Me.TextBox2.Text

If Not cnn.State = ConnectionState.Open Then
    cnn.Open()
End If

Try
    Dim daA As New OleDb.OleDbDataAdapter("SELECT * FROM adlogin WHERE `password` =?", cnn)
    daA.SelectCommand.Parameters.AddWithValue("@pass", password);
    daA.Fill(dtT)
    Me.DG1.DataSource = dtT


    If user = DG1.Item(1, 0).Value And password = DG1.Item(0, 0).Value Then
        cmd.Connection = cnn
        cmd.CommandText = "UPDATE adlogin SET `password` = ? WHERE `user` = ?"
        Dim result = MsgBox("Change Administrator password!!! Are you sure?", MsgBoxStyle.YesNo)
        If result = DialogResult.Yes Then
            cmd.Parameters.AddWithValue("@pass", Me.TextBox3.Text)
            cmd.Parameters.AddWithValue("@user", user)
            cmd.ExecuteNonQuery()
            MsgBox("PassWord Changed", MsgBoxStyle.MsgBoxRight)
            Panel1.Hide()
        End If
    Else
        MsgBox("INVALID PASSWORD", MsgBoxStyle.Critical)
    End If
    cnn.Close()
Catch ex As Exception
    MsgBox("INVALID PASSWORD " & ex.Message, MsgBoxStyle.Critical)
End Try

10-07 19:39
查看更多