Unable to insert data in C# sql database

Question

So when my form loads, it will connect to the database, and when i click on the button, it will insert an new row into my database, but i after i clicked it i didnt see any changes in my table.

namespace database
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection con;

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection();
            con.ConnectionString =
              "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\myworkers.mdf;Integrated Security=True;User Instance=True";
            con.Open();
            MessageBox.Show("OPEN!");
        }

        private void label1_Click(object sender, EventArgs e)
        {
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int x;
            SqlCommand thiscommand = con.CreateCommand();
            thiscommand.CommandText =
              "INSERT INTO player_info (player_id, player_name) values (10, 'Alex') ";

            x =  thiscommand.ExecuteNonQuery();  /* I used variable x to verify
                                                    that how many rows are
                                                    affect, and the return is 1.
                                                    The problem is that I don't
                                                    see any changes in my table*/
            MessageBox.Show(x.ToString());
            con.Close();
        }
    }
}

What are you using to check if the record got inserted? SQL Server management studio? — Shane Wealti, Mar 25 '12 at 20:12
Did you leave the username and password out for posting the question, or are they not in the code at all? — mowwwalker, Mar 25 '12 at 20:14
Why are you opening the connection in Form_Load? Open, use and close(maybe dispose) connections where you need them. — Tim Schmelter, Mar 25 '12 at 20:14
ExecuteNonQuery returns the number of affected rows. Why would it return anything but 1? — mowwwalker, Mar 25 '12 at 20:18
@ShaneWealti I checked the database explorer and see no changes. — qwr qwr, Mar 25 '12 at 23:10
@Walkerneo Yes, it means that the query successfully insert 1 row. But i just didnt see it on the db explorer — qwr qwr, Mar 25 '12 at 23:11
NVM, I find out using mysql is much more easier than sqlserver. the problem solved,thanks for the helps from everyone. — qwr qwr, Mar 25 '12 at 23:32

score 9 · Answer 1 · answered Mar 25 '12 at 20:12

9

You are using an attached Db file. That means that a Project-build makes a copy in the Bin folder, and you are dealing with 2 different versions of the Db. You are probably checking in the original.

This can actually be quite useful (fresh copy every time). If not, change the Copy-when property of the Db file.

answered Mar 25 '12 at 20:12

H H

263,252
30
330
514

Thank for your time. So what is the correct or more efficient way to do so? Because it seems like i insert successfully but i am not viewing the correct db file, should i upload the db online ?? sorry, i am new to this. – qwr qwr Mar 25 '12 at 23:12
You can view the results in the program itself, limit copying or (sometimes) copy the version from the bin\debug folder to your project root. – H H Mar 26 '12 at 18:56

Eric J. · Answer 2 · 2012-03-25T20:39:24.463

There's not enough detail to know exactly why the value is not showing up in your database, but I would suggest that you change your coding practice (described below), re-test, and then provide as much detail as you can about exactly where things are failing.

Coding Practice

You open your connection much earlier than you need to (which means you hold a valuable resource longer than necessary) and you do not clean up the connection or the command. They implement IDisposable, so you must call Dispose() on them. The easiest way to do that is with a using statement.

Suggested rewrite:

// Remove initialization of the SqlConnection in the form load event.

private void button1_Click(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection())
    {
        con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\myworkers.mdf;Integrated Security=True;User Instance=True";
        con.Open();
        // Optional: MessageBox.Show("OPEN!");
        int x;
        using (SqlCommand thiscommand = con.CreateCommand())
        {
            thiscommand.CommandText = "INSERT INTO player_info (player_id, player_name) values (10, 'Alex') ";

            x = thiscommand.ExecuteNonQuery();        /*I used variable x to verify that how many rows are affect, and the return is 1. The problem is that i dont see any changes in my table*/
            // Optional: MessageBox.Show(x.ToString());
        }
    }
}

Agreed on the `using`s but then also get the ConnString from the config. — H H, Mar 25 '12 at 20:23
`IDisposableS` should (not must) be disposed and your closing the connection what is redundant with the using-statement(dispose will close it implicitely). +1 anyway — Tim Schmelter, Mar 25 '12 at 20:26
@Tim: Agreed about not redundantly calling con.Close() (updated answer). What do you mean by the first part of your comment? — Eric J., Mar 25 '12 at 20:39
@EricJ.: Quote: "They implement IDisposable, so you **must** call Dispose() on them". It **is** good practise to do so but normally it's not essential since the garbage collector will dispose them sooner or later and you might have no problems even if you're not disposing them manually. http://stackoverflow.com/questions/2926869/do-you-need-to-dispose-of-objects-and-set-them-to-null — Tim Schmelter, Mar 25 '12 at 20:47

score 0 · Answer 3 · answered Mar 25 '12 at 20:22

0

The ExecuteNonQuery method is explained as follows on MSDN:

Executes a Transact-SQL statement against the connection and returns
the number of rows affected.

If you get 1 as return value, one record must have been inserted.

Have you forgotten to requery the table? The new record will not be displayed automatically in your application.

answered Mar 25 '12 at 20:22

Olivier Jacot-Descombes

104,806
13
138
188

So how do i check if the query works if the new record will not be displayed automatically in my application. Is there any better way to do so?? Like using online db or sql management studio??? sorry i am entirely new to this – qwr qwr Mar 25 '12 at 23:15
NVM, I find out using mysql is much more easier than sqlserver. the problem solved,thanks for the helps from everyone. – qwr qwr Mar 25 '12 at 23:32
@qwrqwr: If you filled a `ListBox` with records from your table, for example, you will have to fill the `ListBox` again, because the `ListBox` does not know anything about changes to the db tables. – Olivier Jacot-Descombes Mar 26 '12 at 14:10

score 0 · Answer 4 · answered Mar 26 '12 at 19:35

You could consider running SQL Profiler to see what is actually being sent to your database. You can capture the SQL and then try executing that code directly against the database.

"player_id" implies this is a primary key if that's the case the insert would fail.

I would also recommend changing your approach, as others have, to the "using" style as this will manage your resources for you and make sure you do not leave connections "hanging around".

Unable to insert data in C# sql database

4 Answers4