I have some errors and i cant find them.

I use Version 2.0.0.0 from December with Adapter Template

Here ist the code:

   public DataTable Test(Guid mitarbeiterID)
        {
            ResultsetFields fields = new ResultsetFields(1);
            PredicateExpression expression = new PredicateExpression(ArbeitsprotokollFields.MitarbeiterID == mitarbeiterID);
            fields.DefineField(ArbeitsprotokollFields.Datum, 0, "Jahre", "Arbeitsprotokoll");
            fields[0].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { ArbeitsprotokollFields.Datum });

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.HavingClause = expression;

            DataTable data = new DataTable();
            this.DataAdapter.FetchTypedList(fields, data, null, 0, null, true, groupByClause);
            return data;
        }

Here is the error:

------------------------------
Server Name: xxxx\SQLEXPRESS
Error Number: 4104
Severity: 16
State: 1
Line Number: 1

TITLE: CurrentDomain
------------------------------

CurrentDomain_UnhandledException

------------------------------
ADDITIONAL INFORMATION:

Der mehrteilige Bezeichner "Optix001.dbo.Arbeitsprotokoll.Datum" konnte nicht gebunden werden.
Der mehrteilige Bezeichner "Optix001.dbo.Arbeitsprotokoll.MitarbeiterID" konnte nicht gebunden werden.
Der mehrteilige Bezeichner "Optix001.dbo.Arbeitsprotokoll.Datum" konnte nicht gebunden werden. (Microsoft SQL Server, Error: 4104)

and here is the generated SQL Statement

Generated Sql query: 
    Query: SELECT YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum]) AS [Jahre] FROM [Optix001].[dbo].[Arbeitsprotokoll] AS [Arbeitsprotokoll]  GROUP BY YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum]) HAVING ( [Optix001].[dbo].[Arbeitsprotokoll].[MitarbeiterID] = @MitarbeiterID1)
    Parameter: @MitarbeiterID1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 300d6819-3b90-4040-9349-9ee71d96edca.

Error complete

Informationen über das Aufrufen von JIT-Debuggen
anstelle dieses Dialogfelds finden Sie am Ende dieser Meldung.

************** Ausnahmetext **************
System.Data.SqlClient.SqlException: Der mehrteilige Bezeichner "Optix001.dbo.Arbeitsprotokoll.Datum" konnte nicht gebunden werden.
Der mehrteilige Bezeichner "Optix001.dbo.Arbeitsprotokoll.MitarbeiterID" konnte nicht gebunden werden.
Der mehrteilige Bezeichner "Optix001.dbo.Arbeitsprotokoll.Datum" konnte nicht gebunden werden.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   bei System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   bei System.Data.SqlClient.SqlDataReader.get_MetaData()
   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   bei System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   bei System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   bei System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   bei SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
   bei SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
   bei SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause)
   bei DH.Optix.ObjectModel.ArbeitsprotokollManager.Test(Guid mitarbeiterID) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\Manager\ArbeitsprotokollManager.cs:Zeile 217.
   bei DH.Optix.ObjectModel.DataControls.ArbeitsprotokollListDataControl.OnEntityChanged() in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\DataControls\Sonstige\ArbeitsprotokollListDataControl.cs:Zeile 60.
   bei DH.Optix.ObjectModel.DataControls.DataControl.set_Entity(EntityBase2 value) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\DataControls\DataControl.cs:Zeile 137.
   bei DH.Optix.ObjectModel.DataForms.MitarbeiterDataForm.BindDataControls() in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\DataForms\MitarbeiterDataForm.cs:Zeile 39.
   bei DH.Optix.ObjectModel.DataForms.MitarbeiterDataForm..ctor(MitarbeiterEntity Entity) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\DataForms\MitarbeiterDataForm.cs:Zeile 19.
   bei DH.Optix.ObjectModel.MitarbeiterManager.Edit(MitarbeiterEntity Mitarbeiter, Boolean Modal) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\Manager\MitarbeiterManager.cs:Zeile 155.
   bei DH.Optix.ObjectModel.MitarbeiterManager.<GetBrowser>b__1(MitarbeiterEntity entity) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\Manager\MitarbeiterManager.cs:Zeile 99.
   bei DH.Optix.ObjectModel.Browser.EntityBrowser`1.buttonBearbeiten_Click(Object sender, EventArgs e) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\Browser\EntityBrowser.cs:Zeile 273.
   bei DH.Optix.ObjectModel.Browser.EntityBrowser`1.lvListView_DoubleClick(Object sender, EventArgs e) in D:\Daten\Dot Net Sources\Optix Net\Source\Optix.ObjectModel\Browser\EntityBrowser.cs:Zeile 300.
   bei System.Windows.Forms.Control.OnDoubleClick(EventArgs e)
   bei System.Windows.Forms.ListView.WndProc(Message& m)
   bei System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   bei System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   bei System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

thanks for help

Olaf

Olaf, unfortunately I can't understand your error messages as they are not in English. Your query appears to be illegal though as it does not define an aggregate expression though it uses the GROUP BY clause. Have you tried executing the generated SQL statement directly in SQL server?

Chester, thank you for helping.

that is the english Message:

Msg 4104, Level 16, State 1, xxx, Line x
The multi-part identifier "Optix001.dbo.Arbeitsprotokoll.Datum" could not be bound.
The multi-part identifier "Optix001.dbo.Arbeitsprotokoll.MitarbeiterID" could not be bound.
The multi-part identifier "Optix001.dbo.Arbeitsprotokoll.Datum" could not be bound.

This ist the Arbeitsprotokoll table definition:

MitarbeiterID uniqueidentifier
Datum datetime

