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

Question

I have tried using methods that others have used here to fix their issue but none of them are working for me. I am new to the ASP.NET framework and cannot understand why the information I am trying to send to my database isn't working. Also, Visual Studios isn't giving em an error until I try to submit the new data which makes it difficult for me to pinpoint the problem.

namespace ylena_exercise
{
    public partial class KnockoutBind : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ylena_exercise;Integrated Security=True");
        protected void Page_Load(object sender, EventArgs e)
        {
            con.Open();
            GridView1.Visible = false;
        }

        protected void AddBtn_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("insert into Customers ('"+numid.Text+"','"+txtcustomer.Text+"','"+txtcontact.Text+"','"+txtaddress.Text+"','"+txtcity.Text+"','"+numpostcode.Text+"','"+txtcountry.Text+"')",con);
            cmd.ExecuteNonQuery();
            con.Close();
            GridView1.DataBind();
            Label1.Visible = true;
            Label1.Text = "Data Stored Successfully!";
            numid.Text = "";
            txtcustomer.Text = "";
            txtcontact.Text = "";
            txtaddress.Text = "";
            txtcity.Text = "";
            numpostcode.Text = "";
            txtcountry.Text = "";                
        }
    }
}

Is there something wrong with my data? Or perhaps the issue is ExecuteNonQuery?

Answer 1

Hey so I managed to figure out why ExecuteNonQuery was giving me issues. It turns out I simply did not match the table columns names with the variables in the SQL command.

Sorry for all of the trouble guys! Nevertheless, I appreciate all of the helpful suggestions and advice.

Answer 2

Include column names in your sql and set the values like this:

var command = "insert into customers (id, name, contact, address, city, postcode, country) values (@id, @name, @contact, @address, @city, @postcode, @country)";
SqlCommand cmd = new SqlCommand(command);
cmd.Parameters.AddWithValue("@id", numid.Text);
cmd.Parameters.AddWithValue("@name", txtcustomer.Text);
cmd.Parameters.AddWithValue("@contact", txtcontact.Text);
cmd.Parameters.AddWithValue("@address", txtaddress.Text);
cmd.Parameters.AddWithValue("@city", txtcity.Text);
cmd.Parameters.AddWithValue("@postcode", numpostcode.Text);
cmd.Parameters.AddWithValue("@country", txtcountry.Text);
cmd.ExecuteNonQuery();

[Courtesy: Soner Gönül & user2946329]

Answer 3

You missed Values in your insert statement your code should be like this:

SqlCommand cmd = new SqlCommand("insert into Customers values('"+numid.Text+"',....

Also you should always use parameterized queries to avoid SQL Injection:

SqlCommand cmd = new SqlCommand("insert into Customers values(@numid,...");
cmd.Parameters.AddWithValue("@numid",numid.Text);