Update in database in Visual Studio

1

Could you tell me where the error is in this little bit of code I am creating for a budget program with a database with 4 tables (only the last cmd7 object is not accepted in the build):

OleDbCommand cmd1 = new OleDbCommand("INSERT into Cliente(nome, endereco)"+"Values (@nome, @endereco)", connect); // nome e endereço do cliente
            OleDbCommand cmd2 = new OleDbCommand("INSERT INTO Orcamento(id_cliente) SELECT MAX(id) FROM Cliente", connect);//ID do cliente em Orçamento
            OleDbCommand cmd3 = new OleDbCommand("UPDATE Orcamento SET data = @data", connect);//data do Orçamento
            OleDbCommand cmd4 = new OleDbCommand("INSERT INTO Item(id_orcam) SELECT MAX(id) FROM Orcamento", connect); //ID do orçamento em Item
            OleDbCommand cmd5 = new OleDbCommand("UPDATE Item SET quantidade = @quantidade", connect);//quantidade
            OleDbCommand cmd6 = new OleDbCommand("INSERT INTO Produto(nome,preco)"+"Values (@nomeProd,@precoProd)", connect); //Nome e preço do produto
            OleDbCommand cmd7 = new OleDbCommand("UPDATE Item SET id_produto = (SELECT MAX(id) FROM Produto)", connect);
            //ESTE ÚLTIMO cmd7 NÃO FUNCIONA

            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("@id_cliente", OleDbType.Char, 20);
                cmd3.Parameters.Add("@data", OleDbType.Char, 20).Value = data;
                cmd4.Parameters.Add("@id_orcam", OleDbType.Char, 20);
                cmd5.Parameters.Add("@quantidade", OleDbType.Char,20).Value = quantidade;
                cmd6.Parameters.Add("@nome", OleDbType.Char,20).Value = nomeProd;
                cmd6.Parameters.Add("@preco", OleDbType.Char, 20).Value = precoProd;
                cmd7.Parameters.Add("@id_produto", OleDbType.Char, 20);

                try
                {
                    cmd1.ExecuteNonQuery();
                    cmd2.ExecuteNonQuery();
                    cmd3.ExecuteNonQuery();
                    cmd4.ExecuteNonQuery();
                    cmd5.ExecuteNonQuery();
                    cmd6.ExecuteNonQuery();
                    cmd7.ExecuteNonQuery();

I've tried several syntax combinations but it seems like it does not update the product_id of the Item table because of the last insert made in the Products table. Someone can see a solution. Note: product_id in the Item table is a foreign key being inherited from the primary of the Product table. Note: the database was created in Access.

Thank you.

    
asked by anonymous 19.11.2015 / 01:25

1 answer

2

In cmd7, you do not use any parameters.

 OleDbCommand cmd7 = new OleDbCommand("UPDATE Item SET id_produto = (SELECT MAX(id) FROM Produto)", connect);

Plus you add value to the parameter id_producto:

cmd7.Parameters.Add("@id_produto", OleDbType.Char, 20);

The problem must be at this point.

    
19.11.2015 / 11:23