SQL CE Not entering information to DB

Question

When i run my application, it goes through to the end just fine, but when i check my DB afterwards, it never shows any data. Here is my code:

private void button1_Click(object sender, EventArgs e)
    {
        string saltedcryps = saltpassword(10);
        string passWithSalt = (textBox1.Text + saltedcryps);
        string hashedResult = hashPassAndSalt(passWithSalt);
        if (checkPasswordsMatch() == "B")
        {
            SqlCeConnection myConnection = new SqlCeConnection("Data Source = pwdb.sdf");
                    try
                    {
                        myConnection.Open();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                    SqlCeCommand myCommand = new SqlCeCommand("INSERT INTO PW Values ('Master', '" + saltedcryps + "', '" + hashedResult + "');", myConnection);
                    myCommand.ExecuteNonQuery();
                    myConnection.Close();
                    this.Hide();

        }
    }
    private string checkPasswordsMatch()
    {
        if (textBox1.Text == "")
        {
            MessageBox.Show("Passwords cannot be empty");
            return "A";
        }
        else
        {
            MessageBox.Show(textBox1.Text == textBox2.Text ? "Thanks for registering!" : "Your passwords do not match");
            return "B";
        }
    }
    private string saltpassword(int size)
    {
        RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
        byte[] buff = new byte[size];
        crypto.GetBytes(buff);
        return Convert.ToBase64String(buff);
    }
    private string hashPassAndSalt(string passWithSalt)
    {
        HashAlgorithm hashAlg = new SHA256CryptoServiceProvider();
        byte[] bytValue = System.Text.Encoding.UTF8.GetBytes(passWithSalt);
        byte[] bytHash = hashAlg.ComputeHash(bytValue);
        string base64 = Convert.ToBase64String(bytHash);
        return base64;
    }
}

It is the button1_Click that the problem lies in. When it runs myCommand.ExecuteNonQuery(); it never throws an exception, it just carries on, without actually entering any information...

Anyone have a clue??

Why do you only open your connection in your `try` block? What happens if you move `SqlCeCommand myCommand = new SqlCeCommand("INSERT INTO PW Values ('Master', '" + saltedcryps + "', '" + hashedResult + "');", myConnection); myCommand.ExecuteNonQuery(); myConnection.Close(); this.Hide();` into your `try` block? — Brian, Oct 08 '13 at 17:34
still doesnt work. the try block tries to open the connection, then passes by the catch block if successful then runs the next code. its basically the same thing either way — tony b, Oct 08 '13 at 17:37
Look in your bindebug folder for a copy of the datbase file, best approach is to use full path in the connection string — ErikEJ, Oct 08 '13 at 17:41
yes but then when i deploy it, it wont work as the install folder will be different? — tony b, Oct 08 '13 at 17:42
Why do you think “it never shows any data”? Where is your database? Is it part of the project? What's its build action? You have to explain these things because we can't see your screen. — Dour High Arch, Oct 09 '13 at 00:28

score 0 · Accepted Answer · answered Oct 08 '13 at 17:46

0

Try this:

    if (checkPasswordsMatch() == "B")
        {
            SqlCeConnection myConnection = new SqlCeConnection("Data Source = pwdb.sdf");
                    try
                    {
                        myConnection.Open();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                    SqlCeCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandType = CommandType.Text;
myCommand.CommandText = "INSERT INTO PW Values ('Master', '" + saltedcryps + "', '" + hashedResult + "');"
                    myCommand.ExecuteNonQuery();
                    myConnection.Close();
                    this.Hide();

        }

If this doesnt works, try to put the absolut path on the connection string.

answered Oct 08 '13 at 17:46

Diego

738
1
7
19

new myConnection.CreateCommand() creates a red line, type or namespace myConnection could not be found. and '" + hashedResult + "'); creates "newline in constant" – tony b Oct 08 '13 at 17:51
that works, but what about when i deploy the application? the database wont be in the same folder on the clients computer? – tony b Oct 08 '13 at 18:04
Have you tried this?: SqlCeConnection myConnection = new SqlCeConnection("Data Source = /pwdb.sdf"); – Diego Oct 08 '13 at 19:02
This answer contains some bad advice; it is very bad to catch an exception and then continue using the object that threw the exception. It is also bad to show a MessageBox inside database commands; what if it's called from a background thread? `Data Source = pwdb.sdf` is only going to work if the database is embedded with your app, and even then you can insert into it only if you are running as an admin. That is a security risk. – Dour High Arch Oct 09 '13 at 00:33

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

That connection string does not look right; unless you are doing something very very strange you should be using Data Source=|DataDirectory|pwDB.sdf.

Why do you think your database “never shows any data”? Where are you looking for the data? In the original source data in your project directory? That is almost certainly wrong; when you deploy your application you aren't deploying your source are you? You need to look in the deployment folder, consult this answer.

edited May 23 '17 at 10:31

Community

1
1

answered Oct 08 '13 at 17:50

Dour High Arch

21,513
29
75
90

the database is linked to the project. I made it in the project? – tony b Oct 08 '13 at 17:53
That's fine, @tony, but you are not deploying the project when you deploy your application, are you? Please provide more detail as I do not understand what you are doing. – Dour High Arch Oct 08 '13 at 18:05
i am making an aplication that stores passwords securely. This code i provided registers the password to the database. So how do i send the database with the application when i send the software to a client, as the install folder in their machine will not be the same as the folder on my computer, so how do i reference that?? – tony b Oct 08 '13 at 18:08
@tony, how do you “send the software to a client” and how do they install it? – Dour High Arch Oct 09 '13 at 00:30

score 0 · Answer 3 · answered Oct 08 '13 at 18:08

0

Put the try catch around the ExecuteNonQuery so you can see what is the exception if there, otherwise must be something with your connection string (DataSource=pwDB.sdf doesnt look right to me) must have user Id; Password; dataSource=your IP and initial catalog:

        SqlCommand Command = new SqlCommand(YourQuery,myConnection);

        myConnection.Open();
        int Rows=0;
        try
        {
            Rows = Command.ExecuteNonQuery();
            myConnection.Close();

        }
        catch (Exception ex)
        {
            conSMS.Close();
            string Msg = ex.Message;
                            //I log my exceptions
            //Log("ERROR: "+RemoveSQ(Query));
            //Log(RemoveSQ(Msg));
        }

        //check Rows here if there is no exception

answered Oct 08 '13 at 18:08

cvetyab

103
7

its not on a server or anything like that, it was created in the project – tony b Oct 08 '13 at 18:10
So where is the database then?if it will run offline only on the user computer, then you have to have a script to create it on their computer as well in order for them to access it – cvetyab Oct 08 '13 at 18:17
i thought you made the database in the application then it would copy that database with the application and use that one? – tony b Oct 08 '13 at 18:46
DataSource=DataDirectory\\pwdb.sdf, DataDirectory depends on how you install your application, I know Web apps use App_Data folder not sure what is yours – cvetyab Oct 08 '13 at 19:18

SQL CE Not entering information to DB

3 Answers3

Linked