Click here to Skip to main content
15,445,242 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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 gives this runtime error:
System.Data.SqlServerCe.SqlCeException: 'There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]'

What I have tried:

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++)
                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}" +
Updated 3-Jun-21 21:35pm
PIEBALDconsult 4-Jun-21 12:58pm     CRLF
Yeah, definitely avoid double-hitting, it's wasteful. You would also be better off using a transaction if you do. I recommend looking at using a MERGE statement if the database system allows it.
Alex Dunlop 4-Jun-21 13:09pm     CRLF
Please help me to know how I can use 'IF' statement in SQL CE and C#. Please give me an example.

1 solution

While you could execute an anonymous T-SQL block, for a simple update I'd recommend changing this to a 'traditional' UPDATE statement and using bind variables. Something like
UPDATE [MyData] 
SET Wo   = @woToUpdate,
    Code = @codeToUpdate
WHERE Wo   = @woToMatch
AND   Code = @codeToMatch

In general you should think DML operations as set operations, not something one should loop through.

For executing the statement using the bind variables, have a look at Properly executing database operations[^]
Share this answer
PIEBALDconsult 4-Jun-21 12:56pm    
Or a MERGE statement? Unsure CE supports that.
Wendelius 4-Jun-21 15:21pm    
As far as I know, it doesn't support MERGE.
PIEBALDconsult 4-Jun-21 15:41pm    
Yeah, it seems not. :(

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900