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 GetdistrictUser s()
Dim sqlconn As New Data.SqlClient. SqlConnection

sqlconn.Connect ionString = "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.CreateC ommand
Dim cmdgetDistrictU sers As Data.SqlClient. SqlCommand = sqlconn.CreateC ommand


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

cmdgetDistrictU sers.CommandTyp e = Data.CommandTyp e.StoredProcedu re

'This tells Sql server the name of your stored procedure

cmdgetDistrictU sers.CommandTex t = "GetDistrictUse rs"

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

cmdgetDistrictU sers.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 cmdGetDistrictI nfo.Parameters

'*** .Add("@district id", 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

'cmdgetDistrict Users.Parameter s("@districtID" ).Value = ddlDistrict.Sel ectedValue

cmdgetDistrictU sers.Parameters ("@districtID") .Value = ddlDistrict.Sel ectedValue

'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.SelectComman d = cmdgetDistrictU sers
'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.DataSou rce = "Users"
ddlUser.DataMem ber = "UserFileNa me"
ddlUser.DataVal ueField = "UserFileId "
ddlUser.Selecte dIndex = ds.Tables("user s").Rows.Cou nt - 1


ddlApproveBy.Da taSource = "Users"
ddlApproveBy.Se lectedIndex = 0
ddlApproveBy.Da taMember = "UserFileNa me"
ddlApproveBy.Da taValueField = "UserFileId "
ddlApproveBy.Se lectedIndex = ds.Tables("Appr overs").Rows.Co unt - 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.