5

i have trouble inserting data from textbox into ms access database, I get an error "Syntax error in INSERT INTO."

Can someone help me out please? here's 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();
}
}
Billal Begueradj
  • 20,717
  • 43
  • 112
  • 130
user2263271
  • 101
  • 1
  • 1
  • 5

8 Answers8

7

You are missing the VALUES portion 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 are using Access and OldeDbCommand... so you actually need to use ? instead of a named parameter:

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

See this question for more information.

A side note: Ensure you wrap any reserved keywords in square brackets.

Community
  • 1
  • 1
Jason Down
  • 21,731
  • 12
  • 83
  • 117
2

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

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

I have changed the placeholders for the parameters with a single question mark. OleDb doesn't support the named parameters and just a question mark will do, but, it is of uttermost importance to add the parameters to the OleDbCommand in the exact sequence expected by the command.

There is another aspect of you code that need to be addressed. You use the method AddWithValue to build your parameter list. This means that the datatype of the parameter is implicitly derived by the datatype of the value. You use everywhere TextBox.Text and this is a string. So this could cause problems with the update when the receiving field is of a different type (for example Price is probably numeric) If the database field are not of text type then add an appropriate conversion to the incoming parameter value.

For example:

// Supposing you have an in place validator for the text to be converted......
CmdSql.Parameters.AddWithValue("@price", Convert.ToDecimal(textBox3.Text));
Steve
  • 213,761
  • 22
  • 232
  • 286
0

You have written incomplete command. It should be like:

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

The named parameters are only supported in SqlCommand not in oledbcommand so you have to use ? in place of params in command text.

asim-ishaq
  • 2,190
  • 5
  • 32
  • 55
  • in place of @Kod and the rest in Command Text try using ?. Otherwise just build the actual query in command text like: "insert into product(kod,names,price,type,volume,manufacturer, importer) values (22,'My Name',200,1,1000,'DELL','APPLE')" enclose the strings in single quotes. – asim-ishaq Apr 09 '13 at 19:55
  • tried it like this: OleDbCommand CmdSql = new OleDbCommand("INSERT INTO [product] " +"([Kod], [names], [price], [type], [volume], [manufacturer] ,[importer]) " +"VALUES (?Kod,?names,?price,?type,?volume,?manufacturer,?importer)", conn); get the same error – user2263271 Apr 09 '13 at 20:11
  • do not use names with ? mark."VALUES (?,?,?,?,?,?,?)", conn); – asim-ishaq Apr 09 '13 at 20:22
  • I've changed: OleDbCommand CmdSql = new OleDbCommand("INSERT INTO [product]" + "(Kod,[names],price,type,volume,manufacturer,importer) " + "VALUES (?,?,?,?,?,?,?)", conn); now i get an error:the changes were not made ​​because of duplicate values ​​in the index, primary key, or relationship. – user2263271 Apr 09 '13 at 20:36
  • Check the key column in database. is it KDO? then your command might include a value of KDO that is already in that table. – asim-ishaq Apr 09 '13 at 20:43
  • Yes Kod is the key culumn,I don't really understand what i should change. – user2263271 Apr 09 '13 at 20:49
  • what value your are providing for KOD for example if its is 10 then open the table and check if any records exists with this value. – asim-ishaq Apr 09 '13 at 20:50
  • thanks it works, how can I update database so the added data shows in DataGridView? – user2263271 Apr 09 '13 at 21:01
0

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);
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • tried it like this OleDbCommand CmdSql = new OleDbCommand("Insert into [product](Kod, [names], price,type,volume,manufacturer,importer) values(@Kod,@names,@price,@type,@volume,@manufacturer,@importer)"); still doesn't work – user2263271 Apr 09 '13 at 19:54
  • Read the answer again - `OleDbCommand` does not support named parameters. Use question marks instead. – D Stanley Apr 09 '13 at 19:56
  • Based on Steve's answer, try putting the column names insde square brackets `[]` to avoid using reserved keywords. – D Stanley Apr 09 '13 at 20:00
0

Insert always this way the most easy,fast and 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();
Vivek Jain
  • 3,811
  • 6
  • 30
  • 47
Wajahat
  • 1
  • 6
  • 2
    This opens the door up to SQL injection. It may not be a concern for what you are doing, but if you don't use parameters and take text from the user you are exposing your database to sql injection. http://en.wikipedia.org/wiki/SQL_injection – Jason Down Apr 10 '13 at 13:16
0
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();
        }
0
string Query = "insert into tablename values ('" + txtstring.text + "', " + txtDouble.text + ")";
Cmd = new OleDbCommand();
Cmd.Connection = Con;
Cmd.CommandText = Query;
Cmd.ExecuteNonQuery();
Vivek Jain
  • 3,811
  • 6
  • 30
  • 47
Sagar Jaybhay
  • 71
  • 1
  • 2
-2

Feilds= "T1,T2,T3,T4,T5,T6,T7,T8" ; value =
"'NAJAFI','DONYA','3/26/2014 12:00:00 AM','کد :1 نام و نام خانوادگی:افشین نجفی','کد :df نام و نام خانوادگی: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();
Unheilig
  • 16,196
  • 193
  • 68
  • 98