时间戳

首页 > 解决方案 > 它告诉我更新成功而不将数据添加到 ms 访问数据库文件

vb.net - 它告诉我更新成功而不将数据添加到 ms 访问数据库文件

问题描述

此报告更新成功，无需将数据添加到 MS Access 数据库文件

Imports System.Data.OleDb

Public Class Form1
    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DatabaseDataSet.Table1' table. You can move, or remove it, as needed.
        Me.Table1TableAdapter.Fill(Me.DatabaseDataSet.Table1)

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
        dataFile = "C:\Users\police\Documents\database.accdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString
        myConnection.Open()
        Dim str As String
        str = "update Table1 set [ProductName] ='" & TextBox2.Text & "', [Qty] ='" & TextBox3.Text & "', [Price] ='" & TextBox4.Text & "'  where [ID] = " & TextBox1.Text & ""
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        MsgBox("Update Success")

        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            TextBox4.Clear()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

标签： vb.net

解决方案

上面的评论提供了最好的解决方案，但无论出于何种原因，如果您想编写自己的更新命令，您可以按如下方式进行。

将您的数据库对象保持在使用它们的方法的本地。Using...End Using块然后可以确保您的对象已关闭和处置。始终使用参数来防范 sql 注入并使您的查询更易于编写。在 Access 中，参数在 sql 语句中出现的顺序必须与它们添加到参数集合中的顺序相匹配。我不得不猜测.Add方法中参数的数据类型。检查数据库的实际类型并调整代码。文本框中的值应该在您到达此步骤之前进行验证，但我只是根据愿望和祈祷来转换它们。

Private ConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\police\Documents\database.accdb"

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Using myConnection As New OleDbConnection(ConStr),
            cmd As New OleDbCommand("update Table1 set [ProductName] = @ProdName, [Qty] = @Qty, [Price] = @Price  where [ID] = @ID;", myConnection)
        With cmd.Parameters
            .Add("@ProdName", OleDbType.VarChar).Value = TextBox2.Text
            .Add("@Qty", OleDbType.Integer).Value = CInt(TextBox3.Text)
            .Add("@Price", OleDbType.Decimal).Value = CDec(TextBox4.Text)
            .Add("@ID", OleDbType.Integer).Value = CInt(TextBox1.Text)
        End With
        Try
            myConnection.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Using
    MsgBox("Update Success")
    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()
End Sub

推荐阅读