My project is to create a page where our customer can do a search to see who in our vendor list. I was asked for this search page to have a capability for the customers to search by state (might select multiple state), counties (might select multiple counties), vendor name and Mn/Distirct(might select multiple districts). I just created a sample page where customer can select multiple states and do search and it works fine. My question is how can I all these 3 list control in one page and work together to get the right result. Should I create a function for these three controls and somehow connect them to get the desired results? Thanks for the help
Here is the code I used to populate the listcontrol for state
<%@ Import namespace="System.Data" %>
<%@ Import Namespace="System.Data.OracleClient"%>
<%@ Import Namespace="System.Configuration.ConfigurationManager"%>
<html>
<head><title>Multiple Selections</title></head>
<body>
<h3>Multiple Selections</h3>
<form id="Form1" runat="server">
<asp:listBox id = "lstListBox"
selectionmode=Multiple
AutoPostBack="false"
Runat="server" AppendDataBoundItems="True" >
<asp:ListItem Value="state">-- State--</asp:ListItem>
</asp:listBox>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /><br/><br/>
<asp:gridview id="gvEmployee" runat="server" >
<Columns>
<asp:BoundField />
</Columns>
</asp:gridview>
</form>
</body>
</html>
<script language="vb" runat="server">
Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)
If Not IsPostBack Then
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
Dim strSQLforListBox As String = "SELECT distinct aState FROM vendaddr ORDER BY aState"
oOracleConn.Open()
Dim objCommand As New OracleCommand(strSQLforListBox, oOracleConn)
lstListBox.DataSource = objCommand.ExecuteReader()
lstListBox.DataTextField = "aState"
lstListBox.DataValueField = "aState"
lstListBox.DataBind()
oOracleConn.Close()
End If
End Sub
Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strWhereClause As String = ""
Dim liThisOne As ListItem
For Each liThisOne In lstListBox.Items
If liThisOne.Selected Then
strWhereClause += "aState=" & "'" & liThisOne.Value & "'" & " OR "
End If
Next
If strWhereClause.Length > 0 Then
gvEmployee.Visible = True
strWhereClause = Left(strWhereClause, strWhereClause.Length - 4)
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
Dim strSQLforGrid As String = "SELECT vendor,astate FROM VENDADDR where " & strWhereClause & "order by astate"
oOracleConn.Open()
Dim objCommand As New OracleCommand(strSQLforGrid, oOracleConn)
gvEmployee.DataSource = objCommand.ExecuteReader()
gvEmployee.DataBind()
oOracleConn.Close()
Else
gvEmployee.Visible = False
End If
End Sub
</script>
Here is the code I used to populate the listcontrol for state
<%@ Import namespace="System.Data" %>
<%@ Import Namespace="System.Data.OracleClient"%>
<%@ Import Namespace="System.Configuration.ConfigurationManager"%>
<html>
<head><title>Multiple Selections</title></head>
<body>
<h3>Multiple Selections</h3>
<form id="Form1" runat="server">
<asp:listBox id = "lstListBox"
selectionmode=Multiple
AutoPostBack="false"
Runat="server" AppendDataBoundItems="True" >
<asp:ListItem Value="state">-- State--</asp:ListItem>
</asp:listBox>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /><br/><br/>
<asp:gridview id="gvEmployee" runat="server" >
<Columns>
<asp:BoundField />
</Columns>
</asp:gridview>
</form>
</body>
</html>
<script language="vb" runat="server">
Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)
If Not IsPostBack Then
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
Dim strSQLforListBox As String = "SELECT distinct aState FROM vendaddr ORDER BY aState"
oOracleConn.Open()
Dim objCommand As New OracleCommand(strSQLforListBox, oOracleConn)
lstListBox.DataSource = objCommand.ExecuteReader()
lstListBox.DataTextField = "aState"
lstListBox.DataValueField = "aState"
lstListBox.DataBind()
oOracleConn.Close()
End If
End Sub
Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strWhereClause As String = ""
Dim liThisOne As ListItem
For Each liThisOne In lstListBox.Items
If liThisOne.Selected Then
strWhereClause += "aState=" & "'" & liThisOne.Value & "'" & " OR "
End If
Next
If strWhereClause.Length > 0 Then
gvEmployee.Visible = True
strWhereClause = Left(strWhereClause, strWhereClause.Length - 4)
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
Dim strSQLforGrid As String = "SELECT vendor,astate FROM VENDADDR where " & strWhereClause & "order by astate"
oOracleConn.Open()
Dim objCommand As New OracleCommand(strSQLforGrid, oOracleConn)
gvEmployee.DataSource = objCommand.ExecuteReader()
gvEmployee.DataBind()
oOracleConn.Close()
Else
gvEmployee.Visible = False
End If
End Sub
</script>