Why am I not getting any errors but database has no record inserted?

Question

I'm trying to add a record to a database in a control in C#:

public partial class ctrl_Register : UserControl
{
    public ctrl_Register()
    {
        InitializeComponent();
    }

    private void btn_reg_Click(object sender, EventArgs e)
    {
        bool ok = false;
        int col = 1;
        if (tBox_username.Text != "")
        {
            if (tBox_password.Text != "")
            {
                if (cBox_colour.Text != "")
                {
                    ok = true;
                }
            }
        }

        if (ok)
        {
            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.data2cString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = @"INSERT INTO [Table](Id,username,password,colour,bestTime) VALUES ('"+tBox_username.Text+"','"+tBox_password.Text+"','"+cBox_colour.Text+"')";

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("added");
                    }
                    catch (SqlException s)
                    {
                        MessageBox.Show(s.Message.ToString(), "Error Message");
                    }
                }
            }
        }
        else
        {
            MessageBox.Show("no insert");
        }
    }
}

The message box "added" shows up, so the code definitely gets to that point. However when I go check my database there is no new record.

This was my create table code:

CREATE TABLE [dbo].[Table] 
(
    [Id]       INT          IDENTITY (0, 1) NOT NULL,
    [username] VARCHAR (50) NOT NULL,
    [password] VARCHAR (50) NOT NULL,
    [colour]   VARCHAR (50) DEFAULT ('Orange') NULL,
    [bestTime] INT          DEFAULT ((0)) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

It's definitely the correct connection string in the settings file. But when I go to the data2.mdf file in the database explorer and go to 'Show Data' for the table, only NULL is present.

Answer 1

First, if you are going to write SQL code. Check for errors. You are supplying three values for five columns, so that is likely an error.

Second, use parameterized queries. Don't put user input directly into the query string. This is dangerous and well as an impediment to the SQL optimizer.

Third, don't name a table Table. That is a reserved word. Use a non-reserved word that better describes the entity.

Answer 2

You didn't show the connection string - but since you're saying to "go to the Data2.mdf file in the server explorer, I assume you're probably using an AttachDbFileName= approach.

The whole 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

1. install SQL Server Express (and you've already done that anyway)

2. install SQL Server Management Studio Express

3. create your database in SSMS Express, give it a logical name (e.g. Data2)

4. 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=Data2;Integrated Security=True
   

   and everything else is exactly the same as before...

Also see Aaron Bertrand's excellent blog post Bad habits to kick: using AttachDbFileName for more background info.

And also: do NOT ever concatenate together your SQL command like this - this is just asking for a script kiddie to explore SQL injection to attack your site. Use parametrized queries - always, no exception.

Answer 3

Change your command text and use parameters like:

cmd.CommandText = @"INSERT INTO [Table](username,password,colour) VALUES(@username, @password, @colour)";

cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = tBox_username.Text;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value =  tBox_password.Text;
cmd.Parameters.Add("@colour", SqlDbType.VarChar).Value =  tBox_colour.Text;