Hayden Hayden - 7 months ago 25
Vb.net Question

Tried to execute a query that does not include the specified expression 'StaffDetails.StaffID' as part of an aggregate function

I have a datagrid called DGHours which I would like to be populated with the SQL below. However I have been given the error message 'Additional information: You tried to execute a query that does not include the specified expression 'StaffDetails.StaffID' as part of an aggregate function.' I don't understand what is wrong with my sql, any help? I want the data grid to display StaffID, FirstName, LastName, and the total number of hours by by the inner joined staffID. I think the problem is to do with SUM(TimeSheet.Hours).

Dim SqlQuery As String = "SELECT [StaffDetails.StaffID], [StaffDetails.FirstName], [StaffDetails.LastName], [TimeSheet.StaffID], SUM(TimeSheet.Hours) FROM [StaffDetails] INNER JOIN [TimeSheet] ON [StaffDetails].StaffID = [TimeSheet].StaffID WHERE [TimeSheet].TimeSheetMonth='" & cbMonth.Text & "'"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(SqlQuery, conn)
Dim ds As DataSet = New DataSet
da.Fill(ds, "Hours")
Dim dt As DataTable = ds.Tables("Hours")

With DGHours
.AutoGenerateColumns = True
.DataSource = ds
.DataMember = "Hours"
End With

Answer

If you use square brackets, they should include each single component like table name or column name, not the whole thing: Use e. g. [StaffDetails].[StaffID] instead of StaffDetails.StaffID].

But in your case, you could also just omit all square brackets in the statement. These would only be necessary if you have special characters like spaces, commas, etc. in the table or column names, or if you would use SQL keywords like SELECT or FROM as table names (which is bad practice anyway).

Furthermore, if you use aggregate functions like Sum on some columns, you need to GROUP BY the other columns, i. e. for your query you would have to append

GROUP BY StaffDetails.StaffID,
         StaffDetails.FirstName,
         StaffDetails.LastName,
         TimeSheet.StaffID

to the end of your query.