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
Dim dt As DataTable = ds.Tables("Hours")
.AutoGenerateColumns = True
.DataSource = ds
.DataMember = "Hours"
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
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
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.