Link to home
Create AccountLog in
Avatar of R8VI
R8VI

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
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

the way I do this kind of issues, is to create stored procedures inside database, and in ASP aplication for each dropdown I have associated a objectdatasource. each objectdatasource will call a function that return me the value and name to each dropdown, and I can ink automatically the foreign key of the second dropdown to the first control. if you have doubts tell me!
Cheers!
Avatar of Aneesh
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
Avatar of R8VI
R8VI

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
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?
Avatar of R8VI

ASKER

ummm still confused sorry is there like a tut i can follow or work

Thanks
give me your table. scripts ana some rows
Avatar of R8VI

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
How many rows has this table?
Are you thinking to normalize this table?
Cheers!
Avatar of R8VI

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
ok... could you copy your data to an excel file and send me for my mail? I will try to help you.
Cheers
Avatar of R8VI

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
Avatar of R8VI

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
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.

<%@ 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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of R8VI

ASKER

Thank you very much it works a treat

Thanks,

R8VI