I have two dropdowns in aspx page one for location and vendors. Based on the value selected in the location dropdown, The vendors dropdown must populate...I am trying to bind with location name Location:\[code\] <asp
ropDownList ID="ddlAllLocations" runat="server" DataSourceID="SqlDataSourceBusinessLocations" DataTextField="Location_Name" DataValueField="Location_ID" AutoPostBack="True" AppendDataBoundItems="True"> <asp:ListItem value="" selected="True">\[/code\]Vendors :\[code\]<asp:SqlDataSource ID="SqlDataSourceAllVendors" runat="server" ConnectionString="<%$ ConnectionStrings:xxxxx %>" ProviderName="<%$ ConnectionStrings:xxxxx.ProviderName %>" SelectCommand="GetAllVendorsForBUforLocation" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="userBUIds" SessionField="BusinessUnitIds" Size="200" Type="String" /> <asp:ControlParameter ControlID="ddlAllLocations" Name="LOCATION_ID" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource>\[/code\]My stored procedure is \[code\]ALTER PROCEDURE [dbo].[GetAllVendorsForBUforLocation] @userBUIds varchar(200), @LOCATION_ID varcharASDECLARE @sql NVARCHAR(4000) BEGIN set @sql=' + + broken onto multi-lines for clarity + + SELECT DISTINCT tblVendor_Payees.PayeeID, RTRIM(ISNULL(a.Name1_Last, '''')) + '' '' + ISNULL(a.Name1_First, '''') AS VendorName, tblVendor_Business.BusinessID FROM tblVendor_Payees AS a left JOIN tblFields AS f ON a.PayeeID = f.VendorID INNER JOIN tblVendor_Business ON a.PayeeID = tblVendor_Business.PayeeID INNER JOIN INVENTORY.TBL_LOCATION on INVENTORY.TBL_LOCATION.BusinessID = tblVendor_Business.BusinessID WHERE (a.VendorType = 1) AND (tblVendor_Business.BusinessID = '+@userBUIds+' and INVENTORY.TBL_LOCATION.LOCATION_ID = '+cast(@LOCATION_ID as int)+') ORDER BY VendorName' exec sp_executeSQL @sql \[/code\]
ropDownList ID="ddlAllLocations" runat="server" DataSourceID="SqlDataSourceBusinessLocations" DataTextField="Location_Name" DataValueField="Location_ID" AutoPostBack="True" AppendDataBoundItems="True"> <asp:ListItem value="" selected="True">\[/code\]Vendors :\[code\]<asp:SqlDataSource ID="SqlDataSourceAllVendors" runat="server" ConnectionString="<%$ ConnectionStrings:xxxxx %>" ProviderName="<%$ ConnectionStrings:xxxxx.ProviderName %>" SelectCommand="GetAllVendorsForBUforLocation" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="userBUIds" SessionField="BusinessUnitIds" Size="200" Type="String" /> <asp:ControlParameter ControlID="ddlAllLocations" Name="LOCATION_ID" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource>\[/code\]My stored procedure is \[code\]ALTER PROCEDURE [dbo].[GetAllVendorsForBUforLocation] @userBUIds varchar(200), @LOCATION_ID varcharASDECLARE @sql NVARCHAR(4000) BEGIN set @sql=' + + broken onto multi-lines for clarity + + SELECT DISTINCT tblVendor_Payees.PayeeID, RTRIM(ISNULL(a.Name1_Last, '''')) + '' '' + ISNULL(a.Name1_First, '''') AS VendorName, tblVendor_Business.BusinessID FROM tblVendor_Payees AS a left JOIN tblFields AS f ON a.PayeeID = f.VendorID INNER JOIN tblVendor_Business ON a.PayeeID = tblVendor_Business.PayeeID INNER JOIN INVENTORY.TBL_LOCATION on INVENTORY.TBL_LOCATION.BusinessID = tblVendor_Business.BusinessID WHERE (a.VendorType = 1) AND (tblVendor_Business.BusinessID = '+@userBUIds+' and INVENTORY.TBL_LOCATION.LOCATION_ID = '+cast(@LOCATION_ID as int)+') ORDER BY VendorName' exec sp_executeSQL @sql \[/code\]