Filling drop down list from database

[jaeden99]

I have a two drop down list box.
The first contains district name(district id is the value)
and the the second will contain the user name based on the district selected in the first drop down list.

I am trying to code this in vb.net. It is a web based application.
I've gotten the district info to load, but I can't figure out how to get the users to load. I've created a stored procedure to get user...

Stored procedure info.
SELECT UserFileID, UserFileName
FROM UserFile
WHERE UserFileStatus = 1 and DistrictID = @districtID
GO

Public Sub GetdistrictUsers()
Dim sqlconn As New Data.SqlClient.SqlConnection

sqlconn.ConnectionString = "Server=" & ServerName & ";User id=sa;Password=" & pswd & "; Initial Catalog=" & DBName & ";"

'Here you are creating a command object (cmd) to go with the connection object (sqlconn)

'Dim cmdGetDistricts As SqlCommand = sqlconn.CreateCommand
Dim cmdgetDistrictUsers As Data.SqlClient.SqlCommand = sqlconn.CreateCommand


'this tells SQL Server that the command is a stored procedure

cmdgetDistrictUsers.CommandType = Data.CommandType.StoredProcedure

'This tells Sql server the name of your stored procedure

cmdgetDistrictUsers.CommandText = "GetDistrictUsers"

'Since the stored procedure takes an input parameter you add the parameter here

cmdgetDistrictUsers.Parameters.Add(New Data.SqlClient.SqlParameter("@districtID", Data.SqlDbType.Int, 4))

'or you can add it like you did before using the "With" statement - you can add all of these with "With"

'With cmdGetDistrictInfo.Parameters

'*** .Add("@districtid", SqlDbType.Int, 4)


'Now supply a value for your input parameter - it will be the "VALUE" of the selected

'District in the District drop down

'cmdgetDistrictUsers.Parameters("@districtID").Val ue = ddlDistrict.SelectedValue

cmdgetDistrictUsers.Parameters("@districtID").Valu e = ddlDistrict.SelectedValue

'Now you're read to execute the command - first create a data adapter

Dim da As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter

'Put the command in the data adapter

da.SelectCommand = cmdgetDistrictUsers
'Create a dataset to hold your data coming back

Dim ds As Data.DataSet = New Data.DataSet
'Dim ds1 As DataSet = New DataSet

'Then fill the dataset putting your returned data from the data adapter in a table that you might call "Users"
Try


ddlUser.DataSource = "Users"
ddlUser.DataMember = "UserFileName"
ddlUser.DataValueField = "UserFileId"
ddlUser.SelectedIndex = ds.Tables("users").Rows.Count - 1


ddlApproveBy.DataSource = "Users"
ddlApproveBy.SelectedIndex = 0
ddlApproveBy.DataMember = "UserFileName"
ddlApproveBy.DataValueField = "UserFileId"
ddlApproveBy.SelectedIndex = ds.Tables("Approvers").Rows.Count - 1

Catch esystem As Exception

Debug.WriteLine("An exception of type " & esystem.Message.ToString & " was encountered while attempting to access database.")
'MessageBox("An exception has occurred.", _
'"Source: " & esystem.Source _
'& esystem.Message)
End Try

End Sub

Thanks in advance.