How do I display updated dropdownlist value along with other records?

Question

Greetings mates,

I have two tables, Positions and login.

The position table is a lookup table with two values posiitonId and position_Title.

Then a login table with several values.

We would like to update the records in Login table by selecting from position table.

Login table has positionId as foreign key to positions table.

We would like to select position Id that is in both position table and login table.

A user can choose to change the value of positions by selecting the position Id from position table that matches positionId in Login table so that when updated the new value displays the position_Title text on the dropdown.

For instance, if a position Id (say 2) in position table has position_Title called Manager is updated to position Id 3 with locationDirector, we would like that reflected as the default selected value in the dropdown.

The code I have below shows the correct updated value but does not show the rest of the values in the dropdown that a user can select from to update.

I know I am doing this wrong. I have done this before but can't figure out how to make it work here.

Any assistance is greatly appreciated.

        Dim cmd2 As New SqlCommand("Select distinct p.PositionId, p.Position_title from Positions p left join tblLogin l on p.positionId = l.positionId", New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString))
        cmd2.Connection.Open()

        Dim ddlPValues As SqlDataReader
        ddlPValues = cmd2.ExecuteReader()

        PositionList.DataSource = ddlPValues
        PositionList.DataValueField = "PositionId"
        PositionList.DataTextField = "Position_title"
        PositionList.DataBind()
        
        '*****************
        
                 Dim myConnectionString As [String] = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
                Dim myConnection As New SqlConnection(myConnectionString)
                Try
                    myConnection.Open()
                    Dim cmd1 As New SqlCommand("Select * from tblLogin where userid=@userid and EMPLMT_STA_CD = 'A' ", myConnection)
                    cmd1.Parameters.AddWithValue("@userid", Session("UserId"))
                    Dim dr As SqlDataReader = cmd1.ExecuteReader()
                    If dr.Read() Then
                        NameTB.Text = dr("fullName").ToString()
                        AddressTB.Text = dr("Address").ToString()
                        PhoneTB.Text = dr("Phone_nbr").ToString()
                        EmailTB.Text = dr("Email").ToString()
                        PositionList.SelectedValue = dr("Position_title").ToString()
                    End If
                Catch ex As SqlException
                    Response.Write("")
                Finally
                    myConnection.Close()
                End Try
            End If
        End If
        
      'HTML
      
              Precinct:

Accepted Answer

Hi @Simflex ,

You need to get the value of dr("Position_title").ToString(), then the SQL statement in cmd1 also needs to link the two tables to get Position_title.

According to your description, there should be no Position_title field in the tblLogin table.

Dim cmd1 As New SqlCommand("SELECT distinct * FROM Positions p LEFT JOIN tblLogin l ON p.positionId = l.positionId where userid=@userid and EMPLMT_STA_CD = 'A' ", myConnection)

Then pass dr("Position_title").ToString() to PositionList.SelectedItem.Text.

PositionList.SelectedItem.Text = dr("Position_title").ToString()

Below is my test based on the code you provided.

 
              Precinct:

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then

            Dim cmd2 As New SqlCommand("Select distinct p.PositionId, p.Position_title from Positions p left join tblLogin l on p.positionId = l.positionId", New SqlConnection(ConfigurationManager.ConnectionStrings("DBCS").ConnectionString))
            cmd2.Connection.Open()

            Dim ddlPValues As SqlDataReader
            ddlPValues = cmd2.ExecuteReader()

            PositionList.DataSource = ddlPValues
            PositionList.DataValueField = "PositionId"
            PositionList.DataTextField = "Position_title"
            PositionList.DataBind()
        End If
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs)
        Dim myConnectionString As [String] = ConfigurationManager.ConnectionStrings("DBCS").ConnectionString
        Dim myConnection As New SqlConnection(myConnectionString)
        Try
            myConnection.Open()
            Session("UserId") = 2
            Dim cmd1 As New SqlCommand("SELECT distinct * FROM Positions p LEFT JOIN tblLogin l ON p.positionId = l.positionId where userid=@userid and EMPLMT_STA_CD = 'A' ", myConnection)
            cmd1.Parameters.AddWithValue("@userid", Session("UserId"))
            Dim dr As SqlDataReader = cmd1.ExecuteReader()
            If dr.Read() Then
                NameTB.Text = dr("fullName").ToString()
                AddressTB.Text = dr("Address").ToString()
                PhoneTB.Text = dr("Phone_nbr").ToString()
                EmailTB.Text = dr("Email").ToString()
                PositionList.SelectedItem.Text = dr("Position_title").ToString()
            End If
        Catch ex As SqlException
            Response.Write("")
        Finally
            myConnection.Close()
        End Try
    End Sub

Best regards,

Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How do I display updated dropdownlist value along with other records?

0 additional answers

Your answer