Trouble inserting data into ACCESS database.

Question

3.00/5 (1 vote)

See more:

I'm having trouble inserting data into an ACCESS database. The code below is what I'm using but it's throwing an exception. Something along the lines of "Number of query values and destination fields are not the same."

<pre lang="vb"> Dim cmd As OleDbCommand = New OleDbCommand

        cmd.CommandType = CommandType.Text
        cmd.Connection = myConnection
        cmd.CommandText = "INSERT INTO users VALUES ('" & tbUsername.Text & "', '" & tbPassword1.Text & "', '" & cbRole.SelectedItem & "');"

        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "username"
        cmd.Parameters.Item("username").Value = tbUsername.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "pwd"
        cmd.Parameters.Item("pwd").Value = tbPassword1.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "role"
        cmd.Parameters.Item("role").Value = cbRole.SelectedItem

        myConnection.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message)
        End Try

        myConnection.Close()

Any thoughts would be helpful. I am at a complete loss here...

What I have tried:

Ive tried adding the columns to the SQL statement but then it says there's a syntax error. Ive checked the ACCESS reserved words list and I'm not using those as table or column names.

Ive walked through this code multiple times and it seems like it should work and Ive verified there are only 3 columns in the table and I'm only passing three variables to be written into the table.

Posted 2-Nov-16 15:02pm

matt09524

Updated 3-Nov-16 9:32am

Add a Solution

Comments

Richard Deeming 3-Nov-16 16:14pm

You have some urgent reading to do:

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

Richard Deeming 3-Nov-16 16:15pm

Once you've fixed that, then you need to fix how you're storing the passwords:

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

matt09524 5-Nov-16 22:09pm

This program is no where near completion. In figuring out this problem, I learned about parameterising, but I still need to learn how to do it. The password hashing is something I was aware of, but had not yet put into practice on this app yet.

But it was good to read the information there. Looks like I will be going with Libsodium unless you recommend something better?

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

Suvendu Shekhar Giri · Answer 1 · 2016-11-02T15:47:00

That's could be because, although you have added values to parameters but in the query you are not actually using those parameters.
Modify your query to something like following and check if that helps-

VB

cmd.CommandText = "INSERT INTO users VALUES (@username, @pwd, @role);"

Hope, it helps :)

matt09524 · Answer 2 · 2016-11-02T15:56:00

Solution 3

I actually fixed it like this:

VB

cmd.CommandText = "INSERT INTO users (username,pwd,role) VALUES ('" + tbUsername.Text + "','" + tbPassword1.Text + "','" + cbRole.SelectedItem + "')"

I added the column names. I did this before, but I don't think it likes spaces after the commas.

Posted 2-Nov-16 15:56pm

matt09524

Comments

Suvendu Shekhar Giri 3-Nov-16 1:21am

Well, "spaces" could never be the cause of the error here. Can you check once again?
Very Important :
Your code is vulnerable to SQL injection[^]

I would suggest to go for parameterised query or stored procedure instead.

matt09524 3-Nov-16 15:29pm

Can you explain paramaterised queries? Are you meaning to remove the values from the insert statement? If so, what do I put in their place? The last time I did OLE programming, it was a bit different than this so I'm trying to find the right methods.

matt09524 · Answer 3 · 2016-11-03T09:32:00

This is the final code that I went with and it's working... not sure how or why.

VB

Dim cmd As OleDbCommand = New OleDbCommand

        cmd.CommandType = CommandType.Text
        cmd.Connection = myConnection
        cmd.CommandText = "INSERT INTO users (username,pwd,role) VALUES ('" + tbUsername.Text + "','" + tbPassword1.Text + "','" + cbRole.SelectedItem + "')"

        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "username"
        cmd.Parameters.Item("username").Value = tbUsername.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "pwd"
        cmd.Parameters.Item("pwd").Value = tbPassword1.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "role"
        cmd.Parameters.Item("role").Value = cbRole.SelectedItem

        myConnection.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message)
            Exit Sub
        End Try

        myConnection.Close()

        MsgBox("User information has been saved.")

        fill_listbox()
        tbUsername.Clear()
        tbPassword1.Clear()
        tbPassword2.Clear()
        cbRole.Items.Clear()
        tbUsername.Focus()

Trouble inserting data into ACCESS database.

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0