[02/03] Select statement help required please

[frankwhite]

Hi people,

I was hoping someone could help me out please.

What I need is to do is basically run a sql statement where it shows the company name, chare code, counts of all the charge codes and the sum of time spent.

For example I have the following statement so far:

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter

        Dim selCommand As New OleDbCommand
        With selCommand
            .CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  COUNT(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [TSpent]"
            .CommandType = CommandType.Text
            .Connection = conn
            'Add Parameters 
            .Parameters.Add(New OleDbParameter("@Date1", OleDbType.Date)).Value = dtp.Value
            .Parameters.Add(New OleDbParameter("@Date2", OleDbType.Date)).Value = dtp2.Value
            .Parameters.Add(New OleDbParameter("@CompID", OleDbType.Integer)).Value = cmbcomp.SelectedValue
        End With
        'assign select command to data adapter 
        adapter.SelectCommand = selCommand
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False

What the above coding does is list the answer as follows:

Company name,Charge Code, Count, Time Spent
Adidas, ABS, 5, 5
Adidas, ABS, 7, 7
Adidas, RYS. 4, 4

However I want it as follows:

Company name,Charge Code, Count, Time Spent
Adidas, ABS, 12, (Total Sum)
Adidas, RYS. 4, (Total Sum)

Any help please?

**EDIT** Tried the following:

.CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"

Which shows everything correctly (apart from the time spent column) So I decided to include the Time Spent column which I assume will have to be a SUM function as I want to add up all the time spent for that particular charge code, so my statement has been changed to:

.CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  SUM(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [TSpent]"

However this totally shows the wrong data as the Charge Code column is no longer counted but listed per charge code and the time spent is not even showing the correct values.

[Hack]

This is more of a database/SQL question than a .NET question. Moved

[si_the_geek]

Your last attempt was close, you just made a minor mistake - you added [TSpent] to the Group By clause, which you shouldn't have.

The Group By should only include things in your Select clause that do not use functions - as the functions basically do the same kind of thing as putting a field into the Group By (they specify how to "merge" rows).

[frankwhite]

Thanks Si,

I tried the coding but the result is not what Im looking for, the result shows the count of charge code and the time spent column exactly the same however they should not be the same because the time spent column should be counted up (Sum?) I think.

[si_the_geek]

Can you show us some lines of data from the table, along with the exact output you want based on that data?

[techgnome]

TSpent should be a SUM .... NOT a COUNT ....

-tg

[frankwhite]

Hi Si and Techgnome, I tried the following statement:

 .CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  SUM(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"

However it breaks on the adapter.fill line.

My data is shown in the picture:

What I need help with is to show the company, charge code, count of that particular charge code (which I have working so far) and finally the sum of the time spent. The problem I have is the data in Time Spent is saved as 00:00/00:30/01:00/01:30 - like every half an hour so when I do the sum I need to calculate is for every hour so 00:30 and 00:30 should be 01:00 rather than 00:60 (T spent is a text field in the Access database) Should I change this?

http://tinypic.com/view.php?pic=21azb6u&s=4

[si_the_geek]

However it breaks on the adapter.fill line.

As always, there are several million possibilities for what that could mean.. it is a good idea to give us some detail when you have an error, as i may tell us exactly what the issue is (even if it doesn't make sense to you).

(T spent is a text field in the Access database) Should I change this?

Why are you storing a Time value as Text, when there is a perfectly good data type for it?

Using the wrong data type slows things down, can cause many errors and bugs, and often requires extra work to handle correctly.

Using mathematical functions (like Sum) on strings is very prone to errors, especially with Access/Jet - which will automatically convert the string to whatever data seems best at that moment (so sometimes converted to Date/Time, and others to Numeric types - if it is ever inconsistent you will get the wrong answer but no error).

[frankwhite]

I have the following coding:

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter

        Dim selCommand As New OleDbCommand
        With selCommand
            .CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  SUM(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"
            .CommandType = CommandType.Text
            .Connection = conn
            'Add Parameters 
            .Parameters.Add(New OleDbParameter("@Date1", OleDbType.Date)).Value = dtp.Value
            .Parameters.Add(New OleDbParameter("@Date2", OleDbType.Date)).Value = dtp2.Value
            .Parameters.Add(New OleDbParameter("@CompID", OleDbType.Integer)).Value = cmbcomp.SelectedValue
        End With
        'assign select command to data adapter 
        adapter.SelectCommand = selCommand
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False

And it breaks on the adapter.fill line.

With the following error

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

So I included an exception (never done this before so please correct me if Im wrong)

    Try
            adapter.Fill(dt)
        Catch ex As Exception
            MessageBox.Show(ex.InnerException.ToString)
        End Try

And it threw the following error on the messagebox.show line, the error was:

An unhandled exception of type 'System.NullReferenceException' occurred in CNSSupport.exe

Additional information: Object reference not set to an instance of an object.

Sorry point taken so I have changed the TSpent field to time.

[riteshjain1982]

I tried almost same and it works fine,
what is the datatype of Dol column? also try pass dtp.Value.Date and dtp2.Value.Date
still gives error just try with only @CompID parameter (update query and comment 2 date related parameter) and see if it's returning any result?

[frankwhite]

Hi,

I dont think the values of the dates have any problems, because I have other select statements running in my application which use the dates values and it is correct. However I have so far got to the following statement which shows the correct data between the dates including the company name, charge code, count of charge code but the time spent field does not look correct to me:

"Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"

I did change the time field to short date, the times that are currently set in the field are either 00:00, 00:30, 01:00, 01:30 and so on (so they go up in 30 mins) So the sum it should show should either have a 00 or a 30 at the end and the first digit (hour format) should be correct. I know im getting there, if you know what it might be then please let me know.

[riteshjain1982]

will it be possible for you to post some sample data for column TSpent

[frankwhite]

No problem, you can see an example in post number my earlier post where I have posted a url.

I think I understand where the problem is coming from.

If we take the following data as an example:

names timespent
bob 0:30
dave 1:00
fred 1:45
george 2:15
albert 0:15

If I sum these up I get .29 of a day or 5 3/4 hours which is right. My Sum is currently in terms of days or a fraction of a day but would it be possible to show my data in the hourly format?

Rather than showing it in a day format, I want to show it in the hourly format.

THanks

[si_the_geek]

If the value is correct, you can simply change the output format in the SQL statement:

... , Format(SUM(TSpent),'hh:nn') As SumofTimeSpent ...

Note that the Format function returns a String, so if you are doing anything other than displaying it you will need extra work to convert it between data types.

An alternative would be to format it in your display, which I think you can do by specifying it in the control, or perhaps even in the DataTable.