我如何...更新我的访问数据库 [英] How do i...update my access database

问题描述

如何在vb.net代码中更新我的访问数据库我使用OleDb连接到数据库，它在datagridview中进行更改但不进入数据库

how can i update my access database in vb.net code i use OleDb connection to the database it's make change in datagridview but not into the database

Public Sub executquery()
        Dim commandOleDb As New OleDbCommand(query, con)
        commandOleDb.ExecuteNonQuery()
        con.Close()
    End Sub
--------------------
    Private Sub ButtonInsert_Click(sender As Object, e As EventArgs)
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Almaashat.accdb")
        Dim query As String
        Try
            con.Open()
            query = "INSERT INTO Techers (File_ID,Name,Workplace,Jop,Appointment,Class,Birthday,End_date,End_class,End_for,Note) VALUES (" & TextBoxFile_ID.Text & " ,'" & TextBoxName.Text & "' ,'" & TextBoxWorkplace.Text & "' ,'" & TextBoxJop.Text & "'  ,'" & DateTimePickerAppoiment.Text & "','" & TextBoxClass.Text & "','" & DateTimePickerBirthday.Text & "' ,'" & DateTimePickerEnd_date.Text & "' ,'" & TextBoxEnd_class.Text & "','" & TextBoxEnd_for.Text & "' ,'" & TextBoxNote.Text & "')"
            executquery()
            con.Close()
            MsgBox("Your Data Inserted")
        Catch ex As Exception
            MsgBox("Your Data Not Inserted")
        End Try
        TechersDataGridView.DataSource = TechersBindingSource
           End Sub

我尝试过：

What I have tried:

Public Sub executquery()
        Dim commandOleDb As New OleDbCommand(query, con)
        commandOleDb.ExecuteNonQuery()
        con.Close()
    End Sub
    Private Sub ButtonInsert_Click(sender As Object, e As EventArgs)
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Almaashat.accdb")
        Dim query As String
        Try
            con.Open()
            query = "INSERT INTO Techers (File_ID,Name,Workplace,Jop,Appointment,Class,Birthday,End_date,End_class,End_for,Note) VALUES (" & TextBoxFile_ID.Text & " ,'" & TextBoxName.Text & "' ,'" & TextBoxWorkplace.Text & "' ,'" & TextBoxJop.Text & "'  ,'" & DateTimePickerAppoiment.Text & "','" & TextBoxClass.Text & "','" & DateTimePickerBirthday.Text & "' ,'" & DateTimePickerEnd_date.Text & "' ,'" & TextBoxEnd_class.Text & "','" & TextBoxEnd_for.Text & "' ,'" & TextBoxNote.Text & "')"
            executquery()
            con.Close()
            MsgBox("تم الإدخال بنجاح")
        Catch ex As Exception
            MsgBox("لم يتم الإدخال بنجاح")
        End Try
        TechersDataGridView.DataSource = TechersBindingSource
           End Sub

推荐答案

从不使用由SQL注入引起的串联字符串。

这里描述了有关SQL注入的一些信息以及如何以正确方式执行此操作的方法：记录未插入ms访问数据库 [ ^ ]

Never use concatenated string due to SQL Injection.
A bit information about SQL Injection and the way how to do it in proper way is described here: Record not insert in ms access database[^]

不喜欢！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？



解决这个问题，并注意你的ButtonInsert_Click中的查询 method是一个局部变量，因此它与 executquery 方法中的 query 无任何关联。



并帮自己一个忙 - 不要硬编码连接字符串！它们应该始终位于配置文件中，因此您不需要更改软件并为每个新安装重新编译。

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that, and note that query inside your ButtonInsert_Click method is a local variable, so it is not in any way related to query inside your executquery method.

And do yourself a favour - don't hard-code connection strings! They should always be in configuration files, so you don;t need to change your software and recompile for each new installation.

根据发布的代码判断，看起来它可能在executquery中出现问题（）函数。



1.不清楚查询和con变量是如何填充的。



将这两行移到按钮点击功能之外或将这些变量传递给executquery（）

Judging by the posted code, look like it could be issue in executquery() function.

1. not clear how the "query" and "con" variable get populated.

Either move these two lines outside of button click function or pass those variables to executquery()

Dim query As String
Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Almaashat.accdb")

那么，为什么不保持简单并将这两行留在按钮中点击功能？

By the way, why not keep it simple and leave this two line in button click function?

Dim commandOleDb As New OleDbCommand(query, con)
        commandOleDb.ExecuteNonQuery()

一旦你开始工作，你就可以研究使用参数化查询

使用VB.NET将记录添加和保存到Access数据库免费的源代码，教程和文章 [ ^ ]

这篇关于我如何...更新我的访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！