0

I'm trying to write a query that can compare each row of a DGV with SQL Server CE and if there were any row that its first and third column is equal to one of SQL rows, do nothing and if there were no duplicate value, then add that complete row to the SQL SERVER CE file. I tried the following code but it throws runtime error:

SqlCeConnection connection2 = new SqlCeConnection();
connection2.ConnectionString = @"DataSource = WO_No.sdf; password= rss900";
SqlCeCommand mycommand = new SqlCeCommand();
mycommand.Connection = connection2;

for (int i = 0; i < DataGridView3.Rows.Count; i++)
{
connection2.Open();
mycommand.CommandText = $"IF EXISTS (SELECT * FROM [MyData] WHERE Wo={DataGridView3.Rows[i].Cells[0].Value} AND Code={DataGridView3.Rows[i].Cells[2].Value}" +
$"BEGIN" +
$"" +
$"END" +
$"ELSE" +
$"BEGIN" +
$"INSERT INTO [MyData] Wo={DataGridView3.Rows[i].Cells[0].Value}, EqN={DataGridView3.Rows[i].Cells[1].Value}, Code={DataGridView3.Rows[i].Cells[2].Value}, Work={DataGridView3.Rows[i].Cells[3].Value}, Cost={DataGridView3.Rows[i].Cells[4].Value}" +
$"END";
mycommand.ExecuteNonQuery();
connection2.Close();
}
Alex
  • 33
  • 7
  • Use a CE application to verify the command text is good. The database tools are much better if giving syntax errors than trying from c#. – jdweng Jun 04 '21 at 13:30
  • 1
    a) Use parameters, not string interpolation to get the values into the query. b) There's a `)` missing. c) There the way you concatenate the strings will lead to several spaces missing. d) The `INSERT` syntax doesn't seem right. e) Maybe there are more problems... – sticky bit Jun 04 '21 at 13:38

0 Answers0