CODEWITHSUNDEEP
c#sqlsql-server
user10898404
user10898404

Reputation:

How can I solve this error "Does not allow NULL, Insert Aborted"?

I'm trying to call a stored procedure to make an insert:

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = @"Data Source=******;Initial Catalog=**********;Integrated Security=True";

    using (SqlConnection sqlCon = new SqlConnection(connectionString: connectionString))
    {
        using (SqlCommand cmd = new SqlCommand("InsertAngajat", sqlCon))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Nume", SqlDbType.VarChar).Value = textBox1.Text;
            cmd.Parameters.Add("@Prenume", SqlDbType.VarChar).Value = textBox2.Text;
            cmd.Parameters.Add("@DataNasterii", SqlDbType.Date).Value = textBox4.Text;
            cmd.Parameters.Add("@DataAngajare", SqlDbType.Date).Value = DateTime.Today.ToString();
            cmd.Parameters.Add("@cnp", SqlDbType.Char).Value = textBox3.Text.ToCharArray();
            cmd.Parameters.Add("@Localitate", SqlDbType.VarChar).Value = textBox8.Text;
            cmd.Parameters.Add("@Judet", SqlDbType.VarChar).Value = textBox10.Text;
            cmd.Parameters.Add("@Strada", SqlDbType.VarChar).Value = textBox9.Text;
            cmd.Parameters.Add("@Departament", SqlDbType.VarChar).Value = comboBox1.Text;
            cmd.Parameters.Add("@Telefon", SqlDbType.Char).Value = textBox5.Text.ToCharArray();

            if (checkBox1.Checked) 
                cmd.Parameters.Add("@Sex", SqlDbType.Char).Value = 'M';
            else if (checkBox2.Checked) 
                cmd.Parameters.Add("@Sex", SqlDbType.Char).Value = 'F';
            else 
                MessageBox.Show("Nu a fost bifat sexul");

            cmd.Parameters.Add("@Numar", SqlDbType.Int).Value = Convert.ToInt32(textBox11.Text);
            cmd.Parameters.Add("@Salariu", SqlDbType.Int).Value = Convert.ToInt32(textBox6.Text);

            sqlCon.Open();
            cmd.ExecuteNonQuery();
        }
    }

    this.Close();  
}

But this is the error I get when I press the button and save everything.

https://i.sstatic.net/TTntF.png

This is the SQL Server stored procedure:

ALTER PROCEDURE [dbo].[InsertAngajat] 
    @Nume VARCHAR(50), 
    @Prenume VARCHAR(50),
    @Departament VARCHAR(50),
    @cnp CHAR(13),
    @DataNasterii DATE,
    @Telefon VARCHAR(12) = "NONE",
    @DataAngajare DATE,
    @Salariu INT,
    @Sex CHAR(1) = 'F',
    @Judet VARCHAR(50),
    @Localitate VARCHAR(50),
    @Strada VARCHAR(50),
    @Numar INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DepID INT = (SELECT D.DepartamentID 
                          FROM Departamente D 
                          WHERE D.[Nume Departament] = @Departament);

    INSERT INTO [dbo].Angajat (Nume, Prenume, DepartamentID, CNP, DataNasterii,
                               Telefon, DataAngajarii, Salariu, Sex, Localitate,
                               [Sector/Judet], Strada, nr)
    VALUES (@Nume, @Prenume, @DepID, @cnp, @DataNasterii,
            @Telefon, @DataAngajare, @Salariu, @Sex, @Localitate,
            @Judet, @Strada, @Numar)        
END

I made sure that the text boxes were having data. I have no idea what else to try.

I'm expecting that as long as the values in the text boxes aren't NULL to be able to Insert all their data easily.

Upvotes: 0

Views: 91

Answers (2)

S.Maneesh
S.Maneesh

Reputation: 60

Run your sql profiler and trace the call. So that, you will be sure about your all input parameters. And then execute your procedure call manually. You will be able to find the exact issue and the line in the procedure. Try one with fixed value of @DepID int=1. If it's running successfully. Then you have to optimized this line of code as below: Declare @DepID int = (Select isnull(D.DepartamentID,0) From Departamente D Where D.[Nume Departament] = @Departament);

It seems your Departamente table don't have data for this department.

Upvotes: 0

ProgrammingLlama
ProgrammingLlama

Reputation: 38767

It seems to me that your issue probably lies with this line:

Declare @DepID int = (Select D.DepartamentID From Departamente D Where D.[Nume Departament] = @Departament);

What happens if there are no results for this query? I'm a bit rusty, but I imagine @DepId will be NULL. Then when you try and insert it into your table, you're inserting a null.

Solution: Either in your procedure, or before calling the insert, check if the department exists.

Upvotes: 5

Related Questions