How do I update data in SQL using C# windows form without affecting other fields?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

, +

Hello,

I have created a Windows Form using C# Windows Application to add, update, delete and show user data from SQL.

All the queries are working perfectly but when I am updating a particular user data, the fields that I am leaving empty are becoming blank in SQL as well.

I have 3 TextBoxes:

Username
Password
Name

So if I put values in Username and Password and leave the Name field blank, then it is updating the Username and Password but making the Name field blank in the SQL.

I want the unchanged fields to remain as they were.

Please help.

What I have tried:

using (SqlConnection connection = new SqlConnection("Data Source=PRIMO-CHALICE;Initial Catalog=NewsClip;Integrated Security=SSPI"))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;            // <== lacking
                    command.CommandType = CommandType.Text;
                    command.CommandText = "UPDATE NewsClip_Login SET Username = @Username, Password = @Password, Name = @Name WHERE Username='" + this.EmployeeUsernameAdd.Text + "';";
                    command.Parameters.AddWithValue("@Username", EmployeeUsernameAdd.Text);
                    command.Parameters.AddWithValue("@Password", EmployeePasswordAdd.Text);
                    command.Parameters.AddWithValue("@Name", EmployeeNameAdd.Text);

                    try
                    {
                        connection.Open();
                        int recordsAffected = command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }

Posted 10-May-18 20:02pm

Primo Chalice

Updated 10-May-18 21:50pm

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2018-05-10T20:16:00

Two major problems with that:
1) Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Think about what you are doing! You pass the username as a parameter, so why the heck are you tacking it on at the end and making your code vulnerable as well?

2) Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]
To give you an idea how we feel about that kind of thing, see here: Code Crime 1[^]

Then we come to minor problems: why are you setting the username, when you know it's the same value already? Because if it was different, it wouldn't match any rows!

And finally: why is it changing the name field? Because you told it to...
If you want the name to remain untouched when it is blank, you need to check in your C# code, and either use a different query, or pass the current value instead. If you tell SQL "set it to this" then that is exactly what it will do...

jsc42 · Answer 2 · 2018-05-10T21:51:00

Further to OG's response...
I assume that

this.EmployeeUsernameAdd.Text

is the old username that you are replacing, so you should use (for example) an @OldUsername parameter.

To not change Name if the user has left the field blank, use something like ...

SQL

... Name = CASE WHEN @Name = '' THEN Name ELSE @Name END ...

Similarly for the Username and Password fields in case they are blank meaning 'no change'.
This saves you having to either dynamically create SQL or to have dozens of variants.

Put the SQL (with parameterisations) into a Stored Procedure. This will help segregate responsibilities so, for example, if you tables change name / columns are restructured, you can just change the Stored Procedure without having to edit / compile / redeploy your source code.

Finally: I hope that I have misread your SQL and you are not actually really storing passwords in plain text. I hope that the sample is just an extract and that any passwords are being saved as encrypted text, either by actually being sent encrypte (preferred) or you are using SQL Server encryption on the database itself.

How do I update data in SQL using C# windows form without affecting other fields?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0