Using COMPUTE(SUM(.....)......)

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Hi All,

Within my database I have a TimeSpent field, which I want to do the following:

(I've managed to do within a DataAdapter by using the following SQL, but when I go to fill the DA's I get an error)

a) SELECT Sum(TimeSpent) AS TimeSpentDWR
FROM DwrWork
WHERE DWRNumber=@DWRNumber

The above totals the time spent in all revisions for a paramaterised DwrNo.

b) SELECT Sum(TimeSpent) AS TimeSpentRevision
FROM DwrWork
WHERE DWRNumber=@DWRNumber and DwrRevision=@DwrRevision

The above totals the time spent for a given revision within a paramaterised DwrNo.

What I couldn't figure out is how to use those DA's within my app so that it looks at the fields DWRNumber and DwrRevision and uses them as the parameters - i.e. so that when the user navigates through the records, the time spent fields are updated respectively.

I then read doing it this way isn't really a good idea because it's looking at the database and not the DataSet. I.E. the fields aren't updated until the user submits the changes made to the DataSet back to the DataBase.....so I thought about using COMPUTE.

Problem I have is that I can't figure out how to word the expression correctly. I think this may be impossible...but when you put the filter in, I want something along the lines of the following:

lblHoursDWR.Text = DsDWR1.DWRWork.Compute("SUM(TimeSpent)", "DWRNumber=lblDwrNo.text")

I get an error with "No Column found by the name of lblDwrNo.text"

I've got it working by manually specifying the DWRNumber, but of course as the users navigate, the label doesn't update.

Any ideas guys? This would be a huge help! I started off as a newbie a few months back, it's amazing what you can learn in such little time!!!

cheers, Luke
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
sum Total of Time Cards

I Sum the total of time cards in my app and display it in a label.
My sql statement takes care of getting the records to sum (selected by date).
Then I do this:

Dim sumTotalHours As Decimal
sumTotalHours = DsTimeCardTime1.Tables(0).Compute("Sum(Hours)", String.Empty)
lblTotalHours.Text = sumTotalHours

you could call this as a SUB whenever you get new data.

This line helps if it is a dollar amount you have summed:

lblTotAmt2.Text = FormatCurrency(sumTotalAmt)

Hope this helps,
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Thanks David,

Kind of along the lines of where I am already with it - when you say "SQL takes care of getting the records to sum", that's where my stumbling block is.

I need to work out how to call the paramaterised part of "WHERE DWRNo=@DWRNumber and DWRSequence=@DWRSequence". Using a DA and manually entering the values works fine, but I need the SQL / APP to be able to use the data in those 2 fields to then calculate the totals.

I can do it by calling a report, but the users want the time spent as a summary on one of the forms, so they don't want to have to generate a report everytime they want to look at the time totals.

thanks for your advice,
Luke
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
SQL Queries

The Dataform Wizard in VS.net writes all my SQL statements for me. Then I just tweek them as needed. Here is the select statement to get all the time records between two dates.
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT [Time ID], [Shop ID], Date, Employee, Hours, Comments, [Date Added], [Incl" & _
"ude In Accounting], Variance, [Time Type], Operation, Machine FROM Time WHERE (Date >=" & _
" @startdate) AND (Date <= @enddate) ORDER BY Date DESC, Employee, [Shop ID]"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@startdate", System.Data.SqlDbType.DateTime, 4, "Date"))
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@enddate", System.Data.SqlDbType.DateTime, 4, "Date"))
'


SqlSelectCommand1.Parameters("@startdate").Value = dStartdate
SqlSelectCommand1.Parameters("@enddate").Value = dEndDate


The meat of the select is where you set the parameters to something (last 2 lines).
Here I set them to public date variables established by date time pickers.

Public dStartdate As Date
Public dEndDate As Date

dStartdate = editStartDate.Text
dEndDate = editEndDate.Text

Use the wizard to get your SQL statements then:

You would most likely change the set parameters command to:

if Drawing Number is an integer:
SqlSelectCommand1.Parameters("@DWRNumber").Value = cint(lblDwrNo.text)

if Drawing Number is a string:
SqlSelectCommand1.Parameters("@DWRNumber").Value = lblDwrNo.text

Let me know, and we'll "get 'er dun"
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Hi David,

Thanks for the reply....

Due to me not using DataForm wizard (for whatever reason) I've had to do this another way - not sure whether it is correct though.

On the form load I have added the following code:

dim SQLDWRTime As New SqlCommand

SQLDWRTime.CommandText = "SELECT TimeSpent FROM DWRWork WHERE DWRNumber=@DWRNumber"

SQLDWRTime.Connection =
Me.conDWR

SQLDWRTime.Parameters.Add(
New System.Data.SqlClient.SqlParameter("@DWRNumber", System.Data.SqlDbType.Decimal))

SQLDWRTime.Parameters("@DWRNumber").Value =
CInt(lblDWRNo.Text)


Not sure whether I've missed something o**ious here, but how do I now bound the result of that SELECT Command to a label?

Cheers,
Luke
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Data Binding

Not sure what you are asking here. All labels have a databinding property for tag and text. In the properties of the label bind the text to a dataset field, or in the program do this:

Label1.DataBindings.Add("text", DataSource, "Field")
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
David,

Because of the way my code is, I've had to create that SQLCommand in code - it isn't editing any from my current dataset.

Therefore I wasn't sure how to bind the label to the result of the SQLCommand I'd programmed.

I may look into doing the DataAdapter again, and doing it that way....

Cheers
Luke
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Right, lets see whether I have this right - knowing me, probably not!!!!

I have a DataAdapter bringing in the following information:
SELECT WorkID, DWRNumber, DWRSequence, EmployeeID, TimeSpent FROM DWRWork

I then have a 2 DataGrids on my form - the first shows any Revisions for the Forms DWRNumber (DataRelation of DWRNumber), and the second DataGrid shows the EmployeeID and TimeSpent for each revision (This has a DataRelation to my Revisions table via DWRNumber & DWRSequence)

This works perfectly in showing what data is required by the user.

The above DA has SQLSelectCommand4.

I've then created a Private Sub called Hours, which is called on Form_Load that has the following code:

'SQLSelectCommand4 - Work
With SqlSelectCommand4
.CommandText = "SELECT DWRNumber, DWRSequence, TimeSpent FROM DWRWork"
.Connection = Me.conDWR
.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DWRNumber", System.Data.SqlDbType.Decimal))
.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DWRSequence", System.Data.SqlDbType.Decimal))
.Parameters("@DWRNumber").Value = (lblDWRNo.Text)
.Parameters("@DWRSequence").Value = (lblRevNo.Text)
EndWith
lblHoursRev.DataBindings.Add("Text", DsDWR1, "daWork.TimeSpent")

The form opens OK on the first tab, but when I click the Revisions tab (which has the 2 DataGrids on) I get error message "Cannot create Child Form for TimeSpent" - with the yellow highlight over "Public Class frmDWR".

Any ideas?!?
Cheers chap(s)
 
Top Bottom