Stored Procedure to insert into related tables

Question

I try to write a SP to insert values into the related tables. I have the error message:

"Cannot insert the value NULL into column 'marka_id', table 'env.dbo.Marka'; column does not allow nulls. INSERT fails.

Cannot insert the value NULL into column 'model_id', table 'env.dbo.Model'; column does not allow nulls. INSERT fails.

Cannot insert the value NULL into column 'f_id', table 'env.dbo.Firma'; column does not allow nulls. INSERT fails.

Cannot insert the value NULL into column 'sira_no', table 'env.dbo.Ana'; column does not allow nulls. INSERT fails.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated."

Here's the stored procedure:

use env
go
create procedure [SP$Ekle](
@marka_adi nvarchar(30) = NULL,
@model_adi nvarchar(30) = NULL,
@f_adi nvarchar(50) = NULL,
@adres nvarchar(20) = NULL,
@tel nvarchar(20) = NULL,
@seri_no nvarchar(30) = NULL,
@kullanici_adi nvarchar(50) = NULL,
@link nvarchar(50) = NULL,
@aciklama nvarchar(50) = NULL,
@sira_no int output
)
 as
 begin
 set nocount on

 DECLARE 
 @marka_id int,
 @model_id int,
 @f_id int
 begin

 INSERT INTO Marka(marka_adi) VALUES(@marka_adi);
 SET @marka_id = (SELECT marka_id FROM Marka WHERE marka_adi = @marka_adi);
 
 INSERT INTO Model(marka_id,model_adi) VALUES(@marka_id,@model_adi);
 SET @model_id = (SELECT @model_id FROM Model WHERE model_adi = @model_adi); 

 INSERT INTO Firma(f_adi,adres,tel) VALUES (@f_adi,@adres,@tel);
 SET @f_id = (SELECT f_id FROM Firma WHERE f_adi= @f_adi AND adres = @adres AND tel = @tel);
 
 INSERT INTO Ana(seri_no,kullanici_adi,link,aciklama,marka_id,model_id,f_id) VALUES (@seri_no,@kullanici_adi,@link,@aciklama,@marka_id,@model_id,@f_id);

 SELECT @sira_no = SCOPE_IDENTITY();
 end

 set nocount off
 end
 go 

Calling from C#:

    cmd.CommandText = "SP$Ekle";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@marka_adi", TextBox1.Text));
        cmd.Parameters.Add(new SqlParameter("@model_adi", TextBox2.Text));
        cmd.Parameters.Add(new SqlParameter("@f_adi", TextBox3.Text));
        cmd.Parameters.Add(new SqlParameter("@adres", TextBox4.Text));
        cmd.Parameters.Add(new SqlParameter("@tel", TextBox5.Text));
        cmd.Parameters.Add(new SqlParameter("@seri_no", TextBox6.Text));
        cmd.Parameters.Add(new SqlParameter("@kullanici_adi", TextBox7.Text));
        cmd.Parameters.Add(new SqlParameter("@link", TextBox8.Text));
        cmd.Parameters.Add(new SqlParameter("@aciklama", TextBox9.Text));
        cmd.Parameters.Add(new SqlParameter("@sira_no", SqlDbType.Int)).Direction = ParameterDirection.Output;

    

Answer 1

The error is telling you that when you call insert you are not passing in a value for a non nullable column. Specifically your ID columns which cannot be nullable if they are set to be your primary key.

it is hard to tell without see your tables, but i think you need to set your columns to Identity so that the ID's for you rows are automatically created. Otherwise, if you don't specify an ID on the insert you will get the errors that you are receiving.

Take a look at this stack overflow question: Setting Identity to on or off in SQL server

here is good stackoverflow question for turning on seeding. Its for sql 2005 but its the same process:

Adding auto incrementing primary key to an existing table in SQL SERVER 2005

EDIT TO ANSWER YOUR QUESTION:

you are trying to assign a query to a variable you want to do something like this:

SELECT TOP 1  @marka_id =  marka_id FROM Marka WHERE marka_adi = @marka_adi

you will have to do this for all of the susbequent statements as well.