vb.net - 使用 vb.net 在 MS Access 中插入和更新值

Question

我已经查看了该站点上的大部分论坛，但没有找到我的解决方案。我的问题是将数据从 vb.net 插入到 MS Access，但我做不到。它没有显示任何错误，但也没有在我的表中插入值。我使用的是非常简单的代码:

Imports System.Data.OleDb

Public Class Add_LEads

    Dim conn As New OleDbConnection
    Dim cmd As New OleDbCommand
    Dim da As New OleDbDataAdapter
    Private Sub Add_LEads_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\IndGlobalDB.accdb;Persist Security Info=True;Jet OLEDB:Database Password=admin")
        lblDate.Text = Format(Date.Now, "yyyy/MM/dd")
        conn.Open()
        Dim sql As String
        Dim a As Integer
        sql = "select S_No from Leadss"
        cmd = New OleDbCommand(sql, conn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            a = dr(0)
        End While
        lblNo.Text = a + 1
        conn.Close()
    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        conn.Open()
        cmd.Connection = conn
        cmd.CommandText = "INSERT INTO Leadss(S_No,Contact_Person,Mobile_No,Email_Id,Description,First_Follow_Up,Remarks,L_Date,Alternate_no)VALUES('" & lblNo.Text & "','" & txtName.Text & "','" & txtMobile.Text & "','" & txtEmail.Text & "','" & txtWebDescr.Text & "','" & txtFollowUp.Text & "','" & txtRemarks.Text & "','" & lblDate.Text & "','" & txtAlternate.Text & "')"
        cmd.ExecuteNonQuery()
        conn.Close()
        MsgBox("Saved!!!", vbOK)
    End Sub
    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
        Welcome.Show()
    End Sub

End Class

Answer 1

您应该使用参数化查询来避免 Sql 注入(inject)攻击，并让 JET 引擎解析您的字符串参数以查找无效字符。

 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 
                                                                   Handles btnSave.Click 
    conn.Open() 
    cmd.Connection = conn 

    cmd.CommandText = "INSERT INTO Leadss(S_No,Contact_Person,Mobile_No,Email_Id," & _
                      "Description,First_Follow_Up,Remarks,L_Date,Alternate_no) VALUES " & _
                      "(?, ?, ?, ?, ?, ?, ?, ?, ?)"


    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@p1", lblNo.Text)
    cmd.Parameters.AddWithValue("@p2", txtName.Text)
    cmd.Parameters.AddWithValue("@p3", txtMobile.Text)
    cmd.Parameters.AddWithValue("@p4", txtEmail.Text)
    cmd.Parameters.AddWithValue("@p5", txtWebDescr.Text)
    cmd.Parameters.AddWithValue("@p6", txtFollowUp.Text)
    cmd.Parameters.AddWithValue("@p7", txtRemarks.Text)
    cmd.Parameters.AddWithValue("@p8", lblDate.Text)
    cmd.Parameters.AddWithValue("@p9", txtAlternate.Text)
    cmd.ExecuteNonQuery() 
    conn.Close() 
End Sub 

也就是说，只有当您的字段类型是文本类型而不是数字、日期时间或 bool 值时，这才有效，在这种情况下，您应该使用 Convert.ToXXXXX 方法将输入文本转换为适当的类型。
(以下示例假定您的输入包含有效数字和日期)

....
cmd.Parameters.AddWithValue("@p3", Convert.ToInt32(txtMobile.Text))
.....
cmd.Parameters.AddWithValue("@p8", Convert.ToDateTime(lblDate.Text))
cmd.Parameters.AddWithValue("@p9", Convert.ToInt32(txtAlternate.Text))

另一种错误的方法是保留全局变量以供重用，例如 OleDbConnection、OleDbCommand。这可以防止运行时在不使用时处置这些对象。相反，您应该遵循这种方法

 Using conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data " +
                  "Source=|DataDirectory|\IndGlobalDB.accdb;" +
                   "Persist Security Info=True;Jet OLEDB:Database Password=admin")   
    Using cmd = New OleDbCommand()
        conn.Open() 
        cmd.Connection = conn 
        cmd.CommandText = "INSERT INTO ................"
        cmd.Parameters.AddWithValue("@p1", lblNo.Text)
        ..........
    End Using
End Using