Imports System.Data.SqlClient
Public Class frm_addlook
    Dim cmd As SqlCommand = Nothing
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Private Sub frm_addlook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Fill_All_ComboBoxes()
    End Sub
    Private Sub Fill_ComboFields(ByVal comboField As ComboBox, ByVal cmdText As String)
        'comboField.Items.Clear()
        cmd = Nothing
        Try
            cmd = New SqlCommand(cmdText, conn)
            cmd.CommandType = CommandType.StoredProcedure
            If (conn.State = ConnectionState.Closed) Then
                conn.Open()
            End If
            Dim ds As New DataSet()
            ds.Tables.Clear()
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            comboField.DataSource = ds.Tables(0)
            comboField.DisplayMember = "t_values"
            comboField.ValueMember = "t_values"
            comboField.SelectedIndex = -1
        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            conn.Close()
            cmd = Nothing
        End Try
    End Sub
    Private Sub Fill_All_ComboBoxes()
        Fill_ComboFields(Me.cmb_city, "SO_tvalues")
        Fill_Streetname(Me.cmb_street, Me.cmb_city.Text.Trim)
    End Sub
    Private Sub Fill_Streetname(ByVal comboField As ComboBox, ByVal CITY As String)
        comboField.DataSource = Nothing
        comboField.Items.Clear()

        'Dim add_city As String


        'If CITY.Trim.Length = 0 Or CITY.Trim = "System.Data.DataRowView" Then
        '    add_city = ""
        'Else
        '    add_city = " where town_nm = '" & CITY & "'"
        'End If

        Dim cmdtext As String
        'cmdtext = "select distinct street_nm from Address  " & add_city
        cmdtext = "SO_streetnm"
        cmd = Nothing
        Try
            cmd = New SqlCommand(cmdtext, conn)

            If CITY.Trim.Length = 0 Or CITY.Trim = "System.Data.DataRowView" Then
                cmd.Parameters.AddWithValue("@city", "1")
            Else
                cmd.Parameters.AddWithValue("@city", Me.cmb_city.Text.Trim)
            End If
            cmd.CommandType = CommandType.StoredProcedure
            If (conn.State = ConnectionState.Closed) Then
                conn.Open()
            End If
            Dim ds As New DataSet()
            ds.Tables.Clear()
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            comboField.DataSource = Nothing
            comboField.Items.Clear()
            comboField.DataSource = ds.Tables(0)
            comboField.DisplayMember = "street_nm"
            comboField.ValueMember = "street_nm"
            comboField.SelectedIndex = -1
        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            conn.Close()
        End Try
    End Sub
    Private Sub FillGrid()
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            'Dim ssql As String
            'Dim add_city As String
            Dim CITY As String = Me.cmb_city.Text.Trim
            Dim cmdtext As String
            cmdtext = "SO_adrlookupGrid"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            If CITY.Trim.Length = 0 Or CITY.Trim = "System.Data.DataRowView" Then
                cmd.Parameters.AddWithValue("@Street", Me.cmb_street.Text.Trim)
                cmd.Parameters.AddWithValue("@city", "1")
            Else
                cmd.Parameters.AddWithValue("@Street", Me.cmb_street.Text.Trim)
                cmd.Parameters.AddWithValue("@city", CITY)
            End If
            cmd.CommandType = CommandType.StoredProcedure
            'ssql = "select PREMISE_NO [PREMISE], TOWN_NM [TOWN],HOUSE_NO [HOUSE NO],  ADDR_PREFIX_1 [PREFIX1], ADDR_PREFIX_2 [PREFIX2], STREET_NM [STEET NAME] , STREET_LOCATION_1_CD [LOCATION_CD1], STREET_LOCATION_2_CD [LOCATION_CD1],ADDR_SUFFIX_CD [SUFFIX2] , STREET_SUFFIX_CD [SUFFIX1], ZIP_CD [ZIP CODE]   FROM dbo.Address WHERE  STREET_NM = '" & Me.cmb_street.Text.Trim & "' " & add_city & " order by TOWN_NM, HOUSE_NO"
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
            gdv_add.DataSource = ds.Tables(0)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmb_city_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_city.SelectedIndexChanged
        Fill_Streetname(Me.cmb_street, Me.cmb_city.Text.Trim)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        FillGrid()
    End Sub
End Class