Dropdownlist Problem

[Glenn L.]

I am using the VS 2005 beta1. I am trying to add a Dropdownlist
control that gets its data from one table and when selected saves the
ID into another table. I have 2 tables TBLSTAFF and TBLRACE. TBLSTAFF
has an integer field called RACEID. TBLRACE has the foreign key RACEID
and RACEDESC(Description field). I set up a dropdown list as follows.


<aspropDownList ID="DropStaff" Runat="server" Height="25px"
Width="203px" DataSourceID="TBLRACE" DataValueField="RACEID"
DataTextField="RACEDESC" >
</aspropDownList>

What I can't seem to figure out is how to bind this control to the
other SQLDataSource called "TBLSTAFF" and its Field "RACEID".

If any one can help me I would sure appreciate it very much. I imagine
this has been done before but I cannot seem to find an example of it.

Thanks,
Glenn

 

[Brock Allen]

You should add a second DataSource control to your page that uses a ControlParameter
for its SelectParameters whose source is from the SelectedItem from the first
table (GridView I presume).

 

[Glenn L.]

I am not sure exactly what you are telling me to do. I already have 2
SQLDataSources. I am trying to use a FormView. Below is the code for
what I have done so far but it doesnt work as of yet I am missing
something.

<asp:FormView ID="FormView1" Runat="server" DataSourceID="TBLSTAFF"
DataKeyNames="STAFFID">
<EditItemTemplate>
SSN:
<asp:TextBox Text='<%# Bind("SSN") %>' Runat="server"
ID="SSNTextBox"></asp:TextBox><br />
FIRSTNAME:
<asp:TextBox Text='<%# Bind("FIRSTNAME") %>'
Runat="server" ID="FIRSTNAMETextBox"></asp:TextBox><br />
LASTNAME:
<asp:TextBox Text='<%# Bind("LASTNAME") %>'
Runat="server" ID="LASTNAMETextBox"></asp:TextBox><br />
MIDDLE:
<asp:TextBox Text='<%# Bind("MIDDLE") %>'
Runat="server" ID="MIDDLETextBox1"></asp:TextBox><br />
<br />
RACEID:
<br />
<aspropDownList ID="DropRace" Runat="server"
Height="25px" Width="203px"
DataSourceID="TBLRACE" DataValueField="RACEID"
DataTextField="RACEDESC" >
</aspropDownList>
SEX:
<asp:TextBox Text='<%# Bind("SEX") %>' Runat="server"
ID="SEXTextBox"></asp:TextBox><br />
STAFFID:
<asp:Label Text='<%# Eval("STAFFID") %>' Runat="server"
ID="STAFFIDLabel1">
</asp:Label><br />
<br />
<asp:LinkButton ID="LinkButton2" Runat="server"
CommandName="update">Save</asp:LinkButton>
</EditItemTemplate>
<ItemTemplate>
SSN:
<asp:Label Text='<%# Bind("SSN") %>' Runat="server"
ID="SSNLabel">
</asp:Label><br />
FIRSTNAME:
<asp:Label Text='<%# Bind("FIRSTNAME") %>'
Runat="server" ID="FIRSTNAMELabel">
</asp:Label><br />
LASTNAME:
<asp:Label Text='<%# Bind("LASTNAME") %>'
Runat="server" ID="LASTNAMELabel">
</asp:Label><br />
MIDDLE:
<asp:Label Text='<%# Bind("MIDDLE") %>' Runat="server"
ID="MIDDLELabel">
</asp:Label><br />
RACEID:
<asp:Label Text='<%# Bind("RACEID") %>' Runat="server"
ID="RACEIDLabel">
</asp:Label><br />
SEX:
<asp:Label Text='<%# Bind("SEX") %>' Runat="server"
ID="SEXLabel">
</asp:Label><br />
STAFFID:
<asp:Label Text='<%# Bind("STAFFID") %>' Runat="server"
ID="STAFFIDLabel">
</asp:Label><br />
<br />
<asp:LinkButton ID="LinkButton3" Runat="server"
CommandName="Edit">Edit</asp:LinkButton>
</ItemTemplate>
</asp:FormView><br />
<asp:SqlDataSource ID="TBLRACE" Runat="server"
SelectCommand="SELECT RACEID, RACEDESC, RACECODE FROM dbo.TBLRACE"
ConnectionString="<%$ ConnectionStrings:SalaryCNN %>">
</asp:SqlDataSource>
<asp:SqlDataSource ID="TBLSTAFF" Runat="server"
ConnectionString="<%$ ConnectionStrings:SalaryCNN %>"
SelectCommand="SELECT [SSN], [FIRSTNAME], [LASTNAME],
[MIDDLE], [RACEID], [SEX], [STAFFID] FROM [TBLSTAFF] WHERE ([STAFFID] =
@STAFFID)"
UpdateCommand="UPDATE [TBLSTAFF] SET [SSN] = @SSN,
[FIRSTNAME] = @FIRSTNAME, [LASTNAME] = @LASTNAME, [MIDDLE] = @MIDDLE,
[RACEID] = @RACEID, [SEX] = @SEX WHERE [STAFFID] = @original_STAFFID"
InsertCommand="INSERT INTO [TBLSTAFF] ([SSN], [FIRSTNAME],
[LASTNAME], [MIDDLE], [RACEID], [SEX]) VALUES (@SSN, @FIRSTNAME,
@LASTNAME, @MIDDLE, @RACEID, @SEX)"
DeleteCommand="DELETE FROM [TBLSTAFF] WHERE [STAFFID] =
@original_STAFFID">
<DeleteParameters>
<asparameter Type="Int32"
Name="STAFFID"></asparameter>
</DeleteParameters>
<UpdateParameters>
<asparameter Type="String"
Name="SSN"></asparameter>
<asparameter Type="String"
Name="FIRSTNAME"></asparameter>
<asparameter Type="String"
Name="LASTNAME"></asparameter>
<asparameter Type="String"
Name="MIDDLE"></asparameter>
<asparameter Type="Int32"
Name="RACEID"></asparameter>
<asparameter Type="Int32" Name="SEX"></asparameter>
<asparameter Type="Int32"
Name="STAFFID"></asparameter>
</UpdateParameters>
<SelectParameters>
<asp:SessionParameter Name="STAFFID" Type="Int32"
SessionField="STAFFID"></asp:SessionParameter>
</SelectParameters>
<InsertParameters>
<asparameter Type="String"
Name="SSN"></asparameter>
<asparameter Type="String"
Name="FIRSTNAME"></asparameter>
<asparameter Type="String"
Name="LASTNAME"></asparameter>
<asparameter Type="String"
Name="MIDDLE"></asparameter>
<asparameter Type="Int32"
Name="RACEID"></asparameter>
<asparameter Type="Int32" Name="SEX"></asparameter>
</InsertParameters>
</asp:SqlDataSource>

 

[Glenn L.]

After futher review I believe I understand. There is still a problem.
I need the ControlParamater for the Update Command not the Select
command. When I try to add one for the dropdown box the FormView is the
only control I can see. If I put in the following code in the Update
parameters it gives an error saying it cant find the control.

<asp:ControlParameter Name="RACEID" Type="Int32" ControlID= "DropRace"
PropertyName="SelectedValue"></asp:ControlParameter>

I am assume that the FormView is the only control in scope, and because
the Dropdownlist is in the FormView the ControlParameter Statement
cannot see it. If this is the case how do I specify the
ControlParameter. But then again I may be going about this the wrong
way. Please advise!

 

[Glenn L.]

After much messing around with the control I found that the following
code binds it ok and seems to work.


<aspropDownList ID="DropRace" Runat="server" Height="25px"
Width="203px" DataSourceID="TBLRACE" DataValueField="RACEID"
DataTextField="RACEDESC"

</aspropDownList>