如何从VB.NET将数据插入MS Access数据库？ [英] How to insert data into MS access database from VB.NET?

问题描述

大家好！

我正在尝试从VB.net向MS Access数据库插入数据。连接正常，没有错误，但是当我访问我的数据库时，没有添加数据。我该怎么做以ms访问我的插入数据？



这是我的代码：

Hello guys!
I am trying to insert data into MS Access Database from VB.net. The connection is working and there's no error but when I visited my database there's no added data. What should I do to see my inserted data in ms access?

Here's my code:

Public Class RegForm

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DBCaseStudy.mdb;Persist Security Info=False")


    Private Sub RegForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DBCaseStudyDataSet.tblAdmins' table. You can move, or remove it, as needed.
        Me.TblAdminsTableAdapter.Fill(Me.DBCaseStudyDataSet.tblAdmins)
    End Sub

    Private Sub btnRegCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegCreate.Click
        conn.Open()

        Dim comd As New OleDb.OleDbCommand("INSERT INTO tblAdmins([ID],[Firstname],[Lastname],[Username],[Password]) VALUES ('" & txtRegID.Text & "','" & txtRegFirst.Text & "','" & txtRegLast.Text & "','" & txtRegUsername.Text & "','" & txtRegPass.Text & "')", conn)

        Try
            comd.ExecuteNonQuery()
            comd.Dispose()
            MsgBox("Record Appended", MsgBoxStyle.Information, "Successfully Added!")
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.InnerException)
        End Try
    End Sub

    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
        LoginForm.Show()
        Me.Close()
    End Sub
End Class

我尝试了什么：



我尝试使用

What I have tried:

I tried using

comd.Parameters.Add(New OledbParameter("ID", CType(txtRegID.Text,String))) ',etc. 

但它仍然不起作用。

推荐答案

首先修复 SQL注入 [ ^ ]漏洞的代码。

Start by fixing the SQL Injection[^] vulnerability in your code.

Private Function CreateConnection() As OleDb.OleDbConnection
    Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DBCaseStudy.mdb;Persist Security Info=False")
End Function

Private Sub btnRegCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegCreate.Click
    Using conn As OleDb.OleDbConnection = CreateConnection()
        Using comd As New OleDb.OleDbCommand("INSERT INTO tblAdmins ([ID], [Firstname], [Lastname], [Username], [Password] VALUES (@ID, @Firstname, @Lastname, @Username, @Password)", conn)
            comd.Parameters.AddWithValue("@ID", txtRegID.Text)
            comd.Parameters.AddWithValue("@Firstname", txtRegFirst.Text)
            comd.Parameters.AddWithValue("@Lastname", txtRegLast.Text)
            comd.Parameters.AddWithValue("@Username", txtRegUsername.Text)
            comd.Parameters.AddWithValue("@Password", txtRegPass.Text)
            
            conn.Open()
            Try
                comd.ExecuteNonQuery()
                MsgBox("Record Appended", MsgBoxStyle.Information, "Successfully Added!")
            Catch ex As Exception
                MsgBox(ex.ToString())
            End Try
        End Using
    End Using
End Sub

然后，您需要修复密码存储空间。您目前正在以纯文本格式存储密码，这是一个极其坏主意。您应该只使用每条记录的唯一盐来存储密码的盐渍哈希。

安全密码身份验证简单解释 [ ^ ]

Salted Password哈希 - 正确行事 [ ^ ]

---

你想知道关于SQL注入的一切（但不敢问）|特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

Then, you'll need to fix your password storage. You're currently storing passwords in plain text, which is an extremely bad idea. You should only ever store a salted hash of the password, using a unique salt per record.
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

除了其他人说的话，如果您的Access数据库在您的项目中，每次在Visual Studio中运行项目时，它都会被复制到bin \Debug | Release文件夹中。因此，在应用程序的第一次运行时，数据库将从项目文件夹复制到bin\Debug | Release文件夹，您可以在其中对数据库进行更改。然后退出该实例，进行一些更改，然后再次运行项目。然后将数据库再次复制到bin \Debug | Release文件夹，覆盖上次更改的内容。



如果数据库看起来与创建时完全一样每当你运行你的应用程序时，这就是正在发生的事情。



不要使用Access。使用REAL数据库引擎，这不会有问题。

On top of what everyone else said, if your Access database is in your project, it's being copied to the bin\Debug|Release folder every time you run your project in Visual Studio. So, on the first run of your app, the database is copied from the project folder to the bin\Debug|Release folder where you make your changes to the database. Then you quit that instance, make some changes, and run your project again. The database is then copied to the bin\Debug|Release folder again overwriting what you changed the last time.

If the database looks exactly like it does when you created it every time your run your app, this is what's happening.

Don't use Access. Use a REAL database engine and this won't be a problem.

这篇关于如何从VB.NET将数据插入MS Access数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！