problem with getting record by current month-CodePudding

Im trying to get a specific record here but it always returns blank

     Try
        cmd = con.CreateCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT SUM(a.qty_sold) FROM sales AS a INNER JOIN transaction_history AS c ON c.Id = a.transaction_id WHERE MONTH(c.transaction.date) = MONTH(CURRENT_DATE()) AND YEAR(c.transaction.date) = YEAR(CURRENT_DATE())"
        cmd.ExecuteNonQuery()

        Dim dt As New DataTable()
        Dim da As New SqlDataAdapter(cmd)

        da.Fill(dt)

        Dim dr As SqlClient.SqlDataReader
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        While dr.Read
            current_month_products_sold = dr.GetInt32(3).ToString()

        End While


    Catch ex As Exception

    End Try
    MessageBox.Show(current_month_products_sold)

    total_product_sold.Text = current_month_products_sold

I don't know if the query is the problem or the way I pull my data

CodePudding user response：

There appears to be some code left over from something else. As you only want one value, you can use ExecuteScalar, like this:

Dim sql = "
    SELECT SUM(a.qty_sold)
    FROM sales AS a
    INNER JOIN transaction_history AS c
    ON c.Id = a.transaction_id
    WHERE MONTH(c.transaction.date) = MONTH(CURRENT_DATE())
    AND YEAR(c.transaction.date) = YEAR(CURRENT_DATE())"

Dim currentMonthProductsSold = 0

Using conn As New SqlConnection("yourConnectionString"),
       cmd As New SqlCommand(sql, conn)

    conn.Open()
    currentMonthProductsSold = CInt(cmd.ExecuteScalar())

End Using

MessageBox.Show(currentMonthProductsSold)

CodePudding user response：

Do not write empty Catch blocks. They only swalow errors.

It is a good idea to keep your database code and your user interface code separate. I created a Function to do this.

Commands and connections need to have their Dispose methods called because they use unmanaged resources which are released in the Dispose method. Using blocks handle closing and disposing for us even if there is an error.

Declare connections in the method where they are used so they can be disposed. You can pass the connection string to the constructor of the connection. Likewise, pass the CommandText string and the connection to the constructor of the command.

Since we are only retrieving a single value, we can use ExecuteScalar. This returns the fist column of the first row of the result set, always an Object. Thus, the CInt. I am assuming that the qty_sold field is an Integer field.

Test your Select statement in SSMS before trying in code. (SQL Server Management System)

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim current_month_products_sold = ""
    Try
        current_month_products_sold = GetSalesSum().ToString
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    MessageBox.Show(current_month_products_sold)
    total_product_sold.Text = current_month_products_sold
End Sub

Private Function GetSalesSum() As Integer
    Dim RetVal As Integer
    Dim sql = "SELECT SUM(a.qty_sold) FROM sales AS a INNER JOIN transaction_history AS c ON c.Id = a.transaction_id WHERE MONTH(c.transaction.date) = MONTH(CURRENT_DATE()) AND YEAR(c.transaction.date) = YEAR(CURRENT_DATE())"
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand(sql, cn)
        RetVal = CDec(cmd.ExecuteScalar())
    End Using
    Return RetVal
End Function