SqlException was unhandled by user code?

Question

I'm currently started in asp.net C# on school, and now they want me to insert data into SQL Server. Now I came so far with this code, but when I try to add something into the database, I get the error:

SqlException was unhandled by user code

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Column name or number of supplied values does not match table definition.

Can someone explain me what I am doing wrong here?

Thanks in advance.

Fred.

public partial class AddMovie : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["VideoStoreConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
            con.Open();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
            SqlCommand cmd = new SqlCommand("INSERT INTO dbo.Movie values('" + txtmovie.Text + "')", con);
            cmd.ExecuteNonQuery();
            con.Close();
            Label1.Visible = true;
            Label1.Text = "Your Movie is Stored Successfully!";
            txtmovie.Text = "";
    }
}

Can you show us the definition of your table? If your columns don't have default values (such as an auto-incrementing primary key column) then this error will be thrown. — Simon Whitehead, Mar 15 '15 at 10:43
Please meet [Bobby Tables](http://stackoverflow.com/q/332365/11683), please never open the connection from `Page_Load`, and please use [`using`](https://msdn.microsoft.com/en-us/library/yh598w02.aspx) for both the connection and the command. As for the actual problem, see the syntax for [`insert`](https://msdn.microsoft.com/en-us/library/ms174335.aspx) and unless your table has one column, provide the list of column names into which you insert. — GSerg, Mar 15 '15 at 10:46

Steve · Answer 1 · 2015-03-15T13:01:32.767

If you have an insert command and do not want to specify the columns where you want to insert values then you need to supply VALUES for all the columns in the table in the correct order in which the columns appears inside the table.

In this case I suppose that your table Movie has more columns than just the one supposed to contain the movie name (IE Title ?). So you need to write the values for all columns or specify which column should receive the value for the txtmovie textbox

SqlCommand cmd = new SqlCommand(@"INSERT INTO dbo.Movie (Title) 
                                values(.....) ....

Said that, this is not the correct way to supply a command text to your database.
You should always use a parameterized query

protected void Button1_Click(object sender, EventArgs e)
{
    string cmdText = @"INSERT INTO dbo.Movie  (Title) values(@name)";

    using(SqlConnection con = new SqlConnection(.......constring here...))
    using(SqlCommand cmd = new SqlCommand(cmdText, con))
    { 
        con.Open();
        cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = txtmovie.Text;
        cmd.ExecuteNonQuery();
        ....
    }
}

Finally, generally speaking, the best approach in using an SqlConnection (or every other kind of Disposable object) is to create it locally inside a using statement without keeping it in a global variable and opening/closing it in different places.
The Using Statement is very useful for this approach because it guarantees that the connection is closed and disposed also in case of exceptions. If you can try always to keep at minimum the number of global objects.

Th OP shows that Connection opened at Page Load and Closed at Button click. Thats also needs to be changed. — SelvaS, Mar 15 '15 at 10:46
@SelvaTS And it has been changed - to what it should have been in the first place. — GSerg, Mar 15 '15 at 10:50
@GSerg First of all the data access must be isolated from Business. And the Connection, Command objects are wrapped with using statements. — SelvaS, Mar 15 '15 at 10:53

SqlException was unhandled by user code?

1 Answers1