Click here to Skip to main content
15,920,217 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
MysqlConn = New MySqlConnection
        MysqlConn.ConnectionString = "server=localhost;userid=root;database=stock_sales_system"
        Dim SDA As New MySqlDataAdapter
        Dim dbDataSet As New DataTable
        Dim bSource As New BindingSource

            Dim query As String
            query = "SELECT SUM(Quantity) AS Sold
                     FROM invoice_products AS IP, invoice AS I
                     WHERE ProductBarcode LIKE @ProductBarcode AND IP.InvoiceNumber = I.InvoiceNumber AND I.InvoiceDate >= @FromDate AND I.InvoiceDate <= @ToDate;"

            Command.Parameters.Add("@ProductBarcode", MySqlDbType.VarChar).Value = frmManageProducts.ProductTableAdapter.GetBarcode1(cbxProductName.Text)
            Command.Parameters.Add("@FromDate", MySqlDbType.Date).Value = dtpFrom.Value
            Command.Parameters.Add("@ToDate", MySqlDbType.Date).Value = dtpTo.Value

            Command = New MySqlCommand(query, MysqlConn)
            SDA.SelectCommand = Command
            bSource.DataSource = dbDataSet
            DataGridView1.DataSource = bSource
        Catch ex As Exception
        End Try

What I have tried:

i cannot find what the error is.. please help
Updated 3-May-20 1:20am
Richard MacCutchan 3-May-20 5:54am    
But you expect us to guess what the error is and where it occurs?
Andreas322 3-May-20 6:05am    
"Fatal error encountered during command execution" is the exception message.. it occurs at 'SDA.Fill(dbDataSet)'
Richard MacCutchan 3-May-20 6:15am    
There is something missing or incorrect in your SELECT clause. Quite possibly the fact that you are trying to fill a Datatable with a single value, i.e. the SUM of the SELECT clause.
Andreas322 3-May-20 6:27am    
i tried to SELECT * but nothing changed.. i have copied this query from MySqlWorkbench where all are working well
Richard MacCutchan 3-May-20 6:30am    
It is no good just randomly changing things and hoping it will work as if by magic. Use your debugger to check all the values in your parameters as well as the correct spelling of the column names. Are you sure that the statements Command.Parameters.Add("@ToDate", MySqlDbType.Date).Value = dtpTo.Value are correctly building the parameter fields?

1 solution

query = "SELECT SUM(IP.Quantity) AS Sold FROM invoice_products AS IP INNER JOIN invoice AS I ON IP.InvoiceNumber = I.InvoiceNumber WHERE ProductBarcode LIKE @ProductBarcode AND I.InvoiceDate >= @FromDate AND I.InvoiceDate <= @ToDate"
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900