Click here to Skip to main content
15,029,501 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 15-Feb-17 7:31am
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.

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900