Help with Dataview

  • Thread starter Thread starter John G
  • Start date Start date
J

John G

Hi,

I've hit a brick wall, trying to get my web application to do what I want and hope someone can help.

I have an SQL database which records financials for our company. A subset of the database looks as follows:

ID ---- TradingDate ---- Loaded ---- AccountCode ---- ItemCode ---- Value

1 --- 15/09/2004 --- 29/09/2004 --- 100710 ------ Sports Bar --- 500.50
2 --- 15/09/2004 --- 29/09/2004 --- 100720 ------ Beach Bar ---- 300.60
3 --- 15/09/2004 --- 29/09/2004 --- 100750 ------ Liqour Barn -- 1050.00
..
..
10 --- 15/09/2004 --- 29/09/2004 --- 100800 ------ Gaming ---- 3000.00
11 --- 13/09/2004 --- 30/09/2004 --- 100710 ------ Sports Bar ---- 600.30
12 --- 13/09/2004 --- 30/09/2004 --- 100720 ------ Beach Bar ---- 400.00
..
..

What I am trying to do is extract a weeks worth of financials (value field in database) and populate a daily page for each of our departments (ItemCodes). Eg. Someone will pick Week Ending September 20 and Totals for Monday, Tuesday, Wednesday, Thursday.... will be extracted and displayed on a web page. My initial thought was to create individual dataviews for each department and then filter based on date (see code below) but there must be an easier way??

Dim SportsBarDV As DataView
SportsBarDV = New DataView(DS.Tables("tblCincOutletData"))
SportsBarDV.RowFilter = "ItemCode='Sports Bar'"
SportsBarDV.RowFilter = "TradingDate >= '" & DateAdd(DateInterval.Day, -7, WDate) & "' AND TradingDate < '" & DateAdd(DateInterval.Day, -6, WDate) & "'"
DailySportsRev.Text = CDec(((SportsBarDV(0)("Value"))) / 1.1).ToString("c0")



___
Newsgroups brought to you courtesy of www.dotnetjohn.com
 
Hi John,

Inline.

John G said:
Hi,

I've hit a brick wall, trying to get my web application to do what I want
and hope someone can help.

I have an SQL database which records financials for our company. A subset
of the database looks as follows:

ID ---- TradingDate ---- Loaded ---- AccountCode ---- ItemCode ----
Value

1 --- 15/09/2004 --- 29/09/2004 --- 100710 ------ Sports Bar ---
500.50
2 --- 15/09/2004 --- 29/09/2004 --- 100720 ------ Beach Bar ----
300.60
3 --- 15/09/2004 --- 29/09/2004 --- 100750 ------ Liqour Barn --
1050.00
.
.
10 --- 15/09/2004 --- 29/09/2004 --- 100800 ------ Gaming ----
3000.00
11 --- 13/09/2004 --- 30/09/2004 --- 100710 ------ Sports Bar ----
600.30
12 --- 13/09/2004 --- 30/09/2004 --- 100720 ------ Beach Bar ----
400.00
.
.

What I am trying to do is extract a weeks worth of financials (value field
in database) and populate a daily page for each of our departments
(ItemCodes). Eg. Someone will pick Week Ending September 20 and Totals for
Monday, Tuesday, Wednesday, Thursday.... will be extracted and displayed
on a web page. My initial thought was to create individual dataviews for
each department and then filter based on date (see code below) but there
must be an easier way??

Dim SportsBarDV As DataView
SportsBarDV = New DataView(DS.Tables("tblCincOutletData"))
SportsBarDV.RowFilter = "ItemCode='Sports Bar'"
SportsBarDV.RowFilter = "TradingDate >= '" &
DateAdd(DateInterval.Day, -7, WDate) & "' AND TradingDate < '" &
DateAdd(DateInterval.Day, -6, WDate) & "'"
Click to expand...

You have to combine filter with AND if you want to do a proper filtering
otherwise the second RowFilter will override the first.
DailySportsRev.Text = CDec(((SportsBarDV(0)("Value"))) /
1.1).ToString("c0")
Click to expand...

Try using DataTable.Compute method instead.
 
Back
Top