Hi, I've just come up against an ORMQueryExecutionException when inserting records using LLBLGenPro2 datasource control with a DetailsView control. First the important details

LBLGenPro version 2.0.0.0 Final Runtime Libs 2.0.0.0 DQE.Sql.Net.2.0 File version: 2.0.7.129 ORM.Support File version: 2.0.7..402

Stack Trace

   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit)
   at CaptureV4.WkpgB.ResortMain._resortRecs_PerformWork(Object sender, PerformWorkEventArgs2 e) in D:\\2005 Projects\\....\\ResortMain.aspx.cs:line 193
   at SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSource2.OnPerformWork(PerformWorkEventArgs2 eventArgs)
   at SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSourceView2.ExecuteInsert(IDictionary values)
   at System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback)

Template: SqlServerSpecific.Net.2.0 Adapter ( Covers SQL 7, 2000 and 2005 ) Database : SQL 2000

Senario: I have a datagrid with an LLBLGenProDatasource2 control set up with a resort typelist and indexed with a resortId ( the type list is the resort entity with the country id converted to a name via the country table). A change to the selected row on the details page causes a page index change on the details view control which is tied to a different datasource configure for resortentity collection. The detailsview is configured for edit normally. A button below the details view switches it to insert mode. The problem comes when I save the inserted record, I get the exception thrown.

"Line 1: Incorrect syntax near '='."

I would be grateful if someone give clues as to what has gone wrong here?

Here's the SQL take from a trace on the database

declare @P1 int
set @P1=NULL
exec sp_executesql N'INSERT INTO [ttCaptureV4].[dbo].[Resort] ([resort_name], [iff_village_id], [iff_village_name], [country_id])  VALUES 
(@ResortName, @IffVillageId, @IffVillageName, @CountryId);SELECT @ResortId=', N'@ResortId int output,@ResortName varchar(50),@IffVillageId 
varchar(50),@IffVillageName varchar(50),@CountryId int', @ResortId = @P1 output, @ResortName = 'Test', @IffVillageId = 'IFFID_02', 
@IffVillageName = 'IFFTest', @CountryId = 108
select @P1

Here is a snippet of the code used to service the insert

        protected void btnNew_Click(object sender, EventArgs e)
        {
            dvResortDetails.ChangeMode(DetailsViewMode.Insert);
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            dvResortDetails.InsertItem(true);
        }

        protected void _resortRecs_PerformSelect(object sender, PerformSelectEventArgs2 e)
        {
            using (DataAccessAdapter da = new DataAccessAdapter())
            {
                da.FetchEntityCollection(e.ContainedCollection, e.Filter, 0, e.Sorter,e.PrefetchPath);
            }
        }

        protected void _resortRecs_PerformGetDbCount(object sender, PerformGetDbCountEventArgs2 e)
        {
            using (DataAccessAdapter da = new DataAccessAdapter())
            {
                da.GetDbCount(e.ContainedCollection, e.Filter);
            }
        }

        protected void _resortRecs_PerformWork(object sender, PerformWorkEventArgs2 e)
        {
            using (DataAccessAdapter da = new DataAccessAdapter())
            {
                foreach (UnitOfWorkElement2 item in e.Uow.GetEntityElementsToInsert())
                {
                    if (item.Entity.LLBLGenProEntityTypeValue == (int)EntityType.ResortEntity)
                    {
                        ResortEntity theResort = item.Entity as ResortEntity;
                        DropDownList ddlCountries = (DropDownList) dvResortDetails.FindControl("ddlCountries");
                        if (ddlCountries != null)
                        {
                            theResort.CountryId = Convert.ToInt32(ddlCountries.SelectedValue);
                        }
                    }
                } 
                e.Uow.Commit(da, true); // <-- Exception throw here. ***********
                _resortDS.Refetch = true;
                dgvResorts.DataBind();
            }
        }

Here's the code for the page.

<%@ Page Language="C#" CodeBehind="ResortMain.aspx.cs" Inherits="CaptureV4.WkpgB.ResortMain" %>
<%@ Register Assembly="SD.LLBLGen.Pro.ORMSupportClasses.NET20" Namespace="SD.LLBLGen.Pro.ORMSupportClasses"
    TagPrefix="llblgenpro" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>ResortMain</title>
