• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

How to populate DropDownList box with info from MSSQL database

I have a page called Main_info.aspx that has a Calander control, a DropDownList and a textbox.
The info saved from this page gets put into a Table called INFO.  class_name in the INFO table is the Foreign Key for class_name in the CLASSES table.
I need to populate the DropDownList with all the records from the class_name column in the CLASSES table.  I don't know how to do this.

Here is the code behind for the Main_info.aspx page.


#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents HyperLink1 As System.Web.UI.WebControls.HyperLink
    Protected WithEvents calendar As System.Web.UI.WebControls.Calendar
    Protected WithEvents classDownList As System.Web.UI.WebControls.DropDownList
    Protected WithEvents notesText As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents btnSave As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            If Not (Request.QueryString("class_name") Is Nothing) Then
                GetInfo(Request.QueryString("class_name").ToString)
            Else
                Dim myConn As New SqlClient.SqlConnection
                Dim myCommand As New SqlClient.SqlCommand
                Dim myAdapter As New SqlClient.SqlDataAdapter
                Dim myReader As SqlClient.SqlDataReader
                Dim CS As String

                CS = "Server=DELL;initial catalog=classes;integrated security=true"
                myConn.ConnectionString = CS
                myCommand.Connection = myConn
                myAdapter.SelectCommand = myCommand
                myCommand.CommandText = "SELECT * FROM CLASSES"
                myConn.Open()

                Try
                    myReader = myCommand.ExecuteReader()

                    While myReader.Read
                        classDownList.SelectedValue = myReader("class_name").ToString.Trim
                        'calendar.SelectedDate = myReader("info_date").ToString
                        'notesText.Text = myReader("notes").ToString.Trim

                    End While
                    lblMessage.Text = "Record Successfully Read"
                Catch
                    lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
                End Try
                myConn.Close()
            End If
        End If
    End Sub
    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("class_name").ToString

        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT * FROM INFO " & _
        "WHERE class_name = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classDownList.SelectedValue = myReader("class_name").ToString.Trim
                calendar.SelectedDate = myReader("info_date").ToString
                notesText.Text = myReader("notes").ToString.Trim

            End While
            lblMessage.Text = "Record Successfully Read"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String


        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand

        If Not (Request.QueryString("class_name") Is Nothing) Then    '<---------******* Modified
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', calendar = '" & calendar.SelectedDate & "', notes = '" & notesText.Text & "' WHERE Class_Name='" & Request.QueryString("class_name").ToString & "'"

        Else
            myCommand.CommandText = "INSERT INTO INFO VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & notesText.Text & "')"
        End If

        myConn.Open()
        Try
            myCommand.ExecuteNonQuery()
            lblMessage.Text = "Record Successfully Added"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.
    End Sub

    Private Sub calendar_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar.SelectionChanged

    End Sub
End Class
0
jettman26
Asked:
jettman26
  • 2
1 Solution
 
tusharashahCommented:
Make a SUB of following code and call it whenever you want to Populate DropDownList.. ( call it in IsPostBack false )

                Dim myConn As New SqlClient.SqlConnection
                Dim myCommand As New SqlClient.SqlCommand
                Dim myAdapter As New SqlClient.SqlDataAdapter
                Dim myReader As SqlClient.SqlDataReader
                Dim CS As String

                CS = "Server=DELL;initial catalog=classes;integrated security=true"
                myConn.ConnectionString = CS
                myCommand.Connection = myConn
                myAdapter.SelectCommand = myCommand
                myCommand.CommandText = "SELECT DISTINCT class_name FROM CLASSES"
                myConn.Open()

                Try
                    myReader = myCommand.ExecuteReader()
                    classDownList.DataSource = myReader
                    classDownList.DataTextField = "class_name"
                    classDownList.DataValueField = "class_name"
                    classDownList.DataBind()
                  End Try
                myConn.Close()


-tushar
0
 
jettman26Author Commented:
Thanks
Worked like a charm.
0
 
jettman26Author Commented:
Now I am hoping you can figure out my listBox question that I posted yesterday!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now