Click here to Skip to main content
15,940,218 members
Please Sign up or sign in to vote.
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
Updated 3-Nov-16 9:32am
Comments
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?

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 :)
 
Share this answer
 
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.
 
Share this answer
 
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.
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()
 
Share this answer
 

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