Stock query, return null access

Asked

Viewed 168 times

0

I have the following structure:

Estrutura

My code:

Private Sub CarregaEstoque()
    Using con As OleDbConnection = GetConnection()
        Try
            con.Open()
            Dim sql As String = "SELECT IdProduto, Descricao, (SELECT SUM(Entradas.QtdEntrada) FROM Entradas WHERE Entradas.IdProduto = Produtos.IdProduto) - " & _
                                "(SELECT SUM(Saidas.QtdSaida) FROM Saidas WHERE Saidas.IdProduto = Produtos.IdProduto) AS Diferenca FROM Produtos"
            Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim dt As DataTable = New DataTable
            da.Fill(dt)
            DataGridView1.DataSource = dt
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Using
End Sub

How to return the quantity of product 02, and had no outputs referred to this?

  • Have you tried using Coalesce in your sql? in the output select, if you do not return any value the return becomes 0, and the calculation will work. I don’t know the syntax of your database, but it’s usually something like that Coalesce(suaselect, 0).

  • Tentei usar "SELECT IdProduto, Descricao, (SELECT SUM(Entradas.QtdEntrada) FROM Entradas WHERE Entradas.IdProduto = Produtos.IdProduto) - " & _ "(SELECT COALESCENSE (SUM(Saidas.QtdSaida, 0)) FROM Saidas WHERE Saidas.IdProduto = Produtos.IdProduto) AS Diferenca FROM Produtos" but returned the error: Incorrect number of arguments used with the function in the query expression

  • Try changing your select: (SELECT COALESCENSE(SUM(Output.Qtdsaida), 0)), see if it works.

No answers

Browser other questions tagged

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