Results 1 to 8 of 8

Thread: Try to display amount total as per quarterly selected and date between selected

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    94

    Try to display amount total as per quarterly selected and date between selected

    Dear frens,
    i have tried using this code to display the amount total as per quarterly selected and date between selected the total is not display in the label. Kindly see what is wrong with this code.

    Code:
     Public Sub totalcollection()
    
    cmd.CommandText = " Select ((select sum(Tuition_Amount) from Tuition)+ " _
                                    & "  select sum(Food_Amount) from Food)+ " _
                                    & "  select sum(Bus_Amount) from Bus)+ " _
                                    & "  select sum(Misc_Amount) from Misc)) as Total " _
                    & " FROM Total " _
                                & " WHERE (Date_received >= '" & DTPcolfrom.Text & "' AND " _
                                & " Date_received <= '" & DTPcolto.Text & "' " _
                                & " AND Quarterly = '" & cmbcolquarter.Text & "') "
    
            con.Open()
    
            Dim r As SqlDataReader = cmd.ExecuteReader()
    
            While r.Read
                lbltotal.Text = String.Format("{0:c}", r("Total"))
    
            End While
    
            r.Close()
            cmd.Connection.Close()
        End Sub

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Try to display amount total as per quarterly selected and date between selected

    If you aren't getting an exception, then the most likely problem is that the datareader is returning no rows, in which case the while loop won't run even once. You can check that out pretty easily by putting a breakpoint on the line where you set the label. If the breakpoint isn't hit, then that is likely to be the problem. There may be a different problem if this method is called from the Load event of a form, but you can check that even more easily by putting a breakpoint on the con.Open() line. If that breakpoint is reached, then you can step forwards to see whether or not the While loop executes. Also, when you reach the While r.Read line, you can look at r.HasRows. If that's false, then the query isn't returning any rows.

    Most likely, the query isn't returning any rows.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    94

    Re: Try to display amount total as per quarterly selected and date between selected

    Dear frens,
    I tried with this code the exception throw " Must declare the scalar variable " A System.Data.SqlClient.SqlException was thrown " Must declare the scalar variable " @startDate". I get stuck here, can you help me in this portion.

    Code:
    Public Sub quarterlytotal()
    
            If Len(Trim(cmbcolquarter.SelectedItem)) = 0 Then
                MessageBox.Show("Please select Quarter", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                cmbcolquarter.Focus()
                Exit Sub
    
            Else
    
                con.Open()
    
                cmd.CommandText = "SELECT (((SELECT SUM(Tuition_Amount) FROM Tuition ) " _
                                                    & " +  (SELECT SUM(Food_Amount) FROM Food ) " _
                                                    & " +  (SELECT SUM(Bus_Amount) FROM Bus ) " _
                                                    & " +  (SELECT SUM(Misc_Amount) FROM Misc )) " _
                                                    & " WHERE Date_received BETWEEN @startDate AND @endDate ) as Total "
    
                'Dim r As SqlDataReader = cmd.ExecuteReader()
    
                Dim r As SqlDataReader = cmd.ExecuteScalar
    
                While r.Read
                    lbltotal.Text = String.Format("{0:c}", r("Total"))
    
                End While
    
                r.Close()
    
                cmd.Connection.Close()
            End If
        End Sub

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Try to display amount total as per quarterly selected and date between selected

    Ok, so you have two parameters in there @StartDate and @endDate. You also have to supply values for the parameters:

    cmd.Parameters.AddWithValue(@StartDate, yourValueForStartDateHere)
    cmd.Parameters.AddWithValue(@endDate, yourValueForEndDateHere)
    My usual boring signature: Nothing

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Try to display amount total as per quarterly selected and date between selected

    I notice that the original code used DTPcolfrom.Text (which is a String), and that could be problematic.

    If the Date_received field has a data type that is Date based (as it should be), then the value you should be using is DTPcolfrom.Value (which is a DateTime), eg:
    Code:
    cmd.Parameters.AddWithValue(@StartDate, DTPcolfrom.Value)
    cmd.Parameters.AddWithValue(@endDate, DTPcolto.Value)
    If the Date_received field has a data type that is not Date based then things get much more complicated, depending on what data type it is and exactly what it stores (eg: is it a String that contains values formatted as "d/m/yyyy" or are they "dd/mm/yyyy" or something else?, and are they always using the same format?).

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    94

    Re: Try to display amount total as per quarterly selected and date between selected

    Quote Originally Posted by si_the_geek View Post
    I notice that the original code used DTPcolfrom.Text (which is a String), and that could be problematic.

    If the Date_received field has a data type that is Date based (as it should be), then the value you should be using is DTPcolfrom.Value (which is a DateTime), eg:
    Code:
    cmd.Parameters.AddWithValue(@StartDate, DTPcolfrom.Value)
    cmd.Parameters.AddWithValue(@endDate, DTPcolto.Value)
    If the Date_received field has a data type that is not Date based then things get much more complicated, depending on what data type it is and exactly what it stores (eg: is it a String that contains values formatted as "d/m/yyyy" or are they "dd/mm/yyyy" or something else?, and are they always using the same format?).
    I have tried again with your suggestion here is the code below and the exception was thrown in correct syntax near keyword 'select.'
    Incorrect syntax near ')'.
    Incorrect syntax near ')'.
    Incorrect syntax near ')'."

    Code:
    Public Sub quarterlytotal()
    
            Dim startD As Date = DTPcolfrom.Value.Date
            Dim endD As Date = DTPcolto.Value.Date
    
            If Len(Trim(cmbcolquarter.SelectedItem)) = 0 Then
                MessageBox.Show("Please select Quarter", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                cmbcolquarter.Focus()
                Exit Sub
    
            Else
    
                con.Open()
    
                cmd.CommandText = " Select ((select sum(Tuition_Amoun) from Tuition) + " _
                                    & "  select sum(Food_Amount) from Food) + " _
                                    & "  select sum(Bus_Amount) from Bus) + " _
                                    & "  select sum(Misc_Amount) from Misc)) as Total " _
                    & " FROM Total " _
                                & " WHERE (Date_received >= '" & startD & "' AND " _
                                & " Date_received <= '" & endD & "' " _
                                & " AND Quarterly = '" & cmbcolquarter.Text & "') "
    
                With cmd.Parameters
                    .AddWithValue("@startDate", startD)
                    .AddWithValue("@endDate", endD)
                End With
    
                 Dim r As SqlDataReader = cmd.ExecuteReader
    
    ' Shows the records in label
    
                While r.Read
                    lbltotal.Text = String.Format("{0:c}", r("Total"))
    
                End While
    
                r.Close()
    
                cmd.Connection.Close()
            End If
        End Sub
    Last edited by kiki2012; Oct 1st, 2017 at 06:41 AM.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Try to display amount total as per quarterly selected and date between selected

    The exception is because you have changed the SQL statement, and you shouldn't have... go back to the SQL statement you had in post #3.

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Try to display amount total as per quarterly selected and date between selected

    Hi,

    I think you should look at a Pivot query.

    I don't have SQL-Server here but can supply a sample for an Access Database
    here the SQL statement for Access
    Code:
    TRANSFORM Sum(CCur([Order Details].[Unitprice]*[Quantity]*(1-[Discount])/100)*100) AS Artikelanzahl
    SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS [Order Year]
    FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
    WHERE (((Orders.OrderDate) Between #1/1/1995# And #12/31/1995#))
    GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
    PIVOT "Quarter " & DatePart("q",[OrderDate],1,0);
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width