I change the method and add the MitarbeiterID to the GroupByCollection

public DataTable Test(Guid mitarbeiterID)
        {
            ResultsetFields fields = new ResultsetFields(2);
            PredicateExpression expression = new PredicateExpression(ArbeitsprotokollFields.MitarbeiterID == mitarbeiterID);
            fields.DefineField(ArbeitsprotokollFields.Datum, 0, "Jahre", "Arbeitsprotokoll");
            fields[0].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { ArbeitsprotokollFields.Datum });

            fields.DefineField(ArbeitsprotokollFields.MitarbeiterID, 1, "MitarbeiterID", "Arbeitsprotokoll");
            
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);
            groupByClause.HavingClause = expression;

            DataTable data = new DataTable();
            this.DataAdapter.FetchTypedList(fields, data, null, 0, null, true, groupByClause);
            return data;
        }

this is the orginal query, that doesn't run in the Query Designer:

SELECT 
YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum]) AS [Jahre],
[Arbeitsprotokoll].[MitarbeiterID] 
FROM [Optix001].[dbo].[Arbeitsprotokoll] AS [Arbeitsprotokoll]  
GROUP BY 
YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum]),
[Arbeitsprotokoll].[MitarbeiterID] 
HAVING 
([Optix001].[dbo].[Arbeitsprotokoll].[MitarbeiterID] ='300d6819-3b90-4040-9349-9ee71d96edca')

and this one have no problemes:

SELECT YEAR(Datum) AS Jahre FROM Arbeitsprotokoll as Arbeitsprotokoll GROUP BY YEAR(Datum),MitarbeiterID HAVING (MitarbeiterID ='300d6819-3b90-4040-9349-9ee71d96edca')

Thanks

Olaf

I changed my test method to:

public DataTable Test(Guid mitarbeiterID) { ResultsetFields fields = new ResultsetFields(1); fields.DefineField(ArbeitsprotokollFields.Datum, 0, "Jahre", "Arbeitsprotokoll"); fields[0].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { ArbeitsprotokollFields.Datum });

        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);

        RelationPredicateBucket bucket = new RelationPredicateBucket(new PredicateExpression(ArbeitsprotokollFields.MitarbeiterID == mitarbeiterID));

        DataTable data = new DataTable();
        this.DataAdapter.FetchTypedList(fields, data, bucket, 0, null, true, groupByClause);
        return data;
    }

(no HAVING) but i get the same error's

Does the catalog 'Optix001' exist? It can't find hte table/field you're referring to in the database server you're connecting to.

@Otis

yes, there a lot of other queries run against the optix001 catalog in the same Form.

When i run this query in the optix001 catalog with query editor, i get the same error.

(sorry for bad english).

Olaf

OlafD wrote:

When i run this query in the optix001 catalog with query editor, i get the same error.

This would seem to confirm Otis' suspicion that the catalog doesn't exist, or the schema, or table, or column. You have to get the query to execute correctly when run directly against the database. Can you try to get this much working?

sorry, but i cant find my error.

this one doesn't run in the editor (generated by llblgen, i have changed the parameter)):

SELECT YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum]) AS [Jahre] FROM [Optix001].[dbo].[Arbeitsprotokoll] AS [Arbeitsprotokoll]  WHERE ( ( [Optix001].[dbo].[Arbeitsprotokoll].[MitarbeiterID] = '300d6819-3b90-4040-9349-9ee71d96edca')) GROUP BY YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum])

and this one run in the editor:

SELECT YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum]) AS [Jahre] FROM [Optix001].[dbo].[Arbeitsprotokoll]  WHERE ( ( [Optix001].[dbo].[Arbeitsprotokoll].[MitarbeiterID] = '300d6819-3b90-4040-9349-9ee71d96edca')) GROUP BY YEAR([Optix001].[dbo].[Arbeitsprotokoll].[Datum])

i have remove "AS [Arbeitsprotokoll]" from the query.

Olaf

The 'error' is in this line:

fields.DefineField(ArbeitsprotokollFields.Datum, 0, "Jahre", "Arbeitsprotokoll");

Here you alias the table to "Arbeitsprotokoll". This is unnecessary as you're not joining the same entity twice. It goes wrong because in this line:

fields[0].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { ArbeitsprotokollFields.Datum });

you refer to a field which doesn't have its entity alias set, so it won't refer to the aliased entity.

So I'd suggest to remove the "Arbeitsprotokoll" alias from the DefineField call.

Otis, thank you. You are my "Hero of the Week". (and today is monday )

I changed it to

fields.DefineField(ArbeitsprotokollFields.Datum, 0, "Jahre");

and it works.
There are 302 overloads for DefineField, so i haven't find the right one.

Olaf

OlafD wrote:

Otis, thank you. You are my "Hero of the Week". (and today is monday )

Cool!

I changed it to

fields.DefineField(ArbeitsprotokollFields.Datum, 0, "Jahre");

and it works.
There are 302 overloads for DefineField, so i haven't find the right one. Olaf

In v2.0 we changed the DefineField methods: if you don't need the FieldIndex based ones (which form 99% of the DefineField methods in the generated code), simply place the backwards compatibility templatebindings in the tab2 of the generator configuration dialog below the shared templatebindings. The definefield method you're using is defined on the EntityFields2 object in the runtime libraries, so the generated code will be smaller if you leave these fieldindex based definefield methods out of hte generated code. Of course, if you have code which uses these definefield methods which use fieldindexes, you have to keep them.

simply place the backwards compatibility templatebindings in the tab2 of the generator configuration dialog below the shared templatebindings

Thanks again.

Olaf