How to implement a cascading lookup on a dataview new form connected to an external database

Question

I have a requirement to develop a new entry form for a database that drives a reporting services report. I set up SSO and created a new item dataview webpart with a table on the database as the datasource. Some of the values for the table are populated from sharepoint lists, with one being dependent on choices from another. I have tried a couple of different methods including using a inside the spdatasource. I have also tried to write some code behind (the page lives in an area that allows server-side code) calling the Page_Load event and that seems to work outside the context of the dataview webpart.

I was getting compiler errors whenever I tried to reference the controls in the DVWP, but now moved the script call to an event called from the second dropdown and the code compiles but doesn't do anything.

Here is the code:

protected void GrabData(Object sender, EventArgs e)
{           
  DropDownList master = (DropDownList) this.Parent.FindControl("DVDropDownList1");
  DropDownList detail = (DropDownList) this.Parent.FindControl("ASPDropDownList1");

   if(Page.IsPostBack){
    SPSecurity.RunWithElevatedPrivileges(delegate{

         String TOselectedValue = master.SelectedValue;
     SPWeb web = SPContext.Current.Site.OpenWeb("/subsite");
         SPQuery tasksQuery = new SPQuery();
         tasksQuery.Query = "<Where><Eq><FieldRef Name='Master Lookup' /><Value Type='Brandysoft.SharePoint.LookupPro.CascadedLookup'>"+TOselectedValue+"</Value></Eq></Where>";
                DataSet taskSet = new DataSet();
                DataTable taskTable = web.Lists["LookupList"].GetItems(tasksQuery).GetDataTable();
                taskTable.TableName = "FilteredItems";
                taskSet.Tables.Add(taskTable);

                detail.DataSource = taskSet.Tables["FilteredItem"];
                detail.DataTextField = "Title";
                detail.DataValueField = "ItemID";
                detail.DataBind();
            });
        } 
    }

And the call:
asp:DropDownList runat="server" id="ASPDropDownList1" __designer:bind="{ddwrt:DataBind('i','ASPDropDownList','SelectedValue','OnSelectedIndexChanged','',ddwrt:EscapeDelims(string('')),'@DBField')}" OnPreRender="GrabData"

Is there a particular event I should use this on, or a way to reference the controls from the Page_Load event, or a better aproach?

Thanks

Answer 1

If you could implement the drop-down boxes in client side code (which also might be a nicer user experience) you should have a look at cascaded drop-downs from SPServices on codeplex.