Hi,

I've just recently used LLBLGEN Pro 2.5 and I'm trying to use a typedlistdao as the datasource for the dropdown. Basically i'm trying to fill the dropdown with the result of the Sql statement:

SELECT columnname FROM table GROUP BY columnname

On my web form i have a dropdown and an objectdatasource:

<asp ropDownList ID="ddlSite" runat="server" DataSourceID="odsSites" DataTextField="Site" DataValueField="Site"> </asp ropDownList>

<asp bjectDataSource ID="odsSites" SelectMethod="GetSites" TypeName="MyBLL" runat="server"> </asp bjectDataSource>

In my BLL i have a function called GetSites:

public DaoClasses.TypedListDAO GetSites() { HelperClasses.ResultsetFields fields = new HelperClasses.ResultsetFields(1); fields.DefineField(HelperClasses.IncidentFields.SiteId, 0, "Site", "Site");

    SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection groupByClause = new SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection();
    groupByClause.Add(fields[0]);

    DataTable dynamicList = new DataTable();
    DaoClasses.TypedListDAO dao = new DaoClasses.TypedListDAO();
    dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null,null, true, groupByClause, null, 0, 0);

    return dao;

}

I'm getting this error message at runtime: DataBinding: 'DaoClasses.TypedListDAO' does not contain a property with the name 'Site'.

I could be going about this in a totally wrong way so any help would be appreciated.

Thanks in advance, John

Your method should return the dataTable, as follows:

public DataTable GetSites()
{
        HelperClasses.ResultsetFields fields = new HelperClasses.ResultsetFields(1);
        fields.DefineField(HelperClasses.IncidentFields.SiteId, 0, "Site", "Site");

        SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection groupByClause = new SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection();
        groupByClause.Add(fields[0]);

        DataTable dynamicList = new DataTable();
        DaoClasses.TypedListDAO dao = new DaoClasses.TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null,null, true, groupByClause, null, 0, 0);
        
        return dynamicList ;
}

Walaa wrote:

Your method should return the dataTable, as follows:

public DataTable GetSites()
{
        HelperClasses.ResultsetFields fields = new HelperClasses.ResultsetFields(1);
        fields.DefineField(HelperClasses.IncidentFields.SiteId, 0, "Site", "Site");

        SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection groupByClause = new SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection();
        groupByClause.Add(fields[0]);

        DataTable dynamicList = new DataTable();
        DaoClasses.TypedListDAO dao = new DaoClasses.TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null,null, true, groupByClause, null, 0, 0);
        
        return dynamicList ;
}

Great!! Thanks for your help! John

Hi Walaa,

I'm now trying to formulate the SQL below (with a dynamic WHERE clause) using the previous code as a basis.

SELECT DISTINCT TOP 100 PERCENT dbo.INCIDENT.SITE_ID, dbo.INCIDENT.INCIDENT_NO, dbo.INCIDENT.EXPOSURE_NO, dbo.INCIDENT.CAD_REFERENCE, dbo.INCIDENT.RPT_STAFF_REG_NO, dbo.INCIDENT.ALARM_DATE, dbo.INCIDENT.ALARM_TIME, dbo.INCIDENT.STREET, dbo.INCIDENT.SUBURB, dbo.A23_INCIDENT_TYPE_CODE.[DESC] AS INCIDENT_TYPE FROM dbo.INCIDENT LEFT OUTER JOIN dbo.A23_INCIDENT_TYPE_CODE ON dbo.INCIDENT.INCIDENT_TYPE = dbo.A23_INCIDENT_TYPE_CODE.CODE WHERE (dbo.INCIDENT.BRIGADE_ID = '812') AND (dbo.INCIDENT.IS_RECORD_COMPLETE = 0) ORDER BY dbo.INCIDENT.ALARM_DATE DESC, dbo.INCIDENT.ALARM_TIME DESC, dbo.INCIDENT.EXPOSURE_NO

My Code:

