Insert for foreign keys SQL

Question

Insert for foreign keys SQL

Navigation

#1 by (1 votes)

1

I'm learning a programming language and I set out to develop a budgeting program. My first hurdle is being 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 Customer table to FK from the Budget table, the next data always adds to the following table rows and not to the current row as it should be. can anybody help me? Here's a snippet of 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();

sql c# chave-estrangeira

asked by anonymous 15.11.2015 / 18:38

1 answer

problem with dynamic proxies ASP .NET MVC Difficulty getting object inside a CDI Extension

score 1 · Answer 1

Create the proc with the required 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

Flame like this

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();