adding dropdown list control

This code works fine!!

However, i have a lot more dropdown lists to go on this form and was wondering if there is a better way to do this and cut down on the code and do i really have to open and close the database for each dropdown box. I am very new to all of this so appreciate your experienced eye. Thanks.  


 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            Dim healthConn As SqlConnection
            Dim cmdselect As SqlCommand
            Dim dtrSalutation As SqlDataReader

            healthConn = New SqlConnection("workstation id.......)
            healthConn.Open()
            cmdselect = New SqlCommand("select salutationID, salutation from salutations", healthConn)
            dtrSalutation = cmdselect.ExecuteReader()
            ddlSalutation.DataSource = dtrSalutation
            ddlSalutation.DataValueField = "salutationID"
            ddlSalutation.DataTextField = "salutation"
            ddlSalutation.DataBind()
            dtrSalutation.Close()
            healthConn.Close()

        End If
        If Not IsPostBack Then
            Dim healthConn As SqlConnection
            Dim cmdselect As SqlCommand
            Dim dtrcountry As SqlDataReader

            healthConn = New SqlConnection("workstatio.")
            healthConn.Open()
            cmdselect = New SqlCommand("select countryID, countryname from country", healthConn)
            dtrcountry = cmdselect.ExecuteReader()
            ddlcountry.DataSource = dtrcountry
            ddlcountry.DataValueField = "countryID"
            ddlCountry.DataTextField = "countryname"
            ddlcountry.DataBind()
            dtrcountry.Close()
            healthConn.Close()
        End If

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim HealthCom As SqlConnection
        Dim StrInsert As String
        Dim cmdInsert As SqlCommand
        Dim canid As Integer


        HealthCom = New SqlConnection("workstation i....")
        StrInsert = "insert can (firstname, Surname, Email, state, salutationID, countryID) values (@firstname, @Surname, @email, @state, @countryID, @salutationID)"

        cmdInsert = New SqlCommand(StrInsert, HealthCom)
        cmdInsert.Parameters.Add("@firstname", txtFirstname.Text)
        cmdInsert.Parameters.Add("@surname", txtSurname.Text)
        cmdInsert.Parameters.Add("@email", txtEmail.Text)
        cmdInsert.Parameters.Add("@state", txtState.Text)
        cmdInsert.Parameters.Add("@salutationID", ddlSalutation.SelectedValue)
        cmdInsert.Parameters.Add("@countryID", ddlcountry.SelectedValue)

        HealthCom.Open()
        cmdInsert.ExecuteNonQuery()
        HealthCom.Close()

        Response.Redirect("candidateSort.aspx?id=" + HttpUtility.UrlEncode(canID.ToString()))

    End Sub
blueohyessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh SrinivasTechnical ConsultantCommented:
Here is a function I use - you can adjust the parameters for the function to suit your needs. This one binds a DDL by passing in a Storedprocedure name, TableName, ValueField to use, TextField to use, True or false if you want to display an "All" in your list, also recieves the name of the DDL you want to populate,  and an optional value if your SP requires parameter:

It is called like so:

BindDDLs("MyTable"m "MyStoredProc", "ID", "Name", true, DropDownList1, Nothing)

Public Shared Sub BindDDLs(ByVal DT_name As String, ByVal SP_Name As String, _
        ByVal theValueFieldName As String, ByVal thetextFieldName As String, _
        ByVal HasAll As Boolean, ByVal whichDDL As DropDownList, ByVal ParamOptions As Object)
        Dim DA As SqlDataAdapter = New SqlDataAdapter
        Dim Cmd_Cbo As SqlCommand = New SqlCommand(SP_Name, iMConn)
        Dim DT As DataTable = New DataTable
       Dim SP_Options = DT_name

        Select Case SP_Options
           'Use parameters if the recieved table name matches any of these
            Case "MovieList"
                Cmd_Cbo.Parameters.Add("@cID", CType(ParamOptions, Int16))
            Case "CountryListByTitle"
                Cmd_Cbo.Parameters.Add("@MOVID", CType(ParamOptions, Int16))
        End Select

        DA.SelectCommand = Cmd_Cbo
        Cmd_Cbo.CommandType = CommandType.StoredProcedure
        DA.Fill(DT)

        If DT.Rows.Count = 0 Then
            whichDDL.BackColor = Color.Gray
            whichDDL.Enabled = False
        Else
           'if boolean is true then add an "All" to the list
            If HasAll Then
                Dim indexRow As DataRow = DT.NewRow
                indexRow(0) = 0
                indexRow(1) = "All"
                DT.Rows.InsertAt(indexRow, 0)
            End If

            With whichDDL
                .DataSource = DT
                .DataTextField = thetextFieldName
                .DataValueField = theValueFieldName
                .DataBind()
            End With
        End If
    End Sub


regards,

KS

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ramesh SrinivasTechnical ConsultantCommented:
If you just wanted to pass it an SQLQuery String then you could alter it like so:

Public Shared Sub BindDDLs(ByVal DT_name As String, ByVal strSQL As String, _
        ByVal theValueFieldName As String, ByVal thetextFieldName As String, _
        ByVal HasAll As Boolean, ByVal whichDDL As DropDownList)
        Dim DA As SqlDataAdapter = New SqlDataAdapter
        Dim Cmd_Cbo As SqlCommand = New SqlCommand(strSQL, YourConnection)
        Dim DT As DataTable = New DataTable
       Dim SP_Options = DT_name

        DA.SelectCommand = Cmd_Cbo
        Cmd_Cbo.CommandType = CommandType.StoredProcedure
        DA.Fill(DT)

        If DT.Rows.Count = 0 Then
            whichDDL.BackColor = Color.Gray
            whichDDL.Enabled = False
        Else
           'if boolean is true then add an "All" to the list
            If HasAll Then
                Dim indexRow As DataRow = DT.NewRow
                indexRow(0) = 0
                indexRow(1) = "All"
                DT.Rows.InsertAt(indexRow, 0)
            End If

            With whichDDL
                .DataSource = DT
                .DataTextField = thetextFieldName
                .DataValueField = theValueFieldName
                .DataBind()
            End With
        End If
    End Sub

regards,

KS
Ramesh SrinivasTechnical ConsultantCommented:
I havent added in the dispose methods for the command object, but thats easy enough for you to do.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.