Not saving new record Into SQL Server database

Question

The problem is my code is not inserting / saving a new record into my SQL Server database using asp.net c# and gives me no error.

Here is my code :

 public partial class AddNews_AddNews : System.Web.UI.Page
 {
        protected SqlConnection _connection;
        protected SqlCommand _command;
        protected SqlDataAdapter _adp;
        protected System.Data.DataTable _tbl;

        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void Button1_Click1(object sender, EventArgs e)
        {
            prepareConnection();
            _command.CommandText = "INSERT INTO" + drbdlSection.SelectedItem + "(Title,Contect) VALUES (" + titleTextBox.Text + "," + CKEditor1.Text + ");";
        }

        protected void prepareConnection()
        {
            _connection = new SqlConnection(@"Data Source=localhost;Initial Catalog=BrainStorms;User ID=sa;Password=xxx");
            _connection.Open();
            _command = new SqlCommand();
            _command.Connection = _connection;
        }
    }

Just to make sure you are not getting an error: press `Ctrl + Alt + E`, which opens the `Exceptions` Dialogue. Check `Thrown` under `Common Language Runtime Exceptions`. — gunr2171, Sep 17 '13 at 17:11
Looking at the answer (wow, how could I miss that?) my comment is not really relevant, but I would still suggest that you know about it. Very useful with debugging. — gunr2171, Sep 17 '13 at 17:23

score 5 · Accepted Answer · answered Sep 17 '13 at 17:17

5

You need to add _command.ExecuteNonQuery(); to the end of your Button1_Click1() method. You've set the query you want to run, but you never actually run it.

answered Sep 17 '13 at 17:17

dpw

1,576
9
14

score 4 · Answer 2 · edited May 23 '17 at 11:56

A command should be executed. Your code lacks the call to ExecuteNonQuery

    protected void Button1_Click1(object sender, EventArgs e)
    {
        ....
        command.ExecuteNonQuery();
    }

Said that, I really suggest to remove the string concatenation that makes your command text.
Your code is open to Sql Injection and parsing problems

So I would rewrite your code in this way

    protected void Button1_Click1(object sender, EventArgs e)
    {
        string commandText = "INSERT INTO " + drbdlSection.SelectedItem.ToString() + 
                             "(Title,Contect) VALUES (@title, @edit)"
        using(SqlConnection con = prepareConnection())
        using(SqlCommand command = new SqlCommand(commandText, con))
        {
            command.Parameters.AddWithValue("@title", titleTextBox.Text);
            command.Parameters.AddWithValue("@edit", CKEditor1.Text);
            command.ExecuteNonQuery();
        } 
    }

    protected SqlConnection prepareConnection()
    {
        SqlConnection con = new SqlConnection(......);
        con.Open();
        return con;
    }

In this rewrite I have changed the prepareConnection method to return an instance of a SqlConnection and removed the code to create the command.
This will allow to remove the global variables used for the connection and the command.

Then, in the button event, I have added the using statement around the connection and the command that will help in closing and destroying these instances also in case of exceptions.

Finally, the parameters are added in the command parameters collection, leaving the task to pass your values to the framework that knows better than you and me how to do this correctly.

A problem still exists with the concatenation of the table name, but I hope that you have a full control on this input having previously prepared the content of the dropdownlist.

makim · Answer 3 · 2013-09-17T17:25:55.890

You don´t execute the command, you don´t open the Connection and your Code is open to SqlInjection use Parameters!

public partial class AddNews_AddNews : System.Web.UI.Page
    {    
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click1(object sender, EventArgs e)
        {
            using(var connection = this.GetConnection())
            {
               using(var cmd = new SqlCommand())
               {
                  cmd.CommandText = "INSERT INTO " + drbdlSection.SelectedItem + "(Title, Contect) VALUES (@param1, @param2)";
                  cmd.Parameters.AddWithValue("@param1", titleTextBox.Text);
                  cmd.Parameters.AddWithValue("@param2", CKEditor1.Text);
                  cmd.Connection.Open();
                  cmd.ExecuteNonQuery();
               }
            }
        }

        protected SqlConnection GetConnection()
        {
            var connection = new SqlConnection(@"Data Source=localhost;Initial Catalog=BrainStorms;User ID=sa;Password=xxx");
            return connection;
        }

    }

score 1 · Answer 4 · answered Sep 17 '13 at 17:19

The Insert statement, your strings don't have single quotations around them (Unless you're actually entering single quotes in the textboxes)... not sure if that will cause a problem or not; I can't remember - but worth a shot.

And since you're using ADO.NET, I think you'll have to execute the _command object after you set the ComamndText I'm thinking...

Not saving new record Into SQL Server database

4 Answers4