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.