Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

selection of one dropdownlist determines the selected value of another

celinehglcelinehgl Member Posts: 4
Hi all,

I have a trouble here.

I have 2 dropdownlists - one is named ddl_vehicle and another one is ddl_employee.

The ddl_vehicle has already an value populated which is select * from vehicle and ddl_employee has an value populated which is select * from employee.

What I need to do now is the following:
1. if vehicle A has a driver, then the selected value of ddl_employee should be the driver of vehicle A.
2. However, if vehicle A has no assigned driver, then the selected value of the ddl_employee should be something like " Please select a driver from the list" which allows the user to choose a driver from the ddl_employee.

Therefore, to summarize, the selected value of ddl_vehicle DOES NOT POPULATE THE VALUE of ddl_employee, instead, it should determine the selected value of the ddl_employee.

Below are my codes, however, I encountered an error on the databind of ddl_employee: 'ddl_Employee' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value

Please let me know what i should I change to get the results I wanted.

[B][U]AddJobOrder.aspx.vb[/U][/B]
Imports [Class]
Imports System.Configuration
Imports System.Data.SqlClient
Partial Public Class AddJobOrder
Inherits System.Web.UI.Page

Dim vf As New VehicleFunctions
Dim ef As New EmployeeFunctions

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

If IsPostBack = False Then

ddl_VehicleNo.DataSource = vf.GetVehicle
ddl_VehicleNo.DataTextField = "vehicleNo"
ddl_VehicleNo.DataValueField = "vehicleNo"
ddl_VehicleNo.SelectedValue = 1
ddl_VehicleNo.DataBind()

ddl_Employee.DataSource = ef.GetEmployee
ddl_Employee.DataTextField = "FullName"
ddl_Employee.DataValueField = "employeeNo"
'ddl_Employee.SelectedValue = 1
Dim vehicleNo1 As String = ddl_VehicleNo.SelectedValue
Dim vehicleNo As Integer = Convert.ToInt32(vehicleNo1)
ddl_Employee.SelectedValue= ef.GetEmployeeVehicleByText(vehicleNo)
If ddl_Employee.SelectedValue = "" Then
ddl_Employee.Items.Insert(0, New ListItem("No Record Found", ""))
End If
ddl_Employee.DataBind()

[B][U]VehicleFunctions.class[/U][/B]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class VehicleFunctions
Private strConnectionString As String
Private objDataAdapter As SqlDataAdapter
Private objInsertCommand As SqlCommand
Private objLoadCommand As SqlCommand
Private objUpdateCommand As SqlCommand
Private objValidCommand As SqlCommand

Public Sub New()

MyBase.New()

objDataAdapter = New SqlDataAdapter

strConnectionString = ConfigurationManager.ConnectionStrings("Strata").ConnectionString

End Sub

Public Function GetVehicle() As DataSet
' Dim query As String = "SELECT distinct Employee.firstName + ' ' + Employee.lastName as employeeName,Employee_Vehicle.vehicleNo as vehicleNo,Employee_Vehicle.employeeNo as employeeNo FROM Employee_Vehicle, Employee where Employee_Vehicle.employeeNo = Employee.employeeNo"
Dim query As String = "SELECT * from Vehicle"
Dim cmd As New SqlCommand(query)

Return FillDataSet(cmd, "vehicle")
End Function

Private Function FillDataSet(ByVal cmd As SqlCommand, ByVal tableName As String) As DataSet
Dim con As New SqlConnection(strConnectionString)
cmd.Connection = con

Dim adapter As New SqlDataAdapter(cmd)

Dim ds As New DataSet()
Try
con.Open()
adapter.Fill(ds, tableName)

Catch ex As Exception
Finally
con.Close()
End Try

Return ds

End Function


End Class

[B][U]EmployeeFunctions.class[/U][/B]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class EmployeeFunctions
Private strConnectionString As String
Private objDataAdapter As SqlDataAdapter
Private objInsertCommand As SqlCommand
Private objLoadCommand As SqlCommand
Private objUpdateCommand As SqlCommand
Private objValidCommand As SqlCommand
Public Sub New()

MyBase.New()

objDataAdapter = New SqlDataAdapter

strConnectionString = ConfigurationManager.ConnectionStrings("Echino").ConnectionString

End Sub

Public Function GetEmployee() As DataSet

Dim query As String = "SELECT employeeNo,cast(employeeNo as varchar) + '.' + ' ' + firstName + ' ' + lastName as FullName FROM Employee"
Dim cmd As New SqlCommand(query)

Return FillDataSet(cmd, "employee")
End Function

Private Function FillDataSet(ByVal cmd As SqlCommand, ByVal tableName As String) As DataSet
Dim con As New SqlConnection(strConnectionString)
cmd.Connection = con

