Insert and Update syntax error on Database / Datagrid

Question

Here is my button command for save. need help in getting this to work, will be getting this to defend for tomorrow school project. Thanks! Its for Datagridview, access, c#. I use 2010VS and MS Access 2007.

private void save_Click(object sender, EventArgs e)
    {

        if (adminyes.Checked == true || adminno.Checked == true && textBox1.Text != null && textBox2.Text != null && textBox3.Text != null)
        {
            admin = "Yes";

            if (mode == "a")
            {
                x = 0;
                connect.Close();
                connect.ConnectionString = inventorydb;
                connect.Open();
                sqlcommand.CommandText = "SELECT * FROM Users WHERE Username ='" +textBox2.Text+ "' Or User_ID ='" +textBox1.Text+ "' ";
                sqlcommand.Connection = connect;
                OleDbDataReader reader = sqlcommand.ExecuteReader();
                while (reader.Read())
                {
                    x++;
                }

                if (x != 0)
                {
                    MessageBox.Show("", "",MessageBoxButtons.OK);
                }
                else
                {
                    DialogResult res = MessageBox.Show("Are you sure?", "Save User", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

                    if (DialogResult.Yes == res)
                    {
                        connect.Close();
                        connect.ConnectionString = inventorydb;
                        connect.Open();
                        sqlcommand.CommandText = "INSERT INTO Users (User_ID, Username, Password, Admin) VALUES ('" + textBox1.Text + "','" + textBox2.Text + "', '" + textBox3.Text + "', '" + admin + "') ";
                        sqlcommand.Connection = connect;
                        reader = sqlcommand.ExecuteReader();
                        MessageBox.Show("Record(s) Saved", "Sample");
                    }

                    reset();
                }
            }
            else if (mode == "e")
            {
                DialogResult res = MessageBox.Show("Are you sure?", "Update User", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

                if (DialogResult.Yes == res)
                {
                    connect.Close();
                    connect.ConnectionString = inventorydb;
                    connect.Open();
                    sqlcommand.CommandText = "UPDATE Users SET User_ID = '" + textBox1.Text + "',  Username = '" + textBox2.Text + "', Password = '" + textBox3.Text + "',Admin = '" + admin + "' WHERE SerialID = '" + idholder + "'  ";
                    sqlcommand.Connection = connect;
                    OleDbDataReader reader = sqlcommand.ExecuteReader();
                    reader.Read();
                    MessageBox.Show("Record(s) Updated", "Sample");

                }

                reset();
            }
        }
        else 
        {
            MessageBox.Show("", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

dukedukes · Accepted Answer · 2016-09-25 16:27:32Z

1

Password is a reserved word in Access. Change it to [Password] in your SQL queries. You should wrap all columns and tables like this.

Although this is just a school project I'll mention a few things:

Your code is vulnerable to SQL injection. Here's how to fix this for your insert method as an example:

sqlcommand.CommandText = "INSERT INTO [Users] ([User_ID], [Username], [Password], [Admin]) VALUES (@user_id, @username, @password, @admin)";
sqlcommand.Connection = connect;
sqlcommand.Parameters.AddWithValue("@user_id", textBox1.Text);
sqlcommand.Parameters.AddWithValue("@username", textBox2.Text);
sqlcommand.Parameters.AddWithValue("@password", textBox3.Text);
sqlcommand.Parameters.AddWithValue("@admin", admin);
reader = sqlcommand.ExecuteReader();

Also passwords shouldn't be stored in plain text. Look into password hashing and salting and how to approach it properly for more information.

edited Sep 25, 2016 at 16:27

answered Sep 25, 2016 at 16:19

dukedukes

2665 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richard Over a year ago

Thank you..!!! It works, i was trying to figure out on how to protect the password. I'm not sure how to but really thanks a lot!

Richard Over a year ago

Just a quick question sir, i have a bit of a problem whenever i add/edit/delete items on my data then after i close the program and open it again, all changes i made do not permanently save/reflect on the access database, do you know how to fix it?

dukedukes Over a year ago

Maybe you need to call OleDbCommand.ExecuteNonQuery rather than ExecuteReader. Also if you have a transaction active (i don't see one though) call OleDbTransaction.Commit. If that doesn't work try looking up some sample queries on access databases using OleDb objects and see if there are any differences. I'm not too familiar with Access databases.

Reza Aghaei Over a year ago

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

Collectives™ on Stack Overflow

Insert and Update syntax error on Database / Datagrid

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related