I cannot save data to my SQL database using C#

Question

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

Sounds like you're trying to insert NULL into a column that is defined in the database as "do not allow nulls." — Robert Harvey
– Robert Harvey, Commented Jan 2, 2019 at 19:58
It means you are passing a null value to a column in your table which does not except NULLS — Ryan Wilson
– Ryan Wilson, Commented Jan 2, 2019 at 19:58
Look at the schema information for the table NumericTable. Do you have other columns that are not set from your query? Are these columns allowed to get NULL values? — Steve
– Steve, Commented Jan 2, 2019 at 20:00
Provide the table structure, hope you have some primary key and not setting value during insert. — Shan
– Shan, Commented Jan 2, 2019 at 20:00
Side note: if these columns contain numerical data - why on earth are they defined as nvarchar(50) ?? Use the most appropriate datatype - always, no exceptions. And nvarchar(50) is certainly NOT the most appropriate datatype to store numerical values! Use int, bigint, or decimal - whatever suits your needs — marc_s
– marc_s, Commented Jan 2, 2019 at 22:24

NicoRiff · Accepted Answer · 2019-01-02 20:30:46Z

2

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/

edited Jan 2, 2019 at 20:30

answered Jan 2, 2019 at 20:22

NicoRiff

4,8933 gold badges29 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ryan Wilson Over a year ago

this will not work if there is a record with an ID = 1 as it would violate the primary key constraint. I don't imagine that was intentional, just being nit-picky. If the OP desired to insert their own primary key, they would need to either query their table first to find a unique integer or modify their query to find a unique integer value and use it as the primary key. In any case, using an Identity auto-increment is much easier.

Caius Jard Over a year ago

I recommend not using this method of data access st all, and learn Entity Framework instead! :)

NicoRiff Over a year ago

@RyanWilson you are right i will make the corresponding edit

Ryan Wilson Over a year ago

@NicoRiff Thank you for updating your answer. :)

NicoRiff Over a year ago

@CaiusJard it is matter of what everyone likes to use. I prefer to use Dapper as I personally do not like EF. If OP wants to have more control over DB and go on writing queries I will definitely go with Dapper. But that is my preference.

|

Caius Jard · Accepted Answer · 2019-01-02 20:32:20Z

2

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

edited Jan 2, 2019 at 20:32

answered Jan 2, 2019 at 20:26

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Collectives™ on Stack Overflow

I cannot save data to my SQL database using C#

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related