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

Can't read back in values to DropDownList w/correct item showing from database

I am passing a unique id from one page to my More_info.aspx.  This unique identifier tells what info to load into the More_info.aspx page.  All of the info showing up except for the DropDownList box.  It is blank.  It should be filled with all of the Distinct classes from the class_info column of the CLASSES table.  The item selected in the DropDown list should be the class_info value taken from the INFO table.
Not sure how to do this.


Here is the code behind for my More_info.aspx page.


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("info_id") Is Nothing) Then
                GetInfo(Request.QueryString("info_id").ToString)
            Else
                DropDownPopulate()
            End If
        End If
    End Sub
    Private Sub DropDownPopulate()
        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()
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("info_id").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 info_id = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classDownList.SelectedValue = myReader("class_name").ToString
                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("info_id") Is Nothing) Then    '<---------******* Modified
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', calendar = '" & calendar.SelectedDate & "', notes = '" & notesText.Text & "' WHERE info_id='" & Request.QueryString("info_id").ToString & "'"

        Else
            myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) 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
0
jettman26
Asked:
jettman26
  • 3
1 Solution
 
sachiekCommented:
Are you able to trace it in debug mode?

Does it return any records when you execute that sql statment in your SQL Query analaysier?

Sachi
0
 
jettman26Author Commented:
I added a Watch to 'lassDownList.SelectedValue' and the value was ""
0
 
Swapnil PipariaArchitectCommented:
If you pass one unique Id in than it is obvious that in page_load event following statement execute.

            If Not (Request.QueryString("info_id") Is Nothing) Then
                GetInfo(Request.QueryString("info_id").ToString)  <--  Executing this statement when u unit info_id is sent            Else
                DropDownPopulate()
            End If
so DropDownPopulate() method is never called if info_id is passed.
so replace GetInfor Method with given below. (It is combination of GetInfo and DropDownPopulate Method so whenever info_id is passed or not passed DropDownPopulate() is executed everytime)

Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("info_id").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 info_id = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classDownList.SelectedValue = myReader("class_name").ToString
                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

        myCommand = New SqlClient.SqlCommand
        myAdapter = New SqlClient.SqlDataAdapter
        Dim myReader = New SqlClient.SqlDataReader
       
        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()
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try

        myConn.Close()

    End Sub

0
 
jettman26Author Commented:
VS.NET doesn't like this line.
Dim myReader = New SqlClient.SqlDataReader   "it says that "Local variable 'myReader' is already declared in the current block"

If I change it to myReader1 then the 'New SqlClient.SqlDataReader' is underlined in blue and VS.NET says
"System.Data.SqlClient.SqlDataReader.PrivateSub New(Command As System.Data.SqlClient.SqlCommand) is not accessable in this context because it is 'Private'."
0
 
jettman26Author Commented:
I figured it out!  It was actually really easy.
netswap, thanks for pointing me in the right direction.

All I had to do was add DropDownPopulate to my If Not statement.  Now it works.  Here it is.

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("info_id") Is Nothing) Then
              DropDownPopulate()                
              GetInfo(Request.QueryString("info_id").ToString)
            Else
                DropDownPopulate()
            End If
        End If
    End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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