How to change the content in the drop-down menu

[Ankor]

I have two drop-down menus: one is for the department, and another one - for the associates in this department. I populated the first one from the database. Now I want the second drop down to be populated from the database depending on the value selected in the first drop-down. To execute the second procedure I turned the AutoPostBack property on the first drop-down menu to TRUE to reload the page each time I change the value. It works and populates the associate ids in the second box. The problem appears when I need to send data to the third procedure that uses both variables. Here is a sub for the second drop-down menu:

Sub DisplayID()

Try
strDept = ddlDept.SelectedItem.Value
'Refer to the connection in web.config
strConnection = ConfigurationSettings.AppSettings("DBConnection")
'Connect to the database
sqlConn = New SqlConnection(strConnection)
'Create a command string
strCmd = "sp_PullID"
'Create a command
sqlCmd = New SqlCommand(strCmd, sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure
'Set parameters
sqlParam = sqlCmd.Parameters.Add(New SqlParameter("@sDept", SqlDbType.VarChar, 10))
sqlParam.Direction = ParameterDirection.Input
sqlParam.Value = strDept
'Open Connection
sqlConn.Open()
'Set Data Reader
sqlReader = sqlCmd.ExecuteReader()

'Get data from the Data Reader
ddlID.DataSource = sqlReader
ddlID.DataTextField = "sName"
ddlID.DataValueField = "sID"
If Not Page.IsPostBack Then
ddlID.DataBind()
End If

Catch ex As Exception
Response.Write(ex.ToString & "<br>")
Finally
sqlReader.Close()
sqlConn.Close()
End Try

End Sub

If I use IsPostBack, the second drop-down menu is not populated with new names every time I change the value in the first drop-down. If I bind data without IsPostBack, the variable created for the ID does not hold the value that should be passed to the third stored procedure. Is there any way around it?

 

[jbenson001]

what is ddlid holding? You only need to bind the second ddl when the first one changes. There is no need to bind anything when the second ddl is selected.
Jim

 

[Ankor]

ddlID is holding associate ids in the department selected in the first ddl. When the department and associate id are selected, the values should be sent to the database to pull the information for the associate.
I have a Page_Load event that loads both drop-downs.

 

[jbenson001]

don't bind the ddl again, that is causing the problem. Set Autopostback = TRUE on the second ddl. Then in the SelectedIndexChanged event of that ddl, grab the selected values from both ddls and call your SP.

Jim

 

[Ankor]

Jim, I thought I was not doing it. I have

If Not Page.IsPostBack Then
ddlID.DataBind()
End If

It means it should bind only when the page loads the first time, correct? It's my first .NET project, so I might understand you incorrectly.

Another note. I have a button that should execute the third procedure. I know how to program it, that is not a problem. The problem is that if I have AutoPostBack = True on both ddl, and bind them both if NOT Page.IsPostBack, then the second ddl does not update values when I change the first one.

 

[jbenson001]

Page load bind first ddl when not post back.
Bind second ddl on when selection changes in first ddl.
Button click.... get values in both ddl and run your sp.

 

[Ankor]

Page load bind first ddl when not post back. -- Page load should bind both because the selected department in the first ddl may have associates to be loaded to the second.

Bind second ddl on when selection changes in first ddl. -- Is it SelectedIndexChanged event? My problem is that I understand what should be done, I don't know how.

 

[jbenson001]

Yes.. Use the SelectedIndexchanged event of the first ddl, grab the value and get the data and bind ddl2

 

[Ankor]

OK. Finally got it. Had to create ddlDept_Load and ddlDept_SelectedIndexChanged events, and populate second ddl with "Not Page.IsPostBack" statement in the load. It seems to work. Thank you.

 

[jbenson001]

glad you got it working

Jim