CODEWITHSUNDEEP
c#sql-serverdatabaseado.net
Mohamed Safwat
Mohamed Safwat

Reputation: 75

" Error in connection" when use insert command to access db with ado.net C#

I write code to insert some values to access database with C#/ado.net but there is an error appear called "error in connection" although i use select command to retrieve some valuesin the same program and works successfully 

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Mohamed\documents\visual studio 2012\Projects\Library Store\Library Store\Book.accdb");

conn.Open();
OleDbCommand cmd = new OleDbCommand("INSERT INTO Store VALUES (@val1,@val2,@val3,@val4,@val5,@val6)", conn);
cmd.Parameters.AddWithValue("@val1", ISBNTB.Text.Trim());
cmd.Parameters.AddWithValue("@val2", NameTB.Text.Trim());
cmd.Parameters.AddWithValue("@val3", GategoryTB.Text.Trim());
cmd.Parameters.AddWithValue("@val4", AuthorTB.Text.Trim());
cmd.Parameters.AddWithValue("@val5", int.Parse(CostTB.Text.Trim()));
cmd.Parameters.AddWithValue("@val6", dateTimePicker1.Text);
cmd.ExecuteNonQuery();
MessageBox.Show(" Done :)");
conn.Close();

thanks;

Upvotes: 3

Views: 254

Answers (3)

Elvin Mammadov
Elvin Mammadov

Reputation: 27397

If you insert value to database with all column you can use this query

INSERT INTO Store  VALUES(@val1,@val2,@val3,@val4,@val5,@val6)

If you insert value to database with few column you will get this error, and you must write it as 

INSERT INTO Store **( Column1, Column2 )** VALUES(@val1,@val2,@val3,@val4,@val5,@val6)

Upvotes: 0

JeremiahDotNet
JeremiahDotNet

Reputation: 910

You need to specify the column names in your insert statement so that the DB knows where the data is going.

"INSERT INTO Store (Column1, Column2) VALUES (@val1, @val2)"

Upvotes: 0

Don Thomas Boyle
Don Thomas Boyle

Reputation: 3045

Give this a shot, you didn't specify what error it was but this should help you out if anythign figure out if you really have all columns your trying to insert to

Try writing you sql statement like so

INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data     Source=C:\Users\Mohamed\documents\visual studio 2012\Projects\Library Store\Library     Store\Book.accdb");

conn.Open();
OleDbCommand cmd = new OleDbCommand("INSERT INTO Store **( Column1, Column2 )** VALUES(@val1,@val2,@val3,@val4,@val5,@val6)", conn);
cmd.Parameters.AddWithValue("@val1", ISBNTB.Text.Trim());
cmd.Parameters.AddWithValue("@val2", NameTB.Text.Trim());
cmd.Parameters.AddWithValue("@val3", GategoryTB.Text.Trim());
cmd.Parameters.AddWithValue("@val4", AuthorTB.Text.Trim());
cmd.Parameters.AddWithValue("@val5", int.Parse(CostTB.Text.Trim()));
cmd.Parameters.AddWithValue("@val6", dateTimePicker1.Text);
cmd.ExecuteNonQuery();
MessageBox.Show(" Done :)");
conn.Close();

Upvotes: 2

Related Questions