</head>
<body>
    <form id="form1" runat="server">
        <div style="float:right;width:48%;height:10%">
            <asp:Panel ID="panel5" runat="server" Width="100%" Height="100%">
                <asp:TextBox ID="tbxSearchText" runat="server"></asp:TextBox>&nbsp;
                <asp:Button ID="btnSearch" runat="server" Font-Names="Verdana" Text="Search" OnClick="btnSearch_Click"/>
                <asp:LinkButton ID="lbnAdvancedSearch" runat="server" Width="65px">Advanced Search</asp:LinkButton><br />
                </asp:Panel>
            </div>
            <div style="float:left;width:48%;height:10%; text-align:left; vertical-align:middle">
            <asp:Panel ID="panel4" runat="server" Width="100%" Height="100%" HorizontalAlign="Left">
                <asp:Label ID="lblTitle" runat="server" Text="Resorts" Font-Names="Verdana" Font-Size="X-Large" Height="20%" ></asp:Label><br />
            </asp:Panel>
        </div>
        <div style="float:left;width:48%;height:80%">
            <asp:GridView ID="dgvResorts" runat="server" Font-Names="Verdana" AllowPaging="True" AutoGenerateColumns="False" DataSourceID="_resortDS" CellPadding="4" ForeColor="#333333" GridLines="None"  Font-Size="Small" PageSize="15" Width="100%" AutoGenerateSelectButton="True" OnSelectedIndexChanged="dgvResorts_SelectedIndexChanged" OnPageIndexChanged="dgvResorts_PageIndexChanged" DataMember="DefaultView">
                <Columns>
                    <asp:BoundField DataField="ResortId" HeaderText="ResortId" HtmlEncode="False" InsertVisible="False"
                        SortExpression="ResortId" Visible="False" />
                    <asp:BoundField DataField="ResortName" HeaderText="ResortName" SortExpression="ResortName" />
                    <asp:BoundField DataField="CountryName" HeaderText="CountryName" SortExpression="CountryName" />
                </Columns>
                <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
            <llblgenpro:LLBLGenProDataSource2 ID="_resortDS"
                runat="server" AdapterTypeName="CaptureV4.Data.DatabaseSpecific.DataAccessAdapter, CaptureV4.DataDBSpecific" DataContainerType="TypedList" TypedListTypeName="CaptureV4.Data.TypedListClasses.ResortTypeListTypedList, CaptureV4.Data" EnablePaging="True">
            </llblgenpro:LLBLGenProDataSource2>
        </div>
        <div style="float:right;width:48%;height:80%">
            <asp:Panel ID="DetailsPanel" runat="server" Width="390px">
                <asp:Label ID="lblResortDetails" runat="server" Font-Names="Verdana" Font-Size="Large"
                    Text="Resort Details"></asp:Label><br />
                <asp:DetailsView ID="dvResortDetails" runat="server" Width="100%" Font-Names="Verdana" Font-Size="Small" EmptyDataText="?" DefaultMode="Edit"  AutoGenerateRows="False"   DataSourceID="_resortRecs" OnDataBound="dvResortDetails_DataBound" DataKeyNames="ResortId">
                    <Fields>
                        <asp:BoundField DataField="ResortId" Visible="false" HeaderText="ResortId" SortExpression="ResortId" />
                        <asp:BoundField DataField="ResortName" HeaderText="ResortName" SortExpression="ResortName" />
                        <asp:TemplateField HeaderText="CountryName" SortExpression="CountryName">
                            <ItemTemplate>
                                <asp:DropDownList ID="ddlCountries" runat="server" DataSourceID="_countryDS" DataTextField="CountryName"
                                    DataValueField="CountryId" >
                                </asp:DropDownList>                                                     
                            </ItemTemplate>             
                        </asp:TemplateField>
                        <asp:BoundField DataField="IffVillageName" HeaderText="IffVillageName" SortExpression="IffVillageName" />
                        <asp:BoundField DataField="IffVillageId" HeaderText="IffVillageId" SortExpression="IffVillageId" />
                     </Fields>
                    <EditRowStyle BackColor="#C0C0FF" />
                </asp:DetailsView>
                <asp:Button ID="btnDelete" runat="server" Text="Delete" />
                <asp:Button ID="btnNew" runat="server" Text="New" OnClick="btnNew_Click"  />&nbsp;
                <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click"  />
                <br />
                <asp:Label ID="lblDataError" runat="server" Text="Status:"></asp:Label><br />
                <asp:Label ID="lblGateways" runat="server" Text="Associated Gateways" Font-Names="Verdana" Font-Size="Small"></asp:Label><br />
                &nbsp;
                <asp:ListBox ID="lbxGateways" runat="server" Width="100%"></asp:ListBox>
            </asp:Panel>
            <llblgenpro:LLBLGenProDataSource2 ID="_countryDS" runat="server" AdapterTypeName="CaptureV4.Data.DatabaseSpecific.DataAccessAdapter, CaptureV4.DataDBSpecific"
                DataContainerType="EntityCollection" EntityFactoryTypeName="CaptureV4.Data.FactoryClasses.CountryEntityFactory, CaptureV4.Data">
            </llblgenpro:LLBLGenProDataSource2> &nbsp;&nbsp;
            <llblgenpro:LLBLGenProDataSource2 ID="_resortRecs" runat="server" AdapterTypeName="CaptureV4.Data.DatabaseSpecific.DataAccessAdapter, CaptureV4.DataDBSpecific"
                DataContainerType="EntityCollection" EntityFactoryTypeName="CaptureV4.Data.FactoryClasses.ResortEntityFactory, CaptureV4.Data" LivePersistence="False" OnPerformGetDbCount="_resortRecs_PerformGetDbCount" OnPerformSelect="_resortRecs_PerformSelect" OnPerformWork="_resortRecs_PerformWork">
            </llblgenpro:LLBLGenProDataSource2>
        </div>
    </form>
</body>
</html>

Hi, I think your issue is similar to this one: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7530

Confirm whether a SEQUENSE is set in the designer to ResortId field.

Hi, Thanks for the tip. The resortId related to a table rather than a view but the problem was related to the sequence. I had checked the tickbox for identity without changing the corresponding table column and had not set the sequence (should there be a default setting or at least a warning that it needs to be set?). Inserts now work ok. Thanks again.

Michael