How do you get SqlDataSource to requery?

David_0223 · May 21st, 2008, 03:41 PM

Me again, still trying to get this web control to work.

I have a nice DropDownList that holds the key names and a FormView that displays the data.

The DropDownList DataSource is a SQLDataSource and it appears to work fine.

The FormView has a different SQLDataSource which uses a ControlParameter defined as the Text value from my DropDownList.

When the webpage opens, the Form displays the data from the first item on the DropDownList.

When I select a different item from the DropDownList the Form displays the EmptyDataTemplate. If I re-select the original item from the DropDownList, the EmptyDataTemplate is displayed again.

I added a handler for the SelectedIndexChanged event of the DropDownList to try to see what was hapening and I can verify that the newly selected item is, in fact, being posted back to the server.

It seems that the SqlDataSource is not re-querying after it gets the new key value from the parameter. Or the parameter never gets the new key value from the DropDownList.

Any ideas on how to make this work?

What you don't know can hurt you!

samjudson · May 22nd, 2008, 04:25 AM

Have you tried SqlDataSource.DataBind()?

/- Sam Judson : Wrox Technical Editor -/

David_0223 · May 22nd, 2008, 02:59 PM

Hi Sam,

I had not tried SqlDataSource.DataBind(). But I have now and it doesn't work. I know that it is firing because I can see it on the SelectedIndexChanged event.

Before I had tried FormView.DataBind(). That didn't work either. On a whim I tried populating the DefaultValue of the SelectParameter in the dropdownlist's SelectedIndexChanged event handler. That also did not work.

What you don't know can hurt you!

samjudson · May 22nd, 2008, 04:36 PM

Ah, I think what you actually want is to use the SelectedValue of the DropDownList, not the Text value.

Code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
    SelectCommand="SELECT [ID], [Name] FROM [Table1] WHERE ([Type] = @Type)">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" Name="Type" 
            PropertyName="SelectedValue" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

HTH.

/- Sam Judson : Wrox Technical Editor -/

David_0223 · May 22nd, 2008, 05:49 PM

Sam, I have tried it both ways. Both "Text" and "SelectedValue" post back the selected value, but neither populates the parameter. Even if I programatically populate the DefaultValue of the Parameter at run time, the data source fails to return any records.

What you don't know can hurt you!

samjudson · May 23rd, 2008, 02:00 AM

My test form works fine I'm afraid - here is the drop down list to go with the above SqlDataSource:

Code:

        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true">
            <asp:ListItem>A</asp:ListItem>
            <asp:ListItem>B</asp:ListItem>
        </asp:DropDownList>

There is no code behind - it all just works automatically.

Can you possibly post some of the code you have?

/- Sam Judson : Wrox Technical Editor -/

David_0223 · May 26th, 2008, 11:49 AM

Hey Sam,

Here's the code from the child page:

Code:

<%@ Page Language="C#" MasterPageFile="~/CEMMaster.master" AutoEventWireup="true" CodeFile="CEMDevMgmnt.aspx.cs" Inherits="CEMDevMgmnt" Title="Untitled Page" %>
<%@ MasterType VirtualPath="~/CEMMaster.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
  <asp:DropDownList ID="cboDevices" runat="server" AutoPostBack="True" DataSourceID="CEMDevices"
    DataTextField="ANALYZER_NAME" DataValueField="ANALYZER_NAME">
  </asp:DropDownList><br />
  <br />
  <asp:FormView ID="FormView1" runat="server" DefaultMode="ReadOnly" DataKeyNames="ANALYZER_NAME" DataSourceID="CEMDevData">
...
    <ItemTemplate>
      <table style="width:450px">
        <tr>
          <td><b>Device Name:</b></td><td>
            <asp:Label ID="ANALYZER_NAMELabel" runat="server" Text='<%# Bind("ANALYZER_NAME") %>'></asp:Label>
          </td>
          <td><b>Sort Order:</b></td><td>
            <asp:Label ID="ORDER_NUMLabel" runat="server" Text='<%# Bind("ORDER_NUM") %>'></asp:Label>
          </td>
        </tr>
        <tr>
          <td><b>Description:</b></td><td colspan="3">
            <asp:Label ID="DESCRIPTORLabel" runat="server" Text='<%# Bind("DESCRIPTOR") %>'></asp:Label>
          </td>
        </tr>
...
  <asp:SqlDataSource ID="CEMDevices" runat="server" ConnectionString="<%$ ConnectionStrings:TOL_CEM_Connect %>"
    SelectCommand="SELECT DISTINCT [ANALYZER_NAME] FROM [appToledoCEM].[ANALYZERS]">
  </asp:SqlDataSource>
  <asp:SqlDataSource ID="CEMDevData" runat="server" ConnectionString="<%$ ConnectionStrings:TOL_CEM_Connect %>">
    <SelectParameters>
      <asp:ControlParameter ControlID="cboDevices" Name="ANALYZER_NAME2" PropertyName="SelectedValue"
        Type="String" />
    </SelectParameters>
    <InsertParameters>
      <asp:ControlParameter ControlID="ANALYZER_NAMETextBox" Name="AnalyzerName" Type="string" PropertyName="Text" />
      <asp:ControlParameter ControlID="DESCRIPTORTextBox" Name="Descriptor" Type="string" PropertyName="Text" />
      <asp:ControlParameter ControlID="LOCATIONTextBox" Name="Location" Type="string" PropertyName="Text" />
      <asp:ControlParameter ControlID="ORDER_NUMTextBox" Name="OrderNum" Type="Int32" PropertyName="Text" />
      <asp:ControlParameter ControlID="TS_ADDTextBox" Name="AddUse" Type="DateTime" PropertyName="Text" />
      <asp:ControlParameter ControlID="TS_REMOVETextBox" Name="RemUse" Type="DateTime" PropertyName="Text" />
      <asp:SessionParameter Name="Adder" SessionField="UserID" Type="Int32" />
    </InsertParameters>
  </asp:SqlDataSource>
</asp:Content>

The code behind:

Code:

public partial class CEMDevMgmnt : System.Web.UI.Page
{
...
  void cboDevices_SelectedIndexChanged(object sender, EventArgs e)
  {
    DropDownList ddl = (DropDownList)sender;
    Master.WriteLog(string.Format("Selected Device {0}", ddl.Text));

    Parameter pm = (Parameter)CEMDevData.SelectParameters["ANALYZER_NAME2"];
    Master.WriteLog(string.Format("{0} = {1}", pm.Name, pm.DefaultValue));

    CEMDevData.SelectParameters["ANALYZER_NAME2"].DefaultValue = ddl.Text.Trim();
    this.CEMDevData.DataBind();
    FormView1.DataBind();
  }
...

The log file shows the correct selected device! but FormView1 indicates no records returned. Bear in mind that FormView1 does show data when it opens for the initially selected device. So I know the binding to FormView1's datasource works. But when I select a different device with the dropdownlist I get no rows returned! If I re-select the initially selected device, I get no rows returned.

What you don't know can hurt you!