C# - Writing data in local database

Question

Im working on a program to save Passwords.

I want to write them in a local database file (Name: Database.sdf).

This is my SQL query:

SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = "Data Source = Database1.sdf";
conn.Open();
SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Passwords (Nr, Username, Password, Email, Website, Description, Rating, DateTime) VALUES ('" + UsernameBox.Text + "', " + PasswordBox.Text + "', '" + EmailBox.Text + "', '" + WebsiteBox.Text + "', '" + DescriptionBox.Text + "'," + RatingValue.Value + "," + DateTime.Now + ")", conn);

conn.Close();

But somehow it doesnt work. This is my database setup: https://i.sstatic.net/Q348R.jpg

I want the Nr auto increment 1, ( i have setted that in the database). I hope someone can help me out. I've tried alot of things i found on google, but nothing seems to work.

Greetz, Rajco

1) Which database are you using? 2) Use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. 3) You didn't execute your command with ExecuteNonQuery. 4) Don't store your passwords as a plain text. — Soner Gönül
– Soner Gönül, Commented Mar 20, 2014 at 12:35
Your query should be SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Passwords (Username, Password, Email, Website, Description, Rating, DateTime) VALUES ('" + UsernameBox.Text + "', " + PasswordBox.Text + "', '" + EmailBox.Text + "', '" + WebsiteBox.Text + "', '" + DescriptionBox.Text + "'," + RatingValue.Value + "," + DateTime.Now + ")", conn); — Suresh Kumar Veluswamy
– Suresh Kumar Veluswamy, Commented Mar 20, 2014 at 12:35
To add to Soner: 1. Use entity framework, 2. don't store plain text passwords! — Jon Barker
– Jon Barker, Commented Mar 20, 2014 at 12:36
Im using a local database (which i added in 'Add new item --> Local database). And 2, sure lets do that, and 3 how should i execute it then? — Rajco
– Rajco, Commented Mar 20, 2014 at 12:39

Joakim Andersson · Accepted Answer · 2014-03-20 12:52:03Z

2

you never executed the command.

cmd.ExecuteNonQuery()

before the conn.Close()

And you should look upp parameters to avoid sql-injection

http://www.dotnetperls.com/sqlparameter

        SqlCeCommand cmd = new SqlCeCommand(@"
            INSERT INTO Passwords 
                (Username, Password, Email, Website, Description, Rating, DateTime) 
            VALUES 
                (@UserName, @Password, @Email, @WebSite, @Description, @RatingValue, @DateNow)", conn);
        cmd.Parameters.AddWithValue("UserName", UsernameBox.Text);
        cmd.Parameters.AddWithValue("Password", PasswordBox.Text);
        cmd.Parameters.AddWithValue("Email", EmailBox.Text);
        cmd.Parameters.AddWithValue("WebSite", WebsiteBox.Text);
        cmd.Parameters.AddWithValue("Description", DescriptionBox.Text);
        cmd.Parameters.AddWithValue("RatingValue", RatingValue.Value);
        cmd.Parameters.AddwithValue("DateNow", DateTime.Now);

Try to use this instead. It adds your data as parameters for the query instead.

edited Mar 20, 2014 at 12:52

answered Mar 20, 2014 at 12:36

Joakim Andersson

374 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Rajco Over a year ago

When i have the execute query, it somehow doesnt work. Cause im using a try/catch.

Rajco Over a year ago

There was an error pasing the query. [Token line number = 1, token like offset = 113, token in error = ,]

Joakim Andersson Over a year ago

Check out my edited answer, set your data as parameters instead. You probably have some character missing or extra added.

Joakim Andersson Over a year ago

I'm guessing Nr in the database is the ID for the table? You don't set that as it sets automatically.

Rajco Over a year ago

what reference i need to add for that? cause using System.Data.SqlClient; isnt it..

|

Collectives™ on Stack Overflow

C# - Writing data in local database

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related