Update in Visual Studio database

Asked

Viewed 1,137 times

1

could tell me where is the error in this small piece of code that I am creating for a budget program with a database with 4 tables (only the last cmd7 object is not accepted in the compilation):

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 have tried several syntax combinations but it seems that it does not update the id_product of the Item table because of the last input made in the Products table. Someone can see a solution. Note: id_product in the Item table is a foreign key being inherited from the primary of the Product table. Obs2.: the bank was created in Access.

Thank you.

c# sql-update

  • Can you put in your question what error triggered by the execution of cmd7?

  • Now I have no access to the code, but it looks like something like this: The operation should use an upgradeable query.

1 answer

2

In cmd7, vc does not use any parameter.

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

More vc adds value to the product id_parameter:

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

The problem must be at this point.

  • I thought cmd7.Parameters.Add was necessary to inform the data type where it says Oledbtype.Char. I will try to remove this line as soon as possible. Thanks for the help.

  • I had imagined that was this too, but the top queries are the same and they work.

  • But actually in cmd7 SQL has nothing as parameter using @in the middle, so this may be hacking the command. And @Vanderlei does not need to define the type of data because SQL will take care of it, but it is good to check, because the product id_has to have the same type of data from the product table id field. check there and if it works be sure to accept the answer

  • I took the cmd7.Parameters.Add and executed. God the same mistake. I changed the UPDATE by INSERT, the same initial problem happens: the id_product goes to the next line.

  • @Vanderlei, the product id_field of the item table and the product table id field are of the same data type?

  • Yes. Both numerical. But I don’t think that’s the problem. cmd2 and cmd3 objects do the same function as cmd7 (take the Primary key and transfer it to the Foreign Keys). If there is an easier method to fill these fields with the Primary Keys help me already.

  • I was able to solve using "SELECT MAX(id) FROM Table" and capturing the value by "cmd.Executscalar()" for variables and inserting them as parameters in cmd objects. Thanks for the contribution of friends.

  • @Vanderlei, we are there my dear, pity we have not found the solution here in the question, but it is a pleasure to help the community.

Show 3 more comments

Browser other questions tagged c# sql-update

You are not signed in. Login or sign up in order to post.