insert into access database

Question

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();
}
}

Strapped

"enter code here" looks like you're missing some code? – Gyrose

Answer 1

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.

Watcher

Answer 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));

Whomever

Good call on the AddWithValue implicit conversion. – Watcher

Answer 3

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.

Echevarria

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. – Echevarria

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 – Strapped

do not use names with ? mark."VALUES (?,?,?,?,?,?,?)", conn); – Echevarria

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. – Strapped

Check the key column in database. is it KDO? then your command might include a value of KDO that is already in that table. – Echevarria

Yes Kod is the key culumn,I don't really understand what i should change. – Strapped

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. – Echevarria

thanks it works, how can I update database so the added data shows in DataGridView? – Strapped

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);

Gyrose

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 – Strapped

Read the answer again - OleDbCommand does not support named parameters. Use question marks instead. – Gyrose

Based on Steve's answer, try putting the column names insde square brackets [] to avoid using reserved keywords. – Gyrose

Answer 5

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();

Crissum

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. en.wikipedia.org/wiki/SQL_injection – Watcher

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();
        }

Aspirate

Answer 7

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

Rueful

Answer 8

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();

Alimentation