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