How to write a query in SQL server CE to check data?

Question

1.00/5 (1 vote)

See more:

Hi,
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:

C#

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:

C#

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();
            }

Posted 3-Jun-21 21:26pm

Alex Dunlop

Updated 3-Jun-21 21:35pm

v5

Add a Solution

Comments

PIEBALDconsult 4-Jun-21 12:58pm

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.

Richard Deeming 4-Jun-21 13:04pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation / interpolation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Alex Dunlop 4-Jun-21 13:09pm

Please help me to know how I can use 'IF' statement in SQL CE and C#.
Please give me an example.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2021-06-03T21:35:00

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

SQL

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[^]