Click here to Skip to main content
15,891,704 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi,

I have two dropdownlist
1-ddlcity
2-ddlBranch

I bind the ddlcity from tblCity in sql database

the code is here:

VB
Protected Sub Page_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.DataBinding
        Dim conn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("schoolz").ConnectionString)
        conn.Open()
        Dim cmd As New SqlCommand("select strName from tblCity", conn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        ddlCity.DataSource = dr
        ddlCity.DataTextField = "strName"
        ddlCity.DataBind()
        dr.Close()
        conn.Close()
    End Sub


I want that when I select the city from ddlcity, then all the branch will display in the second dropdown ddlBranch.

For this I write the stored procedure

SQL
create proc spEditReport
@intReturnAdd int=0 output
as
begin
declare @add int
select strName from tblBranch where intAddressID=@intReturnAdd

select @add= tblAddress.intAddressID
from tblAddress  join tblCity 
on tblAddress.intAddressID=tblCity.intCityID
if @@error <> 0
    begin
        rollback transaction
        return @intReturnAdd
    end
    commit transaction
    set @intReturnAdd = @add
    return @intReturnAdd
end



Can anybody tell me how to code to display the branches when I select the city?


please help me out.
thanks and regard
subiya
Posted
Updated 9-Jun-11 21:49pm
v3
Comments
Dalek Dave 10-Jun-11 3:49am    
Edited for Grammar and Readability.

I have query that Why you are using stored procedure to do this?
You can simply fetch the ID of the Selected City in the selectedIndex changed event of your dropdownlist containing cities.And Can pass this ID to fetch the respected Branches of that particular city in where clause .
And then load them in the other dropdownlist .

I assume here that You have declared a primary key in tblcity which must be foreign key for tblBranch.
Try this may it can solve your Problem.
 
Share this answer
 
Comments
ahsan.subiya 9-Jun-11 6:13am    
how to fetch the id of selected city and pass it in to select query to select the branch
Karwa_Vivek 9-Jun-11 6:20am    
Please post the table structure of your City table and Branch Table.Then only I can help you here.
ahsan.subiya 9-Jun-11 6:26am    
actually there is three table
tblBranch having coloumn intBranchId,strBranchName ,inAddressId and many more
tblAddress having coloumn intAddressId,intCityId and manymore
tblCity having coloumn intCityId,strCityName,and many more
hope this info vl help u to help me plz
Karwa_Vivek 9-Jun-11 6:33am    
is there any one link,or say a common ID in between these three Tables.
means is there a cityID in tblBranch
ahsan.subiya 9-Jun-11 6:45am    
no sir.
tblBranch intbranchId(PK)and intAddressId(Fk)
tblAddress intAddressID(pk)and intCityId(fk)
tblCity intCityID(pk)
Try This
On selected Index Changed Event Of your ddlCity
 Dim sd As New SqlDataAdapter("select cityID from tableCity where cityName='" + cmb1.SelectedText.ToString() + "'",connection)
        Dim dt As New DataTable
        sd.Fill(dt)
        If dt.Rows.Count > 0 Then
conn.Open()
        Dim cmd1 As New SqlCommand("select BranchNames from tableBranch where cityID='"+dt.Rows (0).Item (0).ToString ()+"'", conn)
        Dim dr1 As SqlDataReader
        dr1 = cmd.ExecuteReader()
      ddlBranch.DataSource = dr1
        ddlBranch.DataTextField = "BranchName"
        ddlBranch.DataBind()
        dr1.Close()
        conn.Close()

Implement this as Per Your need.I have taken Field Just For Example.So check it and Try
 
Share this answer
 
Comments
Dalek Dave 10-Jun-11 3:50am    
Good Call.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900