CODEWITHSUNDEEP
c#sqlasp.netsql-server
LearningFred
LearningFred

Reputation: 1

SqlException was unhandled by user code?

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 = "";
    }
}

Upvotes: 0

Views: 2171

Answers (1)

Steve
Steve

Reputation: 216263

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.

Upvotes: 4

Related Questions