I have three tables as seen attached in the picture. I want to join these three tables and I insert all data from a form of the app in the Produse, Clienti and Comenzi tables.

Everything is OK except the two columns in the Comenzi table (IDClient and IDProdus). They are both of type int and foreign keys for the other two tables, and I can't make them Identity to auto increase id.

So the program crashes when I press the submit button in the app form.

Cannot insert the value NULL into column 'IDClient', table 'dbo.Tmp_Comenzi'; column does not allow nulls. INSERT fails.

If I allow nulls, it throws no error but the column will be null. There is no value going inside these two columns from the app form so I have to find a way to provide a value for them or is there another way?

I can't fill them by hand. I am not very good at SQL joining and table relationships, maybe I did something wrong.

How can I insert my data successfully?

Well, i created 3 stored procedures

CREATE PROCEDURE [dbo].[InsertProducts]

@DenProd nvarchar(50),
@ProdQuant nvarchar(50),
@ProdSize nvarchar(50),
@ProdComm nvarchar(50)

AS

BEGIN
    INSERT INTO Produse (Denumire, Cantitate, Dimensiuni, Comentarii) VALUES (@DenProd, @ProdQuant, @ProdSize, @ProdComm)
END

CREATE PROCEDURE [dbo].[InsertClients]

@NumeClient nvarchar(50)

AS

BEGIN
    INSERT INTO Clienti (NumeClient) VALUES (@NumeClient)
END

CREATE PROCEDURE [dbo].[InsertOrders]

-- @ClientID int,
-- @ProductID int,
@StartDate date,
@FinishDate date,
@Billed nvarchar(50),
@Delivered nvarchar(50)

AS

BEGIN
    INSERT INTO Comenzi (IDClient) SELECT IDClient FROM Clienti;
    INSERT INTO Comenzi (IDProdus) SELECT IDProdus FROM Produse;
    INSERT INTO Comenzi (DataInceput, DataSfarsit, Facturata, Livrata) VALUES (@StartDate, @FinishDate, @Billed, @Delivered)
END

The first two procedures work perfectly. The third is the problem.

The insertion code:

private void InsertOrderButton_Click(object sender, EventArgs e)
    {

        SqlCommand cmd =  new SqlCommand("InsertClients", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@NumeClient", textBoxClientNou.Text);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        textBoxClientNou.Text = "";

        foreach (ListViewItem item in ListaProduse.Items)
        {
            SqlCommand cmd2 = new SqlCommand("InsertProducts", con);
            cmd2.CommandType = CommandType.StoredProcedure;
            cmd2.Parameters.AddWithValue("@DenProd", item.Text);
            cmd2.Parameters.AddWithValue("@ProdQuant", item.SubItems[1].Text);
            cmd2.Parameters.AddWithValue("@ProdSize", item.SubItems[1].Text);
            cmd2.Parameters.AddWithValue("@ProdComm", item.SubItems[1].Text);
            con.Open();
            cmd2.ExecuteNonQuery();
            con.Close();
        }

        SqlCommand cmd3 = new SqlCommand("InsertOrders", con);

        cmd3.CommandType = CommandType.StoredProcedure;
        cmd3.Parameters.AddWithValue("@StartDate", dateTimePicker1.Text);
        cmd3.Parameters.AddWithValue("@FinishDate", dateTimePicker2.Text);
        cmd3.Parameters.AddWithValue("@Billed", factstatus);
        cmd3.Parameters.AddWithValue("@Delivered", livstatus);
        con.Open();
        cmd3.ExecuteNonQuery();
        con.Close();

        comboBoxClient.Text = "";
        dateTimePicker1.Text = "";
        dateTimePicker2.Text = "";
        textBoxClientNou.Text = "";
        ListaProduse.Items.Clear();
      
        MessageBox.Show("Succesful insert");
        
    }

Now i get these errors:

Cannot insert the value NULL into column 'IDProdus', table 'DatabaseName.dbo.Comenzi'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'IDClient', table 'DatabaseName.dbo.Comenzi'; column does not allow nulls. INSERT fails.

In the InsertOrders procedure i tried to insert the values from the other tables but nothing. I tried all kinds of queries.

CodePudding user response：

First you insert Produse and Clienti tables and get those ID and send these values when inserting Comenzi table. How you get those I am sharing sample store procedure

CREATE PROCEDURE Usp_Insert_Produse
    @col1            VARCHAR(50),
    @new_identity    INT    OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO Produse(col1)
    VALUES (@col1)

    SELECT @new_identity = SCOPE_IDENTITY()

    SELECT @new_identity AS id

    RETURN
END

CodePudding user response：

Your first two stored procedures should have output parameters to return the new IDClient and IDProdus. The application code can then receive the values and submit them as parameters to the third stored procedure.

Here's the first step as an example.

CREATE PROCEDURE [dbo].[InsertClients]
@NumeClient nvarchar(50),
@IDClient INT OUTUT
AS
BEGIN
    INSERT INTO Clienti (NumeClient) VALUES (@NumeClient);
    SELECT @IDClient = SCOPE_IDENTITY();
END

Application

SqlCommand cmd =  new SqlCommand("InsertClients", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@NumeClient", textBoxClientNou.Text);
var IDClientParameter = cmd.Parameters.Add("@IDClient", SqlDbType.Int);
IDClientParameter.Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
var IDClient = (int) IDClientParameter.Value;
con.Close();