連動式dropdownlist

2017-05-11

177
0
vb.net
2017-05-23

連動式dropdownlist

先撈出所有城鎮value

 Sub GetTown()
        TownID.Items.Clear()
        Conn.Open()
        Dim Comm As New SqlCommand("Select TownID, TownName From ListTown Order By TownID", Conn)
        Dim RD As SqlDataReader = Comm.ExecuteReader()
        While RD.Read()
            Dim li As New ListItem
            li.Text = RD("TownName")
            li.Value = RD("TownID")
            TownID.Items.Add(li)
        End While
        Conn.Close()
    End Sub

接著撈出所有村里資料

Sub GetVillage()
    VillageID.Items.Clear()
    Conn.Open()
    Dim sql As String = "Select row_number() Over (Order By TownID) R, TownID, VillageID, VillageName From ListVillage Order By TownID"
    Dim Comm As New SqlCommand(sql, Conn)
    Dim RD As SqlDataReader = Comm.ExecuteReader()
    While RD.Read()
        If RD("TownID") = 1 Then
           Dim li As New ListItem
           li.Text = RD("VillageName")
           li.Value = RD("VillageID")
           VillageID.Items.Add(li)
        End If
    End While    
    Conn.Close()
End Sub

'row_number() Over (Order By TownID as R 此段會新增一個R欄位，並且會依數列向下+1

簡單來說就是" 依照指定的欄位排序，並逐筆加上順號的方式 "-出處Topcat

'另外有 Rank over與 DENSE_RANK 可參考
https://dotblogs.com.tw/topcat/archive/2009/08/06/9906.aspx

接著寫下當Dropdownlist更動value值時所作的變化

  Protected Sub TownID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TownID.SelectedIndexChanged
        Conn.Open()
        TownIDChanged(Request("ctl00$CP$TownID"))
        Conn.Close()
    End Sub

當Dropdownlist變化時，使用Requrest("ctl00$CP$TownID")代入副程式 TownIDChanged的tid

Sub TownIDChanged(ByVal tid As Byte)
        Try
            VillageID.Items.Clear()
            Dim Comm As New SqlCommand("Select VillageID, VillageName From ListVillage Where TownID=" & tid & " Order By TownID", Conn)
            Dim RD As SqlDataReader = Comm.ExecuteReader()
            While RD.Read()
                Dim li As New ListItem
                li.Text = RD("VillageName")
                li.Value = RD("VillageID")
                VillageID.Items.Add(li)
            End While
            TownID.Text = Request("ctl00$CP$TownID")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

由TownID_SelectedIndexChanged取得tid

(Byval tid as Byte是將參數傳入)

去執行下面的SQL Query

備註:ctl00$CP$TownID，由於是在MasterPage，所以系統會指定一個特殊字元，要取得該字元，須由網頁上去看該元件ID。

回首頁