[Solved] OpenAccessDataSource Foreignkeys

[JOHN STRUCHYNSKI]

Hello,

I have run into a problem when using OpenAccessDataSource inserting/updating foreignkeys.
I am using a formview with an OpenAccessDataSource to insert and edit my data. The entity that I am editing is called Employee.
Employee has one foreignkey realtion to another entity called EmployeeGroup. I want to use RadComboBox in the formview to tie an employee to an employeegroup.

If using a SQLDataSource control you can simply do the following SelectedValue='<%# Bind("EmployeeGroupID") %>'. I have tried using the SelectedValue='<%# Bind("EmployeeGroup.EmployeeGroupID") %>' when using the an OpenAccessDataSource and I get an exception. The exception says:
{"Concurrent update detection failed because property 'EmployeeGroup.EmployeeGroupID' is not an accessible on type 'IntranetDataAccess.Employee'."}




Is there a way to handle this?

Any help is greatly appreciated

Here is my full code

    <asp:FormView ID="FormView1" runat="server" DataKeyNames="EmployeeID"
        DataSourceID="OpenAccessDataSourceEmployee"
        oniteminserted="FormView1_ItemInserted"
        onitemupdated="FormView1_ItemUpdated">
        <EditItemTemplate>
            <table class="input_form_table" width="800px;">
                <tr>
                    <td colspan="4"><div class="heading">Basic Information</div></td>
                </tr>
                <tr>
                    <td style="width:150px">First Name: <span class="red">*</span></td>
                    <td style="width:250px"><asp:TextBox ID="TextBoxFirstName" runat="server" Text='<%# Bind("FirstName") %>' MaxLength="100" Width="200px" /></td>
                    <td style="width:150px">Last Name: <span class="red">*</span></td>
                    <td style="width:250px"><asp:TextBox ID="TextboxLastName" runat="server" Text='<%# Bind("LastName") %>' MaxLength="100" Width="200px" /></td>
                </tr>
                <tr>
                    <td>Phone: <span class="red">*</span></td>
                    <td><telerik:RadMaskedTextBox ID="RadMaskedTextBoxPhone" runat="server" Text='<%# Bind("Phone") %>' Mask="###-###-####" MaxLength="100" Width="200px" /></td>
                    <td>Email: <span class="red">*</span></td>
                    <td><asp:TextBox ID="TextBoxEmail" runat="server" Text='<%# Bind("Email") %>' MaxLength="100" Width="200px" /></td>
                </tr>
                <tr>
                    <td colspan="4"><div class="heading">Additional Information</div></td>
                </tr>
                <tr>
                    <td>Duties/Title: <span class="red">*</span></td>
                    <td colspan="3"><asp:TextBox ID="TextBoxDuties" runat="server" Text='<%# Bind("Duties") %>' MaxLength="200" Width="600px" /></td>
                </tr>
                <tr>
                    <td>Employee Group: <span class="red">*</span></td>
                    <td colspan="3">
                        <telerik:RadComboBox ID="RadComboBoxEmployeeGroup" runat="server" DataSourceID="OpenAccessDataSourceEmployeeGroup" SelectedValue='<%# Bind("EmployeeGroup.EmployeeGroupID") %>' DataTextField="Name" DataValueField="EmployeeGroupID"  Width="600px" />
                    </td>
                </tr>
                <tr>
                    <td>Alternate Phone:</td>
                    <td><telerik:RadMaskedTextBox ID="RadMaskedTextBoxAlternatePhone" runat="server" Text='<%# Bind("AlternatePhone") %>' Mask="###-###-####" MaxLength="100" Width="200px" /></td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
            </table>

            <div class="button_container">
                <asp:Button ID="ButtonCancel" runat="server" CommandName="Cancel" Text="Cancel" CausesValidation="false" />
                &nbsp;&nbsp;
                <asp:Button ID="ButtonInsert" runat="server" CommandName="Insert" Text="Insert" Visible='<%# (FormView1.DefaultMode == FormViewMode.Insert) ? true : false %>' />
                &nbsp;&nbsp;
                <asp:Button ID="ButtonUpdate" runat="server" CommandName="Update" Text="Update" Visible='<%# (FormView1.DefaultMode == FormViewMode.Insert) ? false : true %>' />
            </div>

        </EditItemTemplate>
    </asp:FormView>

    <telerik:OpenAccessDataSource ID="OpenAccessDataSourceEmployee" runat="server"
        ContextTypeName="" EnableInsert="True" EnableUpdate="True"
        ObjectContextProvider="Intranet.IntranetScopeProivder, Intranet" OrderBy=""
        TypeName="IntranetDataAccess.Employee" Where="EmployeeID == @EmployeeID"
        oninserting="OpenAccessDataSourceEmployee_Inserting">
        <WhereParameters>
            <asp:QueryStringParameter Name="EmployeeID" QueryStringField="EmployeeID" />
        </WhereParameters>
    </telerik:OpenAccessDataSource>

    <telerik:OpenAccessDataSource ID="OpenAccessDataSourceEmployeeGroup" runat="server"
        ContextTypeName=""
        ObjectContextProvider="Intranet.IntranetScopeProivder, Intranet" OrderBy=""
        TypeName="IntranetDataAccess.EmployeeGroup">
    </telerik:OpenAccessDataSource>

[JOHN STRUCHYNSKI]

Got it working!

After time trial and error I was able to get it to work. If anyone is running into the same problem, you can try the following.

Change your Bind to Eval for the foreignkey

                <tr>
                    <td>Employee Group: <span class="red">*</span></td>
                    <td colspan="3">
                        <telerik:RadComboBox ID="RadComboBoxEmployeeGroup" runat="server" DataSourceID="OpenAccessDataSourceEmployeeGroup" SelectedValue='<%# Eval("EmployeeGroup.EmployeeGroupID") %>' DataTextField="Name" DataValueField="EmployeeGroupID"  Width="600px" />
                    </td>
                </tr>

Then add the following method to your OpenAccessDataSource's OnInserting and OnUpdating events

        protected void OpenAccessDataSourceEmployee_Changing(object sender, Telerik.OpenAccess.OpenAccessDataSourceChangingEventArgs e)
        {
            // Cast entity as current type
            IntranetDataAccess.Employee employee = (IntranetDataAccess.Employee)e.Entity;

            // Define and capture value from object
            RadComboBox RadComboBoxEmployeeGroup = (RadComboBox)FormView1.FindControl("RadComboBoxEmployeeGroup");
            int employeeGroupId = int.Parse(RadComboBoxEmployeeGroup.SelectedValue);

            // Assign realted object to entity
            employee.EmployeeGroup = e.Context.Extent<IntranetDataAccess.EmployeeGroup>().FirstOrDefault(eg => eg.EmployeeGroupID == employeeGroupId);
        }

If there is a better approach that will let you use Bind instead of Eval please let me know.

[Zoran]

Hello JOHN STRUCHYNSKI,

Nice to know you have achieved your goal. I would just like to share another possible workaround where access to the foreign keys is made directly available. Instead of having to access EmployeeGroup.EmployeeGroupID, you will be able to just set EmployeeGroupID as the foreign key from Employee to EmployeeGroup.

You can find the whole workflow described in the following Knowledge-Base article. The advantage of the approach I am suggesting you is that no code-behind is required, neither is handling of any of the OpenAccesDataSource events.

All the best,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.