[Resolved - with issues]TableAdapter SQL DISTINCT Year

[Bruce Fox]

Hi Guys,

Prior to VB.Net 2005, I could return distinct years from a DateTime field by prefixing the field in the SQL statement with the Year() function (Eg: "SELECT DISTINCT Year[Log_Date] FROM tbl_Logbook GROUP BY Year[Log_Date]").

However, I cannot figure out how to do it with a DataTable/TableAdapter SQL in 2005 with the following SQL statement as 'Year' is an unrecognised function.

SELECT DISTINCT Year[Log_Date] FROM tbl_Logbook GROUP BY Year[Log_Date]

I have used the TableAdapter Configuration Wizard to no avail.


What I need is unique, distinct years from a DB.
Eg.

Log_Date column may have the following values: 23/5/2001, 28/6/2001, 2/9/2004

I need to return all records (grouped) by year, ie: 2001 and 2004.

Ideas?

[robertx]

I can't imagine that it has anything to do with the version of VS as VS just queries the database engine and provides a data object model to use the result. Try dropping the GROUP BY clause from your SQL statement and see whether that makes any difference. GROUP BY is not necessary when you are returning a single distinct field.

"SELECT DISTINCT Year[Log_Date] FROM tbl_Logbook"

[Bruce Fox]

Hi Robert, tried that earlier.

SQL (applied to the TableAdapter) won't recognise 'Year' (as it does, as I have used it, with declared data objects/connections within code).

What I need is a way to 'format' the Log_Date field to Year, then use that. I have in the passed been able to incorporate the Year() functions a mentioned within the SQL.

[jmcilhinney]

As robertx says, the programming language has no impact on your query because it's the database that processes the SQL code. Whatever code worked with your database before will still work with your database.

If what you have doesn't work then that means that something you didn't intend happened. My guess is that you would have received an error message, in which case you should have passed it on to us. I'm not sure if it's prohibited but the GROUP BY clause is intended to be used in conjunction with an aggregate function, E.g. SUM, AVG. You have no aggregate function in your query so the GROUP BY clause is at best useless.

Also, in all databases I've worked with you use parentheses, i.e. round brackets, to enclose a function parameter list and brackets, i.e. square brackets, to enclose an object name that may include otherwise invalid characters. Maybe your database is different.

[robertx]

I'm not sure if it's prohibited but the GROUP BY clause is intended to be used in conjunction with an aggregate function, E.g. SUM, AVG. You have no aggregate function in your query so the GROUP BY clause is at best useless.

I just tested this with SQL Server 2005. Whilst the GROUP BY clause is not prohibited, it makes no differences to the result.

[jmcilhinney]

I also just tested against an Access database. I configured a TableAdapter with a query containing the following SQL code:

SELECT DISTINCT YEAR(DateColumn) FROM Table1

and it worked exactly as expected. I added five records to the table with five different dates but only three different years. The query returned three rows, as expected.

[Bruce Fox]

Thanks all.

Hmmm, @ jmcilhinney, that is what I'm trying to do, but I get the 'Undefined function [YEAR] in expression' using the Query Builder, and Execute Query, when using:

SELECT DISTINCT YEAR(Log_Date) FROM tbl_Logbook

[Bruce Fox]

Would you mind attaching that demo app so I can see what you did?

For what its worth, I have a DataSet etc that loads a DataGrid - all works fine. I just added a new DataTable/Table Adapter, which WORKS if I don't use 'Year()'.....

[robertx]

It would appear that the problem is that the database engine doesn't support the year function. What database are you using?

[Bruce Fox]

That was my next thought, as Max and Min etc work.

I'll post back....

[Bruce Fox]

Hmmm, I can use the YEAR() function in the mentioned SQL string with the app I wrote in VB.Net 2003, so I figured the Jet Eng wouldn't be an issue.
It (the Year()) dosn't work with 2005. I guess it may be an issue.....

[jmcilhinney]

I didn't use the query builder. I just typed into the wizard.

[Bruce Fox]

Ok, it seems to Error out in the Query Builder, but not in the wizzard.

I did however, create a fresh (additional) DataSet etc and it worked...

I will no experiment using the YEAR() in against an existing DataTable query and use it in code.

[Bruce Fox]

Ok, works... if you dont try to run the Query Builder/Execute Query!!!!


Thanks for your assistance


BTW, JM could you see if you generate an error in the builder?