How to insert data into MS access database from VB.NET?

Question

0.00/5 (No votes)

See more:

, +

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.

But it still doesn't work.

Posted 14-Feb-17 23:02pm

Member 13002361

Updated 15-Feb-17 7:31am

Add a Solution

Comments

[no name] 15-Feb-17 7:26am

I believe you would need to fix your connection string. Winforms application do not usually have DataDirectory folders.

Richard Deeming 15-Feb-17 12:04pm

Working with local databases[^]
– For applications placed in a directory on the user machine, this will be the app's (.exe) folder.
– For apps running under ClickOnce, this will be a special data folder created by ClickOnce
– For Web apps, this will be the App_Data folder

[no name] 15-Feb-17 12:20pm

Thanks. Learned something new today.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2017-02-15T06:11:00

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

VB.NET

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

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[^]

Dave Kreskowiak · Answer 2 · 2017-02-15T07:31:00

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.

How to insert data into MS access database from VB.NET?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0