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
Sign In or Register to comment.