C# Form not inserting values into SQL Server database

Question

I have a simple create a new user form which takes values from two text boxes, username and password. The button2 click event should take these values and insert them into the Users table in the database. However, when I run my code the message box appears to say the data has been added, I cannot see the data in the database using VS2010.

See screen shot for the database connection in VS. I have also created a datasource of the database in VS.

Any Ideas?

Much appreciated.

private void button2_Click(object sender, EventArgs e)
    {
        string username = txtUsername.Text;
        string password = txtPassword.Text;
        string sqlquery;
        string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\Nick\Documents\Visual Studio 2010\Projects\DebenhamsProjectOffice V.01\DebenhamsProjectOffice V.01\DebenhamsProjectOfficeDatabase.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True";
        SqlConnection cn = new SqlConnection(connection);
        try
        {
            cn.Open();
        }
        catch (Exception)
        {
            MessageBox.Show("Unable to connect to Database");
        }

        sqlquery = "INSERT INTO Users (Username, Password) VALUES ('" + txtUsername.Text + "','" + txtPassword.Text + "')";
        try
        {
            SqlCommand command = new SqlCommand(sqlquery, cn);
            command.Parameters.AddWithValue("Username", username);
            command.Parameters.AddWithValue("Password", password);
            command.Parameters.Clear();
            MessageBox.Show("User Added");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        txtUsername.Text = "";
        txtPassword.Text = "";
        cn.Close();
    }

enter image description here

marc_s · Accepted Answer · 2013-04-06 18:09:06Z

5

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g. DebenhamsProjectOfficeDatabase)
connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:
```
Data Source=.\\SQLEXPRESS;Database=DebenhamsProjectOfficeDatabase;Integrated Security=True
```
and everything else is exactly the same as before...

Also: you should always use parametrized queries and not concatenate together your SQL statements (especially not when user input is included!) to (a) avoid any danger of SQL injection attacks, and to (b) improve performance!

answered Apr 6, 2013 at 18:09

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

2 Comments

NickB6 Over a year ago

Thank you very much for the advice I will try this, apologies for the naivety I am a novice c# programmer. Much appreciated.

Dour High Arch Over a year ago

catch (Exception) is another flawed approach.

Adil · Accepted Answer · 2013-04-06 18:08:55Z

2

You have to call Command.ExecuteNonQuery() in order to take the effect of insert statement.

try
{
      SqlCommand command = new SqlCommand(sqlquery, cn);
      command.Parameters.AddWithValue("Username", username);
      command.Parameters.AddWithValue("Password", password);
      command.ExecuteNonQuery();
      command.Parameters.Clear();
      MessageBox.Show("User Added");
}
catch (Exception ex)
{
      MessageBox.Show(ex.Message);
}

answered Apr 6, 2013 at 18:08

Adil

148k25 gold badges217 silver badges207 bronze badges

Comments

user2184697 · Accepted Answer · 2013-04-06 19:15:34Z

Just trying a code fix. Some elements are crucial, some are just elegant. Try it, it might work., or could point to where the error is:

private void button2_Click(object sender, EventArgs e)
    {
        string username = txtUsername.Text;
        string password = txtPassword.Text;
        string sqlquery;

        //Put away the apostrophes and used twice double quotations for
        //the full path of the database file:
        string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\Nick\Documents\Visual Studio 2010\Projects\DebenhamsProjectOffice V.01\DebenhamsProjectOffice V.01\DebenhamsProjectOfficeDatabase.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True";
        SqlConnection cn = new SqlConnection(connection);

        /* Better to let the program fail than think it's open and moving on
        removed try, catch*/
        cn.Open();


        //Why using your TextBoxes values if you already created strings?
        //changed

        //you should also be careful users can't type something like "') in the      
        //textboxes or they may cause a SQL injection

        sqlquery = "INSERT INTO Users (Username, Password) VALUES ('" + username + "','" + password + "')";

        try
        {
            SqlCommand command = new SqlCommand(sqlquery, cn);
            /* unnecessary since you already built a query command.Parameters.AddWithValue("Username", username);
            command.Parameters.AddWithValue("Password", password);
            command.Parameters.Clear();   */

            //Missing!!
            command.ExecuteNonQuery();
            MessageBox.Show("User Added");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

        //Elegance
        txtUsername.Clear();
        txtPassword.Clear();
        cn.Close();
    }

Thank you ever so much for taking the time to fix this. It now works!

Collectives™ on Stack Overflow

C# Form not inserting values into SQL Server database

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related