Paste into access database

Question

I have problems inserting data from a text field into an MS access database, I get an error message " Syntax error in INSERT INTO. "

Can someone help me please? here is the code:

 public void button1_Click(object sender, EventArgs e)//save { using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\productdb.mdb")) { OleDbCommand CmdSql = new OleDbCommand("Insert into [product](Kod, names, price,type,volume,manufacturer,importer) enter code here { conn.Open(); CmdSql.Parameters.AddWithValue("@Kod", textBox1.Text); CmdSql.Parameters.AddWithValue("@names", textBox2.Text); CmdSql.Parameters.AddWithValue("@price", textBox3.Text); CmdSql.Parameters.AddWithValue("@type", textBox4.Text); CmdSql.Parameters.AddWithValue("@volume", textBox5.Text); CmdSql.Parameters.AddWithValue("@manufacturer", textBox6.Text); CmdSql.Parameters.AddWithValue("@importer", textBox7.Text); CmdSql.ExecuteNonQuery();// i get the error here<<< conn.Close(); } } 

Answer 1

You are missing the VALUES part of your insert statement:

 OleDbCommand CmdSql = new OleDbCommand("Insert into [product] (Kod, [names], price, type, volume, manufacturer, importer) VALUES (@Kod, @names, @price, @type, @volume, @manufacturer, @importer)", conn); 

And you use Access and OldeDbCommand ... so do you really need to use ? instead of the named parameter:

 OleDbCommand CmdSql = new OleDbCommand("Insert into [product] (Kod, [names], price, type, volume, manufacturer, importer) VALUES (?, ?, ?, ?, ?, ?, ?)", conn); 

See this question for more details.

Note: make sure you enclose all reserved keywords in square brackets.

Answer 2

The word NAMES is a reserved keyword for MS-Access Jet SQL , you need to enclose it in square brackets. This causes a syntax error. (Of course, considering that the missing part of VALUES is just a typo). Therefore, the correct syntax is:

 OleDbCommand CmdSql = new OleDbCommand("Insert into [product] (Kod, [names],price,type," + "volume,manufacturer,importer) " + "VALUES (?, ?, ?, ?, ?, ?, ?)"; 

I changed placeholders for parameters with one question mark. OleDb does not support named parameters, and only a question mark will do, but it is imperative to add parameters to OleDbCommand in the exact sequence expected by the command.

There is another aspect of your code that needs to be addressed. You use the AddWithValue method to create a list of parameters. This means that the parameter data type is implicitly determined by the value data type. You use TextBox.Text everywhere, and this is a string. Thus, this can cause update problems when the receiving field is of a different type (for example, the price is probably numeric). If the database field does not have a text type, add the appropriate conversion to the value of the input parameter.

For instance:

 // Supposing you have an in place validator for the text to be converted...... CmdSql.Parameters.AddWithValue("@price", Convert.ToDecimal(textBox3.Text)); 

Answer 3

You have written an incomplete command. It should look like this:

 OleDbCommand CmdSql = new OleDbCommand("Insert into [product](Kod, names, price,type,volume,manufacturer,importer) values(@Kod,@names,@price,@type, @volume,@manufacturer,@importer)"); 

Named parameters are only supported in SqlCommand and not in the oledb command, so you need to use? instead of the parameters in the command text.

Answer 4

OleDbCommand does not support named parameters, so your SQL statement should be:

 OleDbCommand CmdSql = new OleDbCommand( "INSERT INTO [product] " + "(Kod, names, price, type, volume, manufacturer ,importer) " + "VALUES (?, ?, ?, ?, ?, ?, ?)" , conn); 

Answer 5

Insert always in this way the easiest, fastest and most memorable way.

 String query = "Insert into Supplier(Kod, names,price,type,volume,manufacturer,importer) values('" + textBox1.text + "','" +textBox2.text + "','" + textBox3.text + "','" + textBox4.text + "','" + textBox5.text + "','" + textBox6.text + "','" + textBox7.text + "') "; SqlCommand cmd = new SqlCommand(query, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); 

Answer 6

 OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\productdb.mdb" String strSQL="Insert into [product](Kod, names, price,type,volume,manufacturer,importer) values(@Kod,@names,@price,@type, @volume,@manufacturer,@importer)" OleDBCommand CmdSql= new OleDBCommand(strSQL, con); CmdSql.CommandType = CommandType.Text; CmdSql.Parameters.AddWithValue("@Kod", textBox1.Text); CmdSql.Parameters.AddWithValue("@names", textBox2.Text); CmdSql.Parameters.AddWithValue("@price", textBox3.Text); CmdSql.Parameters.AddWithValue("@type", textBox4.Text); CmdSql.Parameters.AddWithValue("@volume", textBox5.Text); CmdSql.Parameters.AddWithValue("@manufacturer", textBox6.Text); CmdSql.Parameters.AddWithValue("@importer", textBox7.Text); con.Open(); try { CmdSql.ExecuteNonQuery(); } catch (Exception ex) { ex.Message.ToString(); } finally { con.Close(); CmdSql.Dispose(); } 

Answer 7

 string Query = "insert into tablename values ('" + txtstring.text + "', " + txtDouble.text + ")"; Cmd = new OleDbCommand(); Cmd.Connection = Con; Cmd.CommandText = Query; Cmd.ExecuteNonQuery(); 

Answer 8

Feilds = "T1, T2, T3, T4, T5, T6, T7, T8"; value =
"NAJAFI", "DONYA", "26/26/2014 12:00:00 AM", "کد: 1 نام و نام" فارسگی ": خانوادگی: fsdfsdf ',' * ',' - ',' 3/4 / 2014 7:13:29 PM "Table =" Table ";

  OleDbConnection sc = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;DataSource=" +@ "G:\sazenama\SazeNama\Sazeama\DBSazeNama.accdb"); sc.Open(); OleDbCommand sm; if (edit == false) sm = new OleDbCommand("insert into " + Table + "(" + Feilds + ") values(" + value + "')", sc); else sm = new OleDbCommand("update " + Table + " set " + Feilds + "'", sc); sm.ExecuteNonQuery();