dropdownlist value is selected depends on the selection value of another dropdownlist

[celinehgl]

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.

AddJobOrder.aspx.vb
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()

VehicleFunctions.class
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

EmployeeFunctions.class
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

GetEmployeeVehicle.sql (Stored Procedure)
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

 

[alander]

use a variable to store the result of the employee (if found)

ddlEmployee.selectedItem = variable

if not found just do a

ddlEmployee.selectedindex = -1

sorry i didnt look at ur codes, but this is what i will do

 

[acbarberi]

I basically had to rewrite your entire piece of code. What I wrote may not work perfectly because I had to do it in on-the-fly, but I'm sure you can correct any minor mistakes.

I ended taking out a bunch of the extra unneccessary code and making the code a bit more re-usable.

AddJobOrder.aspx.vb

Private vf As VehicleFunctions = New VehicleFunctions()
Private ef As EmployeeFunctions = New EmployeeFunctions()

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

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

    If ddl_VehicleNo.Items.Count >= 1 Then
        ddl_VehicleNo.SelectedIndex = 0
    End If

    ddl_Employee.Items.Add("[Select Vehicle #]")
End Sub

Public Sub ddl_VehicleNo_SelectedIndexChanged() Handles ddl_VehicleNo.SelectedIndexChanged
   If ddl_VehicleNo.SelectedIndex >= 0 Then
       Dim vNo As Integer = Convert.ToInt32(ddl_vehicle.SelectedText)

       ddl_Employee.DataSource = ef.GetEmployee(vNo)
       ddl_Employee.DataTextField = "FullName"
       ddl_Employee.DataValueField = "employeeNo"
       ddl_Employee.DataBind()
   Else
       ddl_Employee.DataSource = Nothing
       ddl_Employee.Items.Clear()
       ddl_Employee.Items.Add("[Select Vehicle #]")
   End If
End Sub

VehicleFunctions.class

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class VehicleFunctions
    Public Function GetVehicle() As DataSet
        Return MyData.FillDataSet("SELECT * FROM Vehicle")
    End Function
End Class

EmployeeFunctions.class

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class EmployeeFunctions
    Public Function GetEmployee(ByVal vehicleNum as Integer = -1) As DataSet
        Return MyData.FillDataSet("SELECT * FROM GetEmployeeVehicle(" & _
            vehicleNum.ToString() & ") ORDER BY vehicleNo")
    End Function
End Class

NEW CLASS: MyData.class

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class MyData
    Private strConn As String = ConfigurationManager.ConnectionStrings("Echino").ConnectionString

    Public Shared Function FillDataSet(ByVal queryString As String) As DataSet
        conn = New SqlConnection(strConn)
        conn.Open()

        cmd = New SqlCommand()
        cmd.Connection = conn
        cmd.CommandText = queryString

        Dim adap As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim ds As DataSet = New DataSet()
        adap.Fill(ds)

        adap.Dispose()
        conn.Close()

        Return ds
    End Function
End Class

GetEmployeeVehicle.sql (Changed to FUNCTION)

CREATE FUNCTION GetEmployeeVehicle(@vehicleNumber int)
RETURN TABLE
AS
RETURN
(
SELECT (CAST(e.employeeNo AS VARCHAR) + '.' + ' ' + e.firstName + ' ' + e.lastName) AS FullName
 FROM Employee e, Vehicle v, Employee_Vehicle ev
  WHERE e.employeeNo = ev.employeeNo
   AND ev.vehicleNo = v.vehicleNo
   AND v.vehicleNo = @vehicleNumber
);