How to reset the SelectCommand of a SqlDataSource on the client(JavaScript)?

  • Thread starter Thread starter Radu
  • Start date Start date
R

Radu

I have a table A, filled by a program, with the columns ProductID and
ProductColor and with some other columns. Each product can have 1..N
records, with the same or different colors. Let's say that the
manufacturer produced 10.000 records in this table, like

ProductID ProductColor
111 Green
111 Red
222 Red
222 White
222 Blue
..........................................................

I receive a subset of these products, in an undetermined order. So in
another table, B, I want to write some records, as the products come
in, like this:

ProductID ProductColor SomeOtherData
111 Green A1
222 Red A2
222 Blue A3
..........................................................

I need to create a website which would allow people to add records to
the table B.

So in the first column in the gridview I have a textbox where I enter
the ProductID I want to add to table B, say 222. Then, in the second
column, I have a combobox listing all the possible ProductColor
values. However, on each row I want to restrict the values in this
combo only to the ProductColors that exist for the ProductID just
entered, so for 222 I only want to see in the combo the values Red,
White, and Blue. I don't want to see the color Green, since product
222 has never been created in Green.

So the user enters the ProductID in a textbox in the header of a
gridview (the items in the gridview would show the existing records in
table B, and the header row is used to add new rows - editing of rows
is not allowed).

On successful enter of the ProductID I need to filter the dataset
showing in the ProductColor dropdown. Postbacks cannot work - this is
supposed to be a data-entry form, and after 10 such records flashing
on the screen, the user would throw the computer off the window. So,
this is what I have so far (simplified):
______________________________________________________________________________________________

First, a function which would populate an invisible label,
lblCurrentProductID, with the value just entered by the user in the
textbox ProductIDTextBoxAdd:

function ProductColor_SetSelectQuery(strProductID) {
document.getElementById
('lblCurrentProductID').innerText=strProductID;
}

and the invisible label which would hold that value:
<asp:Label ID="lblCurrentProductID" Text="" runat="Server"></
asp:Label>

The html:

<asp:GridView
ID="GridView_Data"
DataSourceID="SqlDataSource_TableB"
AutoGenerateColumns="False"
DataKeyNames="ID"
Runat="server"
<Columns>

<asp:TemplateField HeaderText="Employee ProductID">
<ItemTemplate>
<asp:Label ID="ProductIDLabel" Runat="Server"><%#Eval
("ProductID")%></asp:Label>
</ItemTemplate>
<HeaderTemplate>
<asp:TextBox
ID="ProductIDTextBoxAdd"
onChange="javascript:ProductColor_SetSelectQuery
(this.value)"
Runat="Server">
</asp:TextBox>
</HeaderTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="ProductColor">
<ItemTemplate>
<asp:Label ID="ProductColorLabel_Item" Runat="Server"><%#Eval
("ProductColor")%></asp:Label>
</ItemTemplate>
<HeaderTemplate>
<asp:DropDownList
ID="ProductColorComboBoxAdd"
AppendDataBoundItems="True"
AutoPostBack="false"
DataSourceID="SqlDataSource_ProductColor"
DataTextField="ProductColor"
DataValueField="ProductColor"
Runat="server">
</asp:DropDownList>
</HeaderTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>


Then the datasource for the gridview:
<asp:SqlDataSource
......................
</asp:SqlDataSource>

...... and the datasource for the dropdown
<asp:SqlDataSource
ID="SqlDataSource_ProductColor"
EnableCaching="True"
CacheDuration="60"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT DISTINCT ProductID, ProductColor FROM [Table
A]"
FilterExpression="ProductID = {0}"
Runat="server"<FilterParameters>
<asp:ControlParameter ControlID="lblCurrentProductID"
PropertyName="Text" />
</FilterParameters>
</asp:SqlDataSource>
______________________________________________________________________________________________

So by entering a ProductID in ProductIDTextBoxAdd, the value of
ProductID is copied (via lblCurrentProductID) into the
FilterParameters collection of "SqlDataSource_ProductColor". However,
the contents of the dropdown does not change. I would like to force
the sqlDataSource to somehow take my param into consideration and re-
render itself without a post. How could I do something like this ? Am
I even on the right track ? Is there any solution to this ? I guess
that it must be, because I've seen this on a few pages....

Thank you very much for your patience reading this long post.
Alex.
 
Back
Top