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

Inserting and Updating values in MS Access Using vb.net

提问by user1756139

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:

我已经检查了这个站点上的大多数论坛，但我没有得到我的解决方案。我的问题是将数据从 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

回答by Steve

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

您应该使用参数化查询来避免 Sql 注入攻击，并让 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 

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)

也就是说，这仅在您的字段类型是文本类型而不是数字或日期时间或布尔值时才有效，在这种情况下，您应该使用 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))

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

另一个错误的方法是保留全局变量以供重用，例如您的 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

回答by Abbas

Here's a simple example for the use of SqlParameter and the try/catch block:

下面是一个使用 SqlParameter 和 try/catch 块的简单示例：

Dim connection As SqlConnection = As New SqlConnection("YourDbConnection")
Dim command As SqlCommand = connection.CreateCommand()

Try
    connection.Open()
    command.CommandText = "INSERT INTO Leadss(S_No) VALUES (@S_No)"
    command.Parameters.Add("@S_No", SqlDbType.Text)
    command.Parameters["@FirstName"].Value = lblNo.Text
    command.ExecuteNonQuery()
Catch Ex As SqlException
    'Process the exception
Finally
    connection.Close()
End Try

回答by joshua

Use Backticks (`) in your FROMStatement. It should be FROM(`Field1`,`Field2`,...etc) Values('value1', 'value2').

`在您的FROM语句中使用反引号 ( ) 。应该是FROM(`Field1`,`Field2`,...etc) Values('value1', 'value2')。

回答by Bhakti K Mishra

write this coding according to your database name, table name, field names in save button's click event...

根据您的数据库名称，表名称，保存按钮单击事件中的字段名称编写此编码...

 Using conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\user\Documents\Visual Studio 2008\Projects\demo for db in access\demo for db in access\DatabaseforDemo.accdb'")
        Using cmd = New OleDbCommand()
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "INSERT INTO demo1(Name) VALUES('" & TextBox1.Text & "')"
            'cmd.Parameters.AddWithValue("@p1", lblNo.Text)
            cmd.ExecuteNonQuery()
            MsgBox("saved..")
            conn.Close()
        End Using
    End Using

good luck... hope so it'll help you...!

祝你好运...希望它会帮助你...！

回答by Myk Agustin

In your question you said:

在你的问题中，你说：

Its not showing any error but also its not inserting values in my table

它没有显示任何错误，但也没有在我的表中插入值

Try to use Commit.

尝试使用提交。

A COMMITstatement in SQL ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users. The general format is to issue a BEGIN WORK statement, one or more SQL statements, and then the COMMIT statement. Alternatively, a ROLLBACK statement can be issued, which undoes all the work performed since BEGIN WORK was issued. A COMMIT statement will also release any existing savepoints that may be in use. In terms of transactions, the opposite of commit is to discard the tentative changes of a transaction, a rollback.

SQL 中的COMMIT语句结束关系数据库管理系统 (RDBMS) 中的事务并使所有更改对其他用户可见。一般格式是发出BEGIN WORK 语句、一个或多个SQL 语句，然后是COMMIT 语句。或者，可以发出 ROLLBACK 语句，这会撤消自发出 BEGIN WORK 以来执行的所有工作。COMMIT 语句还将释放任何可能正在使用的现有保存点。在事务方面，commit 的反面是丢弃事务的临时更改，即回滚。

quoted here: Commit (data management)

此处引用：Commit（数据管理）

Try
    'Open Connection...

        'Insert Statement....

        'Notification / Msgbox to confirm successful transaction

    Catch ex As Exception
        'RollBack Transaction...
        'Error Management...

    Finally
        'Commit...
        'Close DB Connection....
    End Try

Microsoft Documentation: OleDbTransaction.Commit Method ()

Microsoft 文档：OleDbTransaction.Commit 方法 ()

but remember: you should use transactions only if you are inserting/updating multiple SQL statements which then make sense for the rollback.

但请记住：只有在插入/更新多个 SQL 语句时才应该使用事务，这对回滚有意义。

Here is an Example in Adding transaction management into a form using MS Access 2010

这是使用 MS Access 2010 将事务管理添加到表单中的示例