Dim adapter As New SqlDataAdapter(cmd)

Dim ds As New DataSet()
Try
con.Open()
adapter.Fill(ds, tableName)

Catch ex As Exception
Finally
con.Close()
End Try

Return ds

End Function
Private Function GetEmployeeVehicle() As SqlCommand

objLoadCommand = New SqlCommand("GetEmployeeVehicle", New SqlConnection(strConnectionString))
objLoadCommand.CommandType = CommandType.StoredProcedure
objLoadCommand.Parameters.Add(New SqlParameter("@vehicleNumber", SqlDbType.Int))
GetEmployeeVehicle = objLoadCommand

End Function

Public Function GetEmployeeVehicleByText(ByVal vehicleNumber As Integer) As String
With objDataAdapter
Try
.SelectCommand = GetEmployeeVehicle()
.SelectCommand.Parameters("@vehicleNumber").Value = vehicleNumber
If .SelectCommand.Connection.State = ConnectionState.Closed Then
.SelectCommand.Connection.Open()
End If
.SelectCommand.CommandTimeout = 0
GetEmployeeVehicleByText = CStr(.SelectCommand.ExecuteScalar())
Catch exeptionindef As Exception
Throw (New ApplicationException(exeptionindef.Message))
Finally
If Not .SelectCommand Is Nothing Then
If Not .SelectCommand.Connection Is Nothing Then
.SelectCommand.Connection.Dispose()
End If
.SelectCommand.Dispose()
End If
.Dispose()
End Try
End With

End Function
End Class

[B][U]GetEmployeeVehicle.sql (Stored Procedure)[/U][/B]
CREATE PROCEDURE [dbo].[GetEmployeeVehicle]
-- Add the parameters for the stored procedure here
@vehicleNumber int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT cast(Employee.employeeNo as varchar) + '.' + ' ' + Employee.firstName + ' ' + Employee.lastName as FullName from Employee, Vehicle, Employee_Vehicle
WHERE Vehicle.vehicleNo = @vehicleNumber and Employee.employeeNo = Employee_Vehicle.employeeNo and Vehicle.vehicleNo = Employee_Vehicle.vehicleNo

END

Comments

  • seancampbellseancampbell Pennsylvania, USAMember Posts: 684 ✭✭✭
    in the future: Please use code boxes to retain the spacing of your lines, makes it a smidge easier to read. (Click the < > button in the text field's font selecter)

    You are getting that error message because you are trying to set ddl_Employee.SelectedValue to a value that does not exist.

    Personally I suggest doing this:

    [code]
    ddl_Employee.DataSource = ef.GetEmployee
    ddl_Employee.DataTextField = "FullName"
    ddl_Employee.DataValueField = "employeeNo"
    ddl_Employee.Databind()

    'Always have this at the top of the list so when a value of "" is
    'selected, it won't crash, You can use Validation to force the user
    'to select an Employee by checking if the SelectedValue of this
    'dropdown <> "" when you add or update a record in the DB
    ddl_Employee.Items.Insert(0, New ListItem("Please select a driver", ""))
    'We're making sure we insert this blank value item BEFORE setting
    'the SelectedValue of ddl_Employee

    Dim vehicleNo1 As String = ddl_VehicleNo.SelectedValue
    Dim vehicleNo As Integer = Convert.ToInt32(vehicleNo1)
    ddl_Employee.SelectedValue= ef.GetEmployeeVehicleByText(vehicleNo)
    [/code]

    Hope that helps,
    Sean Campbell
    firesickle.com
  • seancampbellseancampbell Pennsylvania, USAMember Posts: 684 ✭✭✭
    One other thing,

    I have had that problem, where setting the SelectedValue to a value that doesn't exist fails. I am currently working on a Web Based Cost Estimation software, and there are many cases where the configuration changes and the existing data would cause crashes because of that...

    I wrote this function and use it to select values of DropDowns in that project. It seems to work fairly quickly (sometimes I am driving 100 to 200 drop downs with values and selecting values in them).

    Your welcome to steal it. Note that the DLL object is ByRef
    [code]
    '''
    ''' Selects the appropriate DropDownList item based on a value
    '''
    '''
    '''
    '''
    Public Sub SelectDropDownListValue(ByRef DDL As DropDownList, ByVal Value As Object)
    DDL.SelectedIndex = -1
    Try
    For i As Integer = 0 To DDL.Items.Count - 1
    If DDL.Items(i).Value = CStr(Value) Then
    DDL.SelectedIndex = i
    Exit For
    End If
    Next
    Catch ex As Exception
    End Try
    End Sub
    [/code]
Sign In or Register to comment.