Showing selected value in dropdown as well as data returned from db

[ninelgorb]

I have an EDIT EMPLOYEE page. It contains a dropdown a user must select an employee from.
I have other dropdowns on the page, for example site, department, and position dropdowns.

The department, siten, and position dropdowns are populated from the database.
When user selects employee John Smith. I need the other dropdowns to show his site, department, and position.

So if John Smith's position is IT, I want that to be selected in the dropdown along with all other positions that were returned by the database.

How can I do this?
Here is my code so far:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            If Request.Cookies.Count = 1 Then
                Response.Redirect("login.aspx")
            ElseIf Request.Cookies.Count = 2 Then
                Dim sSQLEmployee As String
                Dim oConn As SqlConnection
                Dim oComm As SqlCommand
                Dim sConn As String
                Dim oReader As SqlDataReader

                'Employee
                sSQLEmployee = "SELECT sAgentId, rtrim(ltrim(slastname)) + ' ' + rtrim(ltrim(sfirstname)) as 'Name' From ememployee WHERE slastname <> ' ' ORDER  BY slastname"
                sConn = System.Configuration.ConfigurationSettings.AppSettings.Item("connString")

                oConn = New SqlConnection(sConn)
                oConn.Open()
                oComm = New SqlCommand(sSQLEmployee, oConn)
                oReader = oComm.ExecuteReader()

                ddlEmployee.DataSource = oReader
                ddlEmployee.DataBind()
                ddlEmployee.Items.Insert(0, "SELECT")
                ddlEmployee.Items(0).Value = 0
                ddlEmployee.Items(0).Selected = True

                oConn.Close()
           End If
        End If
End Sub

Private Sub ddlEmployee_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlEmployee.SelectedIndexChanged

        sAgentId = ddlEmployee.SelectedValue
        If sAgentId = "0" Then
            lblResult.Text = "Please Select an Employee"
        Else
            Call PopulateDDLs(sAgentId)
        End If
End Sub

Sub PopulateDDLs(ByVal sAgentId As String)
        Dim oConn As SqlConnection
        Dim oComm As SqlCommand
        Dim oReader As SqlDataReader
        Dim sSQLPayRollId As String
        Dim sSQLEmployee As String
        Dim sSQLAgentid As String
        Dim sConn As String
        Dim sErrorText As String
        Dim sLastName As String
        Dim sFirstName As String
        Dim buspGetTextBoxValues As Boolean
        Dim iPayRollId As String
        Dim sSSN As String
        Dim iMsPositionId As String
        Dim iMsDepartmentId As String
        Dim iMsSiteId As String
        
        'AgentId
        txtEmpId.Text = ddlEmployee.SelectedValue

        Dim da As New SqlDataAdapter
        Dim ds As DataSet = New DataSet

        buspGetTextBoxValues = GetControlValues(sAgentId, sFirstName, sLastName, iPayRollId, sSSN, iMsPositionId, iMsDepartmentId, iMsSiteId, sErrorText)
        txtLname.Text = Trim(sLastName)
        txtFname.Text = Trim(sFirstName)
        If iPayRollId = "0" Then
            txtPayId.Text = ""
        Else
            txtPayId.Text = Trim(iPayRollId)
        End If
        txtSSN.Text = Trim(sSSN)

        'Site
        sSQLReport = "SELECT imsSiteId, sDescription FROM dbo.msSite"
        sConn = System.Configuration.ConfigurationSettings.AppSettings.Item("connString")
        oConn = New SqlConnection(sConn)
        oConn.Open()
        oComm = New SqlCommand(sSQLReport, oConn)
        oReader = oComm.ExecuteReader()

        ddlSite.DataSource = oReader
        ddlSite.DataBind()
        ddlSite.Items.Insert(0, "SELECT")
        oConn.Close()

        'Department
        sSQLReport = "SELECT imsDepartmentId, sDescription FROM dbo.msDepartment"
        sConn = System.Configuration.ConfigurationSettings.AppSettings.Item("connString")
        oConn = New SqlConnection(sConn)
        oConn.Open()
        oComm = New SqlCommand(sSQLReport, oConn)
        oReader = oComm.ExecuteReader()

        ddlDept.DataSource = oReader
        ddlDept.DataBind()
        ddlDept.Items.Insert(0, "SELECT")
        oConn.Close()

        'Position
        sSQLReport = "SELECT imsPositionId, sDescription FROM dbo.msPosition"
        sConn = System.Configuration.ConfigurationSettings.AppSettings.Item("connString")
        oConn = New SqlConnection(sConn)
        oConn.Open()
        oComm = New SqlCommand(sSQLReport, oConn)
        oReader = oComm.ExecuteReader()

        ddlPosition.DataSource = oReader
        ddlPosition.DataBind()
        ddlPosition.Items.Insert(0, "SELECT")
        oConn.Close()

End Sub

Any help would be greatly appreciated.
Thanks,
Ninel

 

[jbenson001]

You have to get the info for the emplyee you want into a dataset or datareader..etc. Then do something like this:

Ex:
ddlDept.Items.FindByValue(value in ds).Selected = True

 

[ninelgorb]

Am I misunderstanding this?

I want the the position "IT" to appear selected in the dropdown, but when clicking the dropdown to see the other positions, I want to see the list that came back from the database.

The .FindByValue will do this for me?

 

[jbenson001]

Yes your dropdown will contain all the original data and the findbyvalue will find the correct value and select it

 

[ninelgorb]

What am I supposed to have in the parenthesis of .FindByValue(???)

imsDepartmentId is the value returned from the database for department.

 

[jbenson001]

That is the value you are looking for. LIke I said, you need to select all the info you need from the DB into some sort of datastore, dataset, datatable..etc. Then that value would be a column in on of those objects.

Here is a piece of code that I am using:

'''This example sets the dropdown to a value that is in a datagrid cell.
ddl.Items.FindByValue(e.Item.Cells(4).Text).Selected = True

 

[ninelgorb]

I'm really sorry to keep bugging you about this.
If you take a look at my code above, I'm populating a datareader with the info from my database. Would the datareader work? What would I put in the parenthesis with thw datareader? I am soo confused....can you tell?

 

[ninelgorb]

I actually found other code that worked:
ddlDept.SelectedIndex = ddlDept.Items.IndexOf(ddlDept.Items.FindByValue(iMsDepartmentId))

Thank you jbenson001 for all your help. I really appreciate it. Sometimes I think I don't know where I would be without all the help I receive from the forums.

 

[jbenson001]

I see you are using datareaders, however there is no where that you are pulling all the specific info for a particular employee that I see, unless you have not included that code. For example,

Select Lastname, FirstName, SSN, Dept, Title ..etc
From <table>
Where empid = < some id here>

That info should be stored in something, lets say a dataset named dsEmployees. Then you can use the code above:

'This assumes that the id you are looking for is the first column of the dataset.
ddlDept.Items.FindByValue(DsEmployees1.Tables(0).Rows(0).Item(0)).Selected = True

 

[jbenson001]

Glad you found it and it workd. Sorry my code was within a datagarid. The correct syntax for you is what you have.. ddl.selectedindex .. However the IndexOf is not necessary, you can just use ddlDept.Items.FindByValue(iMsDepartmentId).Selected = True. But it is working and that is all that counts.

Jim