Insert into access 2007 database

Marvin kakuru

Hi everyone
I am trying to use the code below to insert data into my access database, in the database I have set wkno and yr values as "number" and dat as "date/time" all the rest are set as "text" values. When I run the code below I get an error "Syntax error in INSERT INTO statement."

DateTime date = Convert.ToDateTime(textBox4.Text);
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\katoto\Documents\Visual Studio 2010\Projects\Genesis Philcollins\data\thyfarm.accdb;Jet OLEDB:Database Password=Kyozi");

conn.Open();

string insertString = "insert into Herdregistar (day,dat,wkno,mont,yr,motheruic,mother,animalsex,uic,nam,tagno,status,owner,presence) values ('" + textBox3.Text + "','" + DateTime.Parse(textBox4.Text).ToString("dd/MM/yyyy") + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + comboBox3.Text + "','" + textBox11.Text + "','" + textBox12.Text + "','" + textBox13.Text + "','" + comboBox1.Text + "','" + textBox15.Text + "','" + comboBox4.Text + "')";
OleDbCommand cmd = new OleDbCommand(insertString, conn);

cmd.ExecuteNonQuery();

conn.Close();

please help me.
thanks

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
The date/time value needs to be enclosed in #'s rather than single quotes.

Also the numbers shouldn't be enclosed in anything.

So, I'd try:

string insertString = "insert into Herdregistar (day,dat,wkno,mont,yr,motheruic,mother,animalsex,uic,nam,tagno,status,owner,presence) values ('" + textBox3.Text + "',#" + DateTime.Parse(textBox4.Text).ToString("dd/MM/yyyy") + "#," + textBox5.Text + ",'" + textBox6.Text + "'," + textBox7.Text + ",'" + textBox8.Text + "','" + textBox9.Text + "','" + comboBox3.Text + "','" + textBox11.Text + "','" + textBox12.Text + "','" + textBox13.Text + "','" + comboBox1.Text + "','" + textBox15.Text + "','" + comboBox4.Text + "')";
0
Marvin kakuru14y ago
hai vulpes, thanks the trick with replacing single notes with # for date/time values and non for number values, has worked perfectly. the error i kept on getting was due to the work "DAY" being a reserved word for the access database.
thanks again.Best Regards.
0
Marvin kakuru14y ago
hi vulpes,

thanks, i have tried the code you advised but i am still getting the same error "Syntax error in INSERT INTO statement."
could it be the spacing in the insert string.please advise
0