c# sql INSERT INTO syntax error

Question

I've got a task to bulid an access database with interface as homework for school,

so, i build a database of company that imports musical instruments and sells them to stores.

here's the DB:

download the project.

and i used OLeDb connection to edit and read the DB.

then, I tried to use "INSERT INTO" SQL  command to add new customer, but every time i tried to run the program the IDE says: "Syntax error in INSERT INTO statement."

I did not understand where is my problem.

so here is the code:

public partial class Form1 : Form
    {
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\administrator\desktop\instruments import.accdb");
        OleDbCommand cmd = new OleDbCommand();
        
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string values ="('"+firstname.Text+"', "+lastname.Text+"', "+adress.Text+"', '"+phonenum.Text+"')";
            cmd = new OleDbCommand("INSERT INTO Customer(firstname,lastname,adress,phone number) VALUES " + values, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
}

please help me soon as possible...

Answer 1

hi,

you forgot some single quotes:

{  string values ="('"+firstname.Text+"', '"+lastname.Text+"', '"+adress.Text+"', '"+phonenum.Text+"')";

---

Regards, Nico

Answer 2

Exactly, copy and paste not allways is a good idea. ;-)

---

Hannes

If you have got questions about this, just ask.

In a perfect world,
users would never enter data in the wrong form,
files they choose to open would always exist
and code would never have bugs.

C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/

Answer 3

That looks very prone to SQL injection as you're just concatenating the strings into values so anything can be entered. They should be added to the statement as parameters like this:

    public partial class Form1 : Form
    {
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\administrator\desktop\instruments import.accdb");
        OleDbCommand cmd = new OleDbCommand();

        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            //string values = "('" + firstname.Text + "', " + lastname.Text + "', " + adress.Text + "', '" + phonenum.Text + "')";
            cmd = new OleDbCommand("INSERT INTO Customer(firstname,lastname,adress,phone number) VALUES (@firstname, @lastname, @adress, @phonenum)", conn);
            cmd.Parameters.AddWithValue("@firstname", firstName.Text);
            cmd.Parameters.AddWithValue("@lastname", lastname.Text);
            cmd.Parameters.AddWithValue("@adress", adress.Text);
            cmd.Parameters.AddWithValue("@phonenum", phonenum.Text);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}

I'm pretty sure adding the values as parameters includes the single quotes (') so you don't need them in the statement.

Answer 4

In addition to Nico's post: Think about what would happen, if a user entes inside the firstName texbox the following: '','','','');Delete from Customer;   You should allways use parameteizised command's.

---

Hannes

If you have got questions about this, just ask.

In a perfect world,
users would never enter data in the wrong form,
files they choose to open would always exist
and code would never have bugs.

C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/

Answer 5

Hi everyone,

i tried to run the code with the "add with value" function, and it still don't want to run.

here's the bad code i used in my program:

cmd = new OleDbCommand("INSERT INTO customer(firstname,lastname,adress,phone number) VALUES (@firstname, @lastname, @adress, @phonenum)", conn);
            cmd.Parameters.AddWithValue("@firstname", first.Text);
            cmd.Parameters.AddWithValue("@lastname", last.Text);
            cmd.Parameters.AddWithValue("@adress", adress.Text);
            cmd.Parameters.AddWithValue("@phonenum", phonenumber.Text);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

if you can help me, you will save my life :-)

Thanks to all answering.

---

gidi szanton.

Answer 6

please help me....

---

gidi szanton.

Answer 7

As a guess: try to enclose the column "phone number" in these brackets [ ]

---

Hannes

If you have got questions about this, just ask.

In a perfect world,
users would never enter data in the wrong form,
files they choose to open would always exist
and code would never have bugs.

C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/

Answer 8

Hi,

Try with the below code

string values ="('"+firstname.Text+"', '"+lastname.Text+"', '"+adress.Text+"', '"+phonenum.Text+"')";
cmd = new OleDbCommand("INSERT INTO Customer(firstname,lastname,adress,phone number) VALUES " + values +"", conn);
            conn.Open();
            cmd.ExecuteNonQuery();

---

PS.Shakeer Hussain

Answer 9

Syed, please read my post which i had written on friday. 

---

Hannes

If you have got questions about this, just ask.

In a perfect world,
users would never enter data in the wrong form,
files they choose to open would always exist
and code would never have bugs.

C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/

Answer 10

Gidi, I'm reading through the textbook Microsoft Visual C# 2010 Step By Step currently and I've seen in the chapter about Managing Data that leaving code open to SQL injection as in your first example (or Syed's example) is seen as 'phenomenally bad coding' so please stick with the parameters approach. Can you please state what the error is when you run the code in Debug mode?

The code I provided was an example of the methodology to use but as I haven't seen the SQL table you're using I can't be sure the command was correct. As Hannes suggested the [phone number] probably does need the square brackets [] and I also suspect adress is spelt wrong (should be address). The best way to find the issue is to run your application in Debug mode (F5) and see where it breaks or put a breakpoint on your button_Click event and step through checking each line to see what is going wrong.