I'm new in C# and i would like to insert some Datagridview columns to Access in order to retrieve this data afterwards.
private void metroButton5_Click(object sender, EventArgs e)
try
{
OleDbConnection connection = new OleDbConnection(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = F:\Database\DBAdatabase.accdb; Persist Security Info = False; ");
for (int s = 0; s < dataGridView1.Rows.Count - 1; s++)
{
OleDbCommand command = new OleDbCommand(@"INSERT INTO Quotations (Position, LVPosition)VALUES('"+dataGridView1.Rows[s].Cells[0].Value+"','"+dataGridView1.Rows[s].Cells[1].Value+"')", connection);
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
MessageBox.Show("Data Saved");
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
I become the Error Systen.Data.OleDb.OleDbException (0x80040E14): Syntaxerror in the Insert Into.
Can somebody help me to solve it .....
Thx a lot..
I also referred to the this link (https://www.youtube.com/watch?v=8hCfje5VL-0&ab_channel=codefactory2016), but I cant find what's the error reason.
Off the bat, I would change
MessageBox.Show("Error " + ex);
to
MessageBox.Show("Error " + ex.Message);
It will give you a better understanding of the error
Also, it is very good practice to use parameters, this prevents single quote issues and prevents sql injection problems:
try
{
OleDbConnection connection = new OleDbConnection(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = F:\Database\DBAdatabase.accdb; Persist Security Info = False; ");
OleDbCommand command = new OleDbCommand("", connection);
for (int s = 0; s < dataGridView1.Rows.Count - 1; s++)
{
command.Parameters.AddWithValue("@position",dataGridView1.Rows[s].Cells[0].Value);
command.Parameters.AddWithValue("@lvPosition",dataGridView1.Rows[s].Cells[1].Value);
command.CommandText = "INSERT INTO Quotations (Position, LVPosition) VALUES (@position, @lvPosition)";
connection.Open();
//this line is not needed as it is set in the command constructor above
//command.Connection = connection;
command.ExecuteNonQuery();
MessageBox.Show("Data Saved");
connection.Close();
//edit - this needs to run or you will have duplicate values inserted
command.Parameters.Clear();
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
Even still I would seriously look into using statements for the connection and command, and have a try catch around opening the connection. But that is getting a bit off topic
User contributions licensed under CC BY-SA 3.0