[Solved] Unknown Ajax Error When Referencing Foreign Key in AutoComplete Combobox

[Bernard]

Dear All,

I have been facing some issues deploying a RadGrid that has a couple of autocomplete RadComboboxes embedded in the edit mode without any resolution for several days now. An ajax related error (Sys.WebForms :Code 0 ) prevents the grid from activating the "INSERT" and "EDIT" mode. I have 2 tables that is referenced by FK_ relations like so:

dbo.Orders

| OrderId | OriginId | DestinationId |

dbo.Locations

| CityId | CityName |


OriginId and DestinationId is actually FK_ referring to CityId

Here is the source code snippets that I use:

ASPX

<telerik:GridTemplateColumn HeaderText="Origin">
                            <ItemTemplate>
                                 <%#DataBinder.Eval(Container.DataItem, "Origin")%>
                            </ItemTemplate>
                            <EditItemTemplate>
                                 <telerik:RadComboBox runat="server" ID="RadComboBox5" DataTextField="Origin" OnItemsRequested="RadComboBox5_ItemsRequested"
                                      EnableLoadOnDemand="true"
                                      DataValueField="OriginId" OnSelectedIndexChanged="RadComboBox5_SelectedIndexChanged" DataSourceID="OriginDataSource"
                                      SelectedValue='<%#Bind("OriginId") %>'>
                                 </telerik:RadComboBox>
                            </EditItemTemplate>
                       </telerik:GridTemplateColumn>

                        <telerik:GridTemplateColumn HeaderText="Destination">
                            <ItemTemplate>
                                 <%#DataBinder.Eval(Container.DataItem, "Destination")%>
                            </ItemTemplate>
                            <EditItemTemplate>
                                 <telerik:RadComboBox runat="server" ID="RadComboBox6" DataTextField="Destination" EnableAutomaticLoadOnDemand="true"
                                     OnItemsRequested="RadComboBox6_ItemsRequested"
                                      DataValueField="DestinationId" OnSelectedIndexChanged="RadComboBox6_SelectedIndexChanged" DataSourceID="ConsigneesDataSource"
                                      SelectedValue='<%#Bind("DestinationId") %>'>
                                 </telerik:RadComboBox>
                            </EditItemTemplate>
                       </telerik:GridTemplateColumn>

C#

protected void RadComboBox5_ItemsRequested(object sender, RadComboBoxItemsRequestedEventArgs e)
        {
            string sql = "SELECT [CityId], [CityName] FROM [Loications] WHERE CityName LIKE @CityName + '%'";
            SqlDataAdapter adapter = new SqlDataAdapter(sql,
                ConfigurationManager.ConnectionStrings["test"].ConnectionString);
            adapter.SelectCommand.Parameters.AddWithValue("@CityName", e.Text);

            DataTable dt = new DataTable();
            adapter.Fill(dt);

            RadComboBox comboBox = (RadComboBox)sender;

            comboBox.Items.Clear();

            foreach (DataRow row in dt.Rows)
            {
                RadComboBoxItem item = new RadComboBoxItem();
                item.Text = row["CityName"].ToString();
                item.Value = row["CityId"].ToString();

                comboBox.Items.Add(item);

                item.DataBind();
            }
        }

        protected void RadComboBox5_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            Session["CityId"] = e.Value;
        }

        protected void RadComboBox6_ItemsRequested(object sender, RadComboBoxItemsRequestedEventArgs e)
        {
            string sql = "SELECT [CityId], [CityName] FROM [Locations] WHERE CityName LIKE @CityName + '%'";
            SqlDataAdapter adapter = new SqlDataAdapter(sql,
                ConfigurationManager.ConnectionStrings["test"].ConnectionString);
            adapter.SelectCommand.Parameters.AddWithValue("@CityName", e.Text);

            DataTable dt = new DataTable();
            adapter.Fill(dt);

            RadComboBox comboBox = (RadComboBox)sender;

            comboBox.Items.Clear();

            foreach (DataRow row in dt.Rows)
            {
                RadComboBoxItem item = new RadComboBoxItem();
                item.Text = row["CityName"].ToString();
                item.Value = row["CityId"].ToString();

                comboBox.Items.Add(item);

                item.DataBind();
            }
        }

        protected void RadComboBox6_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            Session["CityId"] = e.Value;
        }

DataSources String Used:

<asp:SqlDataSource ID="MasterViewDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:test %>"

         SelectCommand="SELECT     Orders.OrderId, a1.CityName AS Origin, a2.CityName AS Destination

FROM         Orders INNER JOIN

                      Locations AS a1 ON a1.CityId = TransactsData.ShiprId INNER JOIN

                      Locations AS a2 ON a2.CityId = TransactsData.ConsId "

                  

                 InsertCommand="INSERT INTO [Orders] ([OriginId], [DestinationId], [TimeCreated])

             

                                    VALUES (@OriginId, @DestinationId, GETDATE())"

                  

                 UpdateCommand="UPDATE   [Orders] SET [OriginId] = @OriginId,
[DestinationId] = @DestinationId, [TimeUpdated] = @GETTIME()">

Radcombobox datasource

<asp:SqlDataSource ID="OriginDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:test %>"
          SelectCommand="SELECT [CityId], [CityName] FROM [Locations]"></asp:SqlDataSource>

    <asp:SqlDataSource ID="DestinationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:test %>"
          SelectCommand="SELECT [CityId], [CityName] FROM [Locations]"></asp:SqlDataSource>

The above code however does work if I erase either Origin or Destination combobox template column and bind the combobox template directly to the locations table. any advise to improve on this matter is much appreciated.

[Bernard]

*** SOLVED ****

Turns out that adding a datasource to load on demand combobox will cause sql ambiguity thus breaking the internal callback process