使用vb.net在MS Access中插入和更新值 [英] Inserting and Updating values in MS Access Using vb.net

问题描述

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

I have checked most of the forums on this site but I didn't get my Solution. My problem is Inserting data from vb.net to MS Access but I am not able to do. Its not showing any error but also its not inserting values in my table. I am using very simple code:

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

推荐答案

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

You should use parametrized query to avoid Sql Injection Attacks and let the JET engine parse your string parameters for invalid characters.

 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 

表示，仅当您的字段类型是文本类型而不是数字，日期时间或布尔值时，此方法才有效，在这种情况下，应使用Convert.ToXXXXX方法将输入文本转换为适当的类型.
(下面的示例假设您的输入内容包含有效的数字和日期)

Said that, this works only if your field types are of text type and not numeric or datetime or boolean, in that case your should convert the input text in the appropriate type using Convert.ToXXXXX methods.
(The example below assumes that your inputs contains valid numbers and dates)

....
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. 这样可以防止运行时在不使用这些对象时对其进行处置.相反，您应该遵循这种方法

Another wrong approach is to keep global variables for reuse like your OleDbConnection, OleDbCommand. This prevent the runtime to dispose these objects when not used. Instead you should follow this approach

 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

这篇关于使用vb.net在MS Access中插入和更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！