CODEWITHSUNDEEP
c#
qwr qwr
qwr qwr

Reputation: 1059

Unable to insert data in C# sql database

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();
        }
    }
}

Upvotes: 2

Views: 997

Answers (4)

Simon Martin
Simon Martin

Reputation: 4231

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".

Upvotes: 0

Eric J.
Eric J.

Reputation: 150108

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());
        }
    }
}

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

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.

Upvotes: 0

Henk Holterman
Henk Holterman

Reputation: 273244

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.

Upvotes: 9

Related Questions