protected void DoSearch(object sender, EventArgs e) { SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection myRelations = new SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection();

    myRelations.Add(myExtendedRelation.A23IncidentTypeCodeEntityUsingCodeIncidentType, "A23");

    SD.LLBLGen.Pro.ORMSupportClasses.IPredicateExpression filter = new SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression();

    if (rblIncidentStatus.SelectedValue == "1")
        filter.Add((Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.IsRecordComplete % rblIncidentStatus.SelectedValue.ToString()));

    if (ddlBrigade.SelectedIndex > 0)
        filter.Add((Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.BrigadeId % ddlBrigade.SelectedValue.ToString()));

    if (!string.IsNullOrEmpty(txtAlarmDateFrom.Text))
        filter.Add(new SD.LLBLGen.Pro.ORMSupportClasses.FieldBetweenPredicate(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmDate, txtAlarmDateFrom.Text, txtAlarmDateTo.Text));

    if (!string.IsNullOrEmpty(txtCADReference.Text))
        filter.Add(new SD.LLBLGen.Pro.ORMSupportClasses.FieldLikePredicate(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.CadReference, txtCADReference.Text));

    if (ddlSite.SelectedIndex > 0)
        filter.Add((Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.SiteId % ddlSite.SelectedValue.ToString()));

    if (!string.IsNullOrEmpty(txtIncidentNo.Text))
        filter.Add((Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.IncidentNo % txtIncidentNo.Text));


    if (!string.IsNullOrEmpty(txtExposureNo.Text))
        filter.Add((Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.ExposureNo % txtExposureNo.Text));

    Ntpfes.FireReportingSystem.Data.HelperClasses.ResultsetFields fields = new Ntpfes.FireReportingSystem.Data.HelperClasses.ResultsetFields(10);
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.SiteId, 0, "SiteId", "SiteId");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.IncidentNo, 1, "IncidentNo", "IncidentNo");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.ExposureNo, 2, "ExposureNo", "ExposureNo");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.CadReference, 3, "CadReference", "CadReference");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.RptStaffRegNo, 4, "RptStaffRegNo", "RptStaffRegNo");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmDate, 5, "AlarmDate", "AlarmDate");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmTime, 6, "AlarmTime", "AlarmTime");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.Street, 7, "Street", "Street");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.Suburb, 8, "Suburb", "Suburb");
    fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.A23IncidentTypeCodeFields.Desc, 9, "IncidentType", "IncidentType");

    SD.LLBLGen.Pro.ORMSupportClasses.SortExpression sort = new SD.LLBLGen.Pro.ORMSupportClasses.SortExpression();
    sort.Add(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmDate | SD.LLBLGen.Pro.ORMSupportClasses.SortOperator.Descending);
    sort.Add(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmTime | SD.LLBLGen.Pro.ORMSupportClasses.SortOperator.Descending);
    sort.Add(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.ExposureNo | SD.LLBLGen.Pro.ORMSupportClasses.SortOperator.Ascending);

    DataTable dynamicList = new DataTable();
    Ntpfes.FireReportingSystem.Data.DaoClasses.TypedListDAO dao = new Ntpfes.FireReportingSystem.Data.DaoClasses.TypedListDAO();
    dao.GetMultiAsDataTable(fields, dynamicList, 0, sort, filter, myRelations, true, null, null, 0, 0);

    gvIncidents.DataSource = dynamicList;
    gvIncidents.Visible = true;
    gvIncidents.DataBind();
}

I'm getting these errors:

The column prefix 'SiteId' does not match with a table name or alias name used in the query. The column prefix 'IncidentNo' does not match with a table name or alias name used in the query. The column prefix 'ExposureNo' does not match with a table name or alias name used in the query. The column prefix 'CadReference' does not match with a table name or alias name used in the query. The column prefix 'RptStaffRegNo' does not match with a table name or alias name used in the query. The column prefix 'AlarmDate' does not match with a table name or alias name used in the query. The column prefix 'AlarmTime' does not match with a table name or alias name used in the query. The column prefix 'Street' does not match with a table name or alias name used in the query. The column prefix 'Suburb' does not match with a table name or alias name used in the query. The column prefix 'IncidentType' does not match with a table name or alias name used in the query.

My guess is that the GetMultiAsDataTable function is not returning the datatable correctly, can you please help.

Thanks in advance, John

Hi John,

I think the problem is here:

myRelations.Add(myExtendedRelation.A23IncidentTypeCodeEntityUsingCodeIncidentType, "A23");

You are specifying an alias but you are not using it in the further expressions. You should specify the alias in every field used in DefineField or in predicate expressions. However I don't think the alias is necessary here, so just don't use it when adding the relation:

myRelations.Add(myExtendedRelation.A23IncidentTypeCodeEntityUsingCodeIncidentType);

BTW, what is myExtendedRelation?

Some side notes: - Use using clauses to avoid write every namespace in your filters/fields/llblgen classes. - To read more comfortable, please use the "[code][/code]" tags when posting code.

Thanks for the tip in using the namespaces

BTW, what is myExtendedRelation?

I've actually taken that out now and still getting the same error. Basically the relationship between this 2 tables (INCIDENT and A23_INCIDENT_TYPE_CODE) was not defined in the database thus it was not included by relationclass.

Here's my code now:

protected void DoSearch(object sender, EventArgs e)
    {
        IPredicateExpression filter = new PredicateExpression();

        if (rblIncidentStatus.SelectedValue == "1")
            filter.Add((IncidentFields.IsRecordComplete % rblIncidentStatus.SelectedValue.ToString()));
        if (lbBrigade.SelectedIndex > 0)
            filter.Add((IncidentFields.BrigadeId % lbBrigade.SelectedValue.ToString()));
        if (!string.IsNullOrEmpty(txtAlarmDateFrom.Text))
            filter.Add(new FieldBetweenPredicate(IncidentFields.AlarmDate, txtAlarmDateFrom.Text, txtAlarmDateTo.Text));
        if (!string.IsNullOrEmpty(txtCADReference.Text))
            filter.Add(new FieldLikePredicate(IncidentFields.CadReference, txtCADReference.Text));
        if (ddlSite.SelectedValue != "All")
            filter.Add((IncidentFields.SiteId % ddlSite.SelectedValue.ToString()));
        if (!string.IsNullOrEmpty(txtIncidentNo.Text))
            filter.Add((IncidentFields.IncidentNo % txtIncidentNo.Text));
        if (!string.IsNullOrEmpty(txtExposureNo.Text))
            filter.Add((IncidentFields.ExposureNo % txtExposureNo.Text));

        ResultsetFields fields = new ResultsetFields(10);
        fields.DefineField(IncidentFields.SiteId, 0, "SiteId", "SiteId");
        fields.DefineField(IncidentFields.IncidentNo, 1, "IncidentNo", "IncidentNo");
        fields.DefineField(IncidentFields.ExposureNo, 2, "ExposureNo", "ExposureNo");
        fields.DefineField(IncidentFields.CadReference, 3, "CadReference", "CadReference");
        fields.DefineField(IncidentFields.RptStaffRegNo, 4, "RptStaffRegNo", "RptStaffRegNo");
        fields.DefineField(IncidentFields.AlarmDate, 5, "AlarmDate", "AlarmDate");
        fields.DefineField(IncidentFields.AlarmTime, 6, "AlarmTime", "AlarmTime");
        fields.DefineField(IncidentFields.Street, 7, "Street", "Street");
        fields.DefineField(IncidentFields.Suburb, 8, "Suburb", "Suburb");
        fields.DefineField(IncidentFields.IncidentType, 9, "IncidentType", "IncidentType");
        
        SortExpression sort = new SortExpression();
        sort.Add(IncidentFields.AlarmDate | SortOperator.Descending);
        sort.Add(IncidentFields.AlarmTime | SortOperator.Descending);
        sort.Add(IncidentFields.ExposureNo | SortOperator.Ascending);

        DataTable dynamicList = new DataTable();
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, sort, filter, null, true, null, null, 0, 0);

        gvIncidents.DataSource = dynamicList;
        gvIncidents.Visible = true;
        gvIncidents.DataBind();
    }

For this field:

fields.DefineField(IncidentFields.IncidentType, 9, "IncidentType", "IncidentType");

I want to return the actual description from the A23_INCIDENT_TYPE_CODE table instead of the IncidentType code.

Hope I makes sense. Thanks John

The problem is that you are defining ObjectAliases to every field you use although they come from the same table, this should only be used if you are Joining to the same table more than once, to differentiate between them.

so the following should be enough:

fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.SiteId, 0, "SiteId");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.IncidentNo, 1, "IncidentNo");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.ExposureNo, 2, "ExposureNo");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.CadReference, 3, "CadReference");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.RptStaffRegNo, 4, "RptStaffRegNo");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmDate, 5, "AlarmDate");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.AlarmTime, 6, "AlarmTime");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.Street, 7, "Street");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.IncidentFields.Suburb, 8, "Suburb");
        fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.A23IncidentTypeCodeFields.Desc, 9, "IncidentType");

Also either ommit the fields aliases from the above modified code, or use these fields directly in all your predicates.

For example instead of:

filter.Add((IncidentFields.SiteId % ddlSite.SelectedValue.ToString()));

You should use:

filter.Add((fields[0] % ddlSite.SelectedValue.ToString()));

Thanks i'll try that tomorrow. I've also mentioned that 1 field is coming from a different table

fields.DefineField(Ntpfes.FireReportingSystem.Data.HelperClasses.A23IncidentTypeCodeFields.Desc, 9, 

How do I add a relationship between INCIDENT and A23_INCIDENT_TYPE_CODE tables if the relationship was not included in the relationclass?

Thanks John

How do I add a relationship between INCIDENT and A23_INCIDENT_TYPE_CODE tables if the relationship was not included in the relationclass?

If you mean no relation was defined between these entities in the database and hence in the designer. In this case you can create a relation in code using the EntityRelation CTor.

Walaa wrote:

How do I add a relationship between INCIDENT and A23_INCIDENT_TYPE_CODE tables if the relationship was not included in the relationclass?

If you mean no relation was defined between these entities in the database and hence in the designer. In this case you can create a relation in code using the EntityRelation CTor.

I've hard coded the relation in the existing IncidentRelation class:

public virtual IEntityRelation A23IncidentTypeCodeUsingCode
        {
            get
            {
                IEntityRelation relation = new EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.ManyToOne, "A23IncidentTypeCode", true);

                relation.AddEntityFieldPair(IncidentFields.IncidentType, A23IncidentTypeCodeFields.Code);
                relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("IncidentEntity", false);
                relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("A23IncidentTypeCodeEntity", true);
                return relation;
            }
        }

It is working but it looks like its using a 1:1 relationship. I want to return all rows from the Incident table. I want to implement a LEFT OUTER JOIN between Incident and A23IncidentTypeCode.

Thanks John

RelationType.ManyToOne --> RelationType.OneToMany.

Walaa wrote:

RelationType.ManyToOne --> RelationType.OneToMany.

Thanks finally got it working.

I also had to add JointHint.Left on

RelationCollection myRelations = new RelationCollection();
myRelations.Add(IncidentEntity.Relations.A23IncidentTypeCodeUsingCode, JoinHint.Left);

Thanks for all your help guys! John

Good John