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

[Arg81]

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]

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]

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]

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]

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]

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]

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]

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)