asked on
DropDownList Linked to sql database
Hi I have three drop down lists in my webpage. called following
Country, County, and category.
What i want to do is link them to a backend database that i have created but when i pick a Country the county drop down list should only show me the counties in that country and then when i pick a county it should only show me the categories in that county.
So for example pick UK (in Country) then shows uk counties (in drop down list County) and then pick county which shows all the catergories (in Catergories drop down list)
Any tutorals or any examples i can follow
Any help is much appreciated
thank you
R8VI
Country, County, and category.
What i want to do is link them to a backend database that i have created but when i pick a Country the county drop down list should only show me the counties in that country and then when i pick a county it should only show me the categories in that county.
So for example pick UK (in Country) then shows uk counties (in drop down list County) and then pick county which shows all the catergories (in Catergories drop down list)
Any tutorals or any examples i can follow
Any help is much appreciated
thank you
R8VI
.NET ProgrammingASP.NETMicrosoft SQL Server 2005
Last Comment
R8VI
you can use 3 separate sp calls
When the page loads for the first time , call an sp named ListCountries which simply returns the countryId and Countries
CREATE Proc ListCountries
As
SET NOCOUNT ON
SELECT CountryID, CountryName
FRO Country
GO
When the user selects a Country, use another sp call to list the states
CREATE PROC ListStates
@CountryID int
AS
SELECT CountyID, County
FROM Counties
WHERE CountryID = @CountryID
Similarly create an sp for the 3rd combo
When the page loads for the first time , call an sp named ListCountries which simply returns the countryId and Countries
CREATE Proc ListCountries
As
SET NOCOUNT ON
SELECT CountryID, CountryName
FRO Country
GO
When the user selects a Country, use another sp call to list the states
CREATE PROC ListStates
@CountryID int
AS
SELECT CountyID, County
FROM Counties
WHERE CountryID = @CountryID
Similarly create an sp for the 3rd combo
ASKER
As i am a beginner,
does this mean that i will have to have three dfferent table because at the moment i have one table with all three fields in it.
And once i create the stored prod how do i link it to the combo box ?
Any help is much appreciated again
Thanks,
R8VI
does this mean that i will have to have three dfferent table because at the moment i have one table with all three fields in it.
And once i create the stored prod how do i link it to the combo box ?
Any help is much appreciated again
Thanks,
R8VI
you can create the stored procedure based in one table. The difference is in the data returned and in the parameters. The second has a foreign key
helped?
helped?
ASKER
ummm still confused sorry is there like a tut i can follow or work
Thanks
Thanks
give me your table. scripts ana some rows
ASKER
ok my table is like so
Column Name Data Type Allow Nulls
ID smallint Unchcecked
Country nvarchar(50) Checked
County nvarchar(50) Checked
Category nvarchar(50) Checked
Name nvarchar(50) Checked
Address nvarchar(50) Checked
Tel_No nvarchar(50) Checked
Email nvarchar(50) Checked
Website nvarchar(100) Checked
Description nvarchar(MAX) Checked
dropdown1 id = Country
dropdown2 id = County
dropdown3 id = Category
hope that helps
Thanks
r8VI
Column Name Data Type Allow Nulls
ID smallint Unchcecked
Country nvarchar(50) Checked
County nvarchar(50) Checked
Category nvarchar(50) Checked
Name nvarchar(50) Checked
Address nvarchar(50) Checked
Tel_No nvarchar(50) Checked
Email nvarchar(50) Checked
Website nvarchar(100) Checked
Description nvarchar(MAX) Checked
dropdown1 id = Country
dropdown2 id = County
dropdown3 id = Category
hope that helps
Thanks
r8VI
How many rows has this table?
Are you thinking to normalize this table?
Cheers!
Are you thinking to normalize this table?
Cheers!
ASKER
At the moment not many just 4 country uk scotland ireland and wales
not many counties yet
but that about it
normalize not at the moment to be honest i am just testing things
Thanks for all help
not many counties yet
but that about it
normalize not at the moment to be honest i am just testing things
Thanks for all help
ok... could you copy your data to an excel file and send me for my mail? I will try to help you.
Cheers
Cheers
ASKER
yeh sure may i have ur mail address then
EMAIL ADDRESS REMOVED
Please do not use email addresses as part of the Q&A function.
We have the "File Attachment" function available to all members.
Vee_Mod
Experts Exchange Moderator
Please do not use email addresses as part of the Q&A function.
We have the "File Attachment" function available to all members.
Vee_Mod
Experts Exchange Moderator
ASKER
I have emailed you thanks my email is EMAIL ADDRESS REMOVED
Please do not use email addresses as part of the Q&A function.
We have the "File Attachment" function available to all members.
Vee_Mod
Experts Exchange Moderator
Please do not use email addresses as part of the Q&A function.
We have the "File Attachment" function available to all members.
Vee_Mod
Experts Exchange Moderator
one other option which is a bit basic but should work it to use the SQLDatSource for your drop downs, but link to hidden labels on your form. When the first dropdown is updated the postback you should update the label in your code behind. The page will then reload the drop down lists using these values. I have a page that does this and it's simple to setup.
The key to making this work is to set the AutoPostBack on the first Popup which launches the code when the index changes.
The key to making this work is to set the AutoPostBack on the first Popup which launches the code when the index changes.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="popuptest.aspx.vb" Inherits="popuptest" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label" Visible="False"></asp:Label>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="FarmName" DataValueField="FarmName">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebGIS.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT [FarmName] FROM [WebGISJobList] ORDER BY [FarmName]">
</asp:SqlDataSource>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="FieldName" DataValueField="FieldName">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebGIS.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT [FieldName] FROM [WebGISJobList] WHERE ([FarmName] = @FarmName)">
<SelectParameters>
<asp:ControlParameter ControlID="Label1" Name="FarmName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
In code Behind:
Partial Class popuptest
Inherits System.Web.UI.Page
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Label1.Text = DropDownList1.Text
End Sub
End Class
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you very much it works a treat
Thanks,
R8VI
Thanks,
R8VI
Cheers!