Inserting Data from VB.net to MS Access

Question

2.33/5 (2 votes)

See more:

Hello Guys,

I'm trying to insert a data from vb.net to ms access but I'm getting an insert syntax error, does anyone care to take a look at my code and share your ideas why it so? Also, if you can give me an alternative way of coding this I will highly appreciate it. Thanks in advance.

VB

Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click

        Try
            Dim sqlconn As New OleDb.OleDbConnection
            Dim sqlquery As New OleDb.OleDbCommand
            Dim connString As String
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Visual Studio 2010\Projects\Invent\Invent\mdbInvent.accdb"
            sqlconn.ConnectionString = connString
            sqlquery.Connection = sqlconn
            sqlconn.Open()
            sqlquery.CommandText = "INSERT INTO tblUsers(Username, Password)VALUES(@Username, @Password)"
            sqlquery.Parameters.AddWithValue("@Username", txtUname.Text)
            sqlquery.Parameters.AddWithValue("@Password", txtPass.Text)
            sqlquery.ExecuteNonQuery()
            sqlconn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        
    End Sub

Posted 12-Feb-14 17:01pm

Michael Sernal

Updated 25-Jul-17 7:23am

Add a Solution

Comments

Michael Sernal 13-Feb-14 4:40am

An update Guys, I recoded it, and now I'm having a different error. It goes like this "no. of query values and destination fields are not the same." My table in my ms access database is consist of 3 columns namely, ID, Username, and Password.
Note: ID is auto-numbered by the DB. That's why I only need txtUname.text and txtPass.text to be saved on my DB.
So does anyone have any idea how will be able to save the data with just the 2 since the ID is auto numbered by MS access? Thanks.

Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
Dim objConn As New OleDb.OleDbConnection
Dim connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Avatar\Documents\Visual Studio 2010\Projects\Invent\Invent\mdbInvent.accdb;User ID=admin"
Dim ins As New OleDb.OleDbCommand
Try
objConn.ConnectionString = connStr
objConn.Open()
ins.Connection = objConn
ins.CommandText = "INSERT INTO tblUsers VALUES(@Username, @Password)"
ins.Parameters.AddWithValue("@Username", txtUname.Text)
ins.Parameters.AddWithValue("@Password", txtPass.Text)
ins.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

6 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)

nilesh sawardekar · Answer 1 · 2014-07-11T04:57:00

try this...dont need to change column name password..

VB

Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
 
        Try
            Dim sqlconn As New OleDb.OleDbConnection
            Dim sqlquery As New OleDb.OleDbCommand
            Dim connString As String
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Visual Studio 2010\Projects\Invent\Invent\mdbInvent.accdb"
            sqlconn.ConnectionString = connString
            sqlquery.Connection = sqlconn
            sqlconn.Open()
            sqlquery.CommandText = "INSERT INTO tblUsers([Username], [Password])VALUES(@Username, @Password)"
            sqlquery.Parameters.AddWithValue("@Username", txtUname.Text)
            sqlquery.Parameters.AddWithValue("@Password", txtPass.Text)
            sqlquery.ExecuteNonQuery()
            sqlconn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        
    End Sub

use square brackets [] around your column name.

basurajkumbhar · Answer 2 · 2014-07-11T03:50:00

Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
 
        Try
            Dim sqlconn As New OleDb.OleDbConnection
            Dim sqlquery As New OleDb.OleDbCommand
            Dim connString As String
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Visual Studio 2010\Projects\Invent\Invent\mdbInvent.accdb"
            sqlconn.ConnectionString = connString
            sqlquery.Connection = sqlconn
            sqlconn.Open()
            sqlquery.CommandText = "INSERT INTO tblUsers(Username, Password)VALUES(@Username, @Password)"
            sqlquery.Parameters.AddWithValue("@Username", txtUname.Text)
            sqlquery.Parameters.AddWithValue("@Password", txtPass.Text)
            sqlquery.ExecuteNonQuery()
            sqlconn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        
    End Sub

In this code Password is the key word in the access database so change the password to any other like pass and do the your process.

Here you want to change the your column name password any other.

Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
 
        Try
            Dim sqlconn As New OleDb.OleDbConnection
            Dim sqlquery As New OleDb.OleDbCommand
            Dim connString As String
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Visual Studio 2010\Projects\Invent\Invent\mdbInvent.accdb"
            sqlconn.ConnectionString = connString
            sqlquery.Connection = sqlconn
            sqlconn.Open()
            sqlquery.CommandText = "INSERT INTO tblUsers(Username, Password)VALUES(@Username, @Pass)"
            sqlquery.Parameters.AddWithValue("@Username", txtUname.Text)
            sqlquery.Parameters.AddWithValue("@Pass", txtPass.Text)
            sqlquery.ExecuteNonQuery()
            sqlconn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        
    End Sub

Try this ....

Karen Mitchelle · Answer 3 · 2014-02-12T17:56:00

Solution 1

can you try readingthis article[^]? Maybe that would help.. :)

Posted 12-Feb-14 17:56pm

Karen Mitchelle

TrushnaK · Answer 4 · 2014-02-12T18:05:00

Solution 2

change this line:-

sqlquery.CommandText = "INSERT INTO tblUsers(Username, Password)VALUES(@Username, @Password)"

to

sqlquery.CommandText = "INSERT INTO tblUsers(Username, Password)VALUES('@Username', '@Password')"

Posted 12-Feb-14 18:05pm

TrushnaK

Comments

CHill60 11-Jul-14 10:43am

Absolutely the wrong thing to do.

Michael Sernal · Answer 5 · 2014-02-28T00:45:00

Private Sub bSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSave.Click
        Dim conn As New OleDb.OleDbConnection
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dbInvent.accdb;Persist Security Info=False"
        Dim cmd As New OleDb.OleDbCommand

        Try
            conn.ConnectionString = connStr
            conn.Open()
            cmd.Connection = conn

            cmd.CommandText = "INSERT INTO tblUsers(Username, Password)VALUES(@User, @Pass)"
            cmd.Parameters.AddWithValue("@User", txtUname.Text)
            cmd.Parameters.AddWithValue("@Pass", txtUpass.Text)

            cmd.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

saxenarangu · Answer 6 · 2014-07-10T22:52:00

Solution 4

rename your column name "password" to some other ex: "password12" or "pwd"

Posted 10-Jul-14 22:52pm

saxenarangu

Comments

CHill60 11-Jul-14 7:35am

Why? "password" is not a reserved word in Access

Inserting Data from VB.net to MS Access

6 solutions

Solution 6

Solution 5

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0