-
Sep 20th, 2017, 01:53 PM
#1
Thread Starter
Lively Member
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
-
Sep 20th, 2017, 02:06 PM
#2
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
-
Sep 29th, 2017, 02:01 PM
#3
Thread Starter
Lively Member
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
-
Sep 29th, 2017, 04:00 PM
#4
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
-
Sep 29th, 2017, 05:40 PM
#5
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?).
-
Oct 1st, 2017, 06:37 AM
#6
Thread Starter
Lively Member
Re: Try to display amount total as per quarterly selected and date between selected
Originally Posted by si_the_geek
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.
-
Oct 1st, 2017, 06:54 AM
#7
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.
-
Oct 1st, 2017, 08:55 AM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|