CODEWITHSUNDEEP
c#
Francis Naval
Francis Naval

Reputation: 13

I cannot save data to my SQL database using C#

I am new to C#. I am trying to save the numbers into a SQL Server database table (locally) but I get an error:

Cannot insert the value NULL into column

My code:

private void SaveBtn_Click(object sender, EventArgs e)
{
     try
     { 
         SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\fn1965\Desktop\Work\TESTDB\NumDB.mdf;Integrated Security=True;Connect Timeout=30");

         conn.Open();
         string insert_query = "INSERT into [NumericTable] (Num1, Num2, Total) VALUES (@Num1, @Num2, @Total)";

         SqlCommand cmd = new SqlCommand(insert_query, conn);

         cmd.Parameters.AddWithValue("@Num1", textBox1.Text);
         cmd.Parameters.AddWithValue("@Num2", textBox2.Text);
         cmd.Parameters.AddWithValue("@Total", textBox3.Text);

         cmd.ExecuteNonQuery();

         MessageBox.Show("Record saved");

         conn.Close();
     }
     catch (Exception ex)
     {
         MessageBox.Show("EROR:"+ ex.ToString());
     }
}

Table schema

enter image description here

Upvotes: 2

Views: 724

Answers (2)

Caius Jard
Caius Jard

Reputation: 74595

That screenshot you took of your table columns design; get back to that, then click the id column, look in the Properties grid for Identity Specification (might need to expand it) and set it to Yes. Set other properties relevant to your needs and save the table.

Borrowed from another SO question:

enter image description here

There are ways to do this from script but they're generally longer/more awkward than using the UI in management studio.

This will (should) change th column so it auto inserts an incrementing number into itself when you insert values for other rows. Someone else has posted an answer as to how to insert values for it yourself but my recommendation to you as a learner is to use auto increment to save the additional needless complication of providing your own primary key values

Upvotes: 2

NicoRiff
NicoRiff

Reputation: 4883

You can see in the image that the column Id is the only one that does not support null values. Since the column is not identity and as you are not providing a value on your insert, then the INSERT fail with the given exception. This code will work (only if there isn't a record with Id = 1 already):

        string insert_query = "INSERT into [NumericTable] (Num1,Num2,Total, Id) Values (@Num1,@Num2,@Total, @id)";
        SqlCommand cmd = new SqlCommand(insert_query, conn);
        cmd.Parameters.AddWithValue("@Num1", textBox1.Text);
        cmd.Parameters.AddWithValue("@Num2", textBox2.Text);
        cmd.Parameters.AddWithValue("@Total", textBox3.Text);
        cmd.Parameters.AddWithValue("@Id", 1);
        cmd.ExecuteNonQuery();

I assume that this is obviously not the desired fuctionality. What you should do is either set the Id column to identity = true or set a value on the insert.

I also encourage you to not use AddWithValue method since it can lead you to some undesired problems. You can read more here: https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

Upvotes: 2

Related Questions