Insert to foreign SQL keys

Question

I am learning a programming language and I set out to develop a budget program. My first obstacle is to add the values of the primary keys in the foreign keys of the respective tables. This code below works well for this, but after copying the PK from the Client table to FK from the Budget table, the next data is always added to the following rows of the table and not to the current row as it should be. Can someone help me? Follow an excerpt from my code:

connect.Open();

        OleDbCommand cmd1= new OleDbCommand("INSERT into Cliente(nome, endereco)"+"Values (@nome, @endereco)", connect); // nome e endereço
        OleDbCommand cmd2 = new OleDbCommand("INSERT into Produto(nome, preco)" + "Values (@nomeProd, @precoProd)", connect); //produto, preço
        OleDbCommand cmd3 = new OleDbCommand("INSERT into Orcamento(id_cliente) SELECT MAX(id) FROM Cliente", connect);
        //Como eu faria para adicionar a informação "data" que está numa string no objeto acima, como as demais (cmd1 e cmd2) tendo em vista que existe o comando SELECT?

        if(connect.State == ConnectionState.Open)
        {
            cmd1.Parameters.Add("@nome",OleDbType.Char,20).Value = nome;
            cmd1.Parameters.Add("@endereco",OleDbType.Char,20).Value = endereco;
            cmd2.Parameters.Add("@nome", OleDbType.Char, 20).Value = nomeProd;
            cmd2.Parameters.Add("@preco", OleDbType.Char, 20).Value = precoProd;
            cmd3.Parameters.Add("@id_cliente", OleDbType.Char, 20).Value = "";
            cmd3.Parameters.Add("@data", OleDbType.Char, 20).Value = data;
            try 
            {           
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();
                cmd3.ExecuteNonQuery();

Answer 1

See the possibility to create a proc, I see is inserted data in the 3 tables of a single time, so you could do as follows...

Create proc with the necessary parameters

create Proc  Cliente_Orcamento  
(
  @nome nvarchar(50),
  @endereco nvarchar(50),
  @nomeProd  nvarchar(50),
  @precoProd numeric(18,2), 
  @data datetime,
  @IdCliente_Out int output
)
as
begin

  Set Xact_Abort on
  Set Nocount on
  Begin Tran

  begin
    INSERT into Cliente(nome, endereco) Values (@nome, @endereco)
    Set @IdCliente_Out = @@IDENTITY -- aqui vc pegar o id do cliente do
    -- ultimo insert.. linha executada acima    

    INSERT into Produto(nome, preco) Values (@nomeProd, @precoProd)
    INSERT into Orcamento(id_cliente) Values (@IdCliente_Out)
  end
  Commit
end

Call it that

connect.Open();

    OleDbCommand cmd1= new OleDbCommand("Cliente_Orcamento", connect); 

    if(connect.State == ConnectionState.Open)
    {
        cmd1.Parameters.Add("@nome",OleDbType.Char,20).Value = nome;
        cmd1.Parameters.Add("@endereco",OleDbType.Char,20).Value = endereco;
        cmd1.Parameters.Add("@nomeProd", OleDbType.Char, 20).Value = nomeProd;
        cmd1.Parameters.Add("@precoProd", OleDbType.Char, 20).Value = precoProd;
        cmd1.Parameters.Add("@data", OleDbType.Char, 20).Value = data;
        try 
        {           
            cmd1.ExecuteNonQuery();