Solved

Gridview show/refresh based on listboxes

Posted on 2007-11-18
18
1,005 Views
Last Modified: 2013-11-26
I have many listboxes set with the select multiple option on and what I want to do is show/refresh upon a button a query based on those selections in a gridview.

I have set my gridview datasource to :
SELECT * FROM [CatalogApp] WHERE (([PRODUCT TYPE] = ?) AND ([DIAMETER] = ?) AND ([LENGTH] = ?) AND ([GRADE] = ?) AND ([THREAD] = ?) AND ([PLATING] = ?) AND ([PAKAGING] = ?)) ORDER BY [PRODUCT CODE]
where all parameters are set to ListBox.SelectedValue.

What should be VB the code in my button ? Is my datasource set correctly ? Thanks
0
Comment
Question by:GPSAddict
  • 8
  • 8
18 Comments
 
LVL 37

Expert Comment

by:samtran0331
ID: 20309354
you mean that's what you have set as the datasource attribute of your gridview?
like
<asp:gridview runat="server".... datasource="SELECT..."...> etc?

you need to add a sqldatasource control to your page.
set the datasourceid of the gridview to the name of the sqldatasource
and then in the select query of the sqldatasource, use the listbox id's for the select parameters...

0
 

Author Comment

by:GPSAddict
ID: 20309428
This is exactly what I did, except I'm using an AccessDataSource...The gridview don't show up obviously because when the page loads nothing is selected in the listboxes yet. So, I want to choose some values in some listboxes, then press a search button, then I want the gridview to return the query results. Can you help ?
0
 

Author Comment

by:GPSAddict
ID: 20309452
Oh and when nothing is selected, I want to show all records (except on page load) !
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 37

Expert Comment

by:samtran0331
ID: 20312008
>>So, I want to choose some values in some listboxes, then press a search button, then I want the gridview to return the query results. Can you help ?

Can you post what code you have?

>>Oh and when nothing is selected, I want to show all records (except on page load) !

You would have to set the wildcard asterisk as the default value for all your parameters...
0
 

Author Comment

by:GPSAddict
ID: 20312046
I have no code yet...I've only done what I said in my first post in Visual Web Developper...Thanks
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20313812
Here is a simple example
<%@ Page Language="VB" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If Me.ListBox1.SelectedIndex >= 0 Then
            Me.GridView1.DataSourceID = "AccessDataSource2"
        End If
        Me.GridView1.DataBind()
    End Sub
 
    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If Me.ListBox1.SelectedIndex >= 0 Then
            Me.GridView1.DataSourceID = "AccessDataSource1"
        End If
        Me.GridView1.DataBind()
    End Sub
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="ListBox1" runat="server" DataSourceID="AccessDataSource3" DataTextField="CompanyName"
                DataValueField="CustomerID"></asp:ListBox><br />
            <br />
            <asp:Button ID="Button1" runat="server" Text="Filter" OnClick="Button1_Click" /><asp:Button
                ID="Button2" runat="server" Text="Show All" OnClick="Button2_Click" />
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID"
                DataSourceID="AccessDataSource1" EmptyDataText="There are no data records to display.">
                <Columns>
                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" />
                    <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
                    <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                </Columns>
            </asp:GridView>
            <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="App_Data\Nwind.mdb"
                SelectCommand="SELECT `CustomerID`, `CompanyName`, `ContactName`, `ContactTitle`, `Address`, `City`, `Region`, `PostalCode`, `Country` FROM `Customers`">
            </asp:AccessDataSource>
            <asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="App_Data\Nwind.mdb"
                SelectCommand="SELECT `CustomerID`, `CompanyName`, `ContactName`, `ContactTitle`, `Address`, `City`, `Region`, `PostalCode`, `Country` FROM `Customers` WHERE `CustomerID` = ?">
                <SelectParameters>
                    <asp:ControlParameter ControlID="ListBox1" DefaultValue="*" Direction="Input" Name="CustomerID"
                        PropertyName="SelectedValue" />
                </SelectParameters>
            </asp:AccessDataSource>
            <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="App_Data\Nwind.mdb"
                SelectCommand="SELECT `CustomerID`, `CompanyName` FROM `Customers`"></asp:AccessDataSource>
        </div>
    </form>
</body>
</html>

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 20313865
I understand that but I need to loop through all my listboxes controls (there are 7 of them), retrieve all the multiple values they each have, construct my SQL statement and then refresh the gridview based on the results. I don't thik it can work to only switch datasources based on which listbox is selected. Is more problem more clear ? Thanks
0
 

Author Comment

by:GPSAddict
ID: 20319205
Anyone ?
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20319507
Ideally, you should be doing this in codebehind...I haven't the time to work up a full demo...
but basically...you would need to build the SQL string based on the listboxes...
like start with a base sql: "SELECT * FROM SomeTable"
Then, do each listbox:
1. loop through it
2. get the selected items
3. if it has selected items, add those to the sql statement as an "IN" clause
so you would end up with sql like:
"SELET * FROM SomeTable WHERE SomeField IN (1,2,4,5)"

and the 1,2,4,5 were added by looping through the listbox.
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20319778
Here is a working example using Northwind Access DB with one listbox
<%@ Page Language="VB" %>
 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        BindGrid()
    End Sub
 
    Private Sub BindGrid()
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("Nwind.mdb")
        Dim Ds As DataSet = New DataSet
        
        Dim SQLStatement As String = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country FROM Customers"
        
        Dim sb As StringBuilder = New StringBuilder
        
        sb.Append(" WHERE CustomerID IN (")
        Dim HasCustomersSelected As Boolean = False
        For i As Integer = 0 To ListBox1.Items.Count - 1
            If ListBox1.Items(i).Selected Then
                sb.Append("'" & ListBox1.Items(i).Value.ToString & "', ")
                HasCustomersSelected = True
            End If
        Next
        If HasCustomersSelected = True Then
            SQLStatement &= sb.ToString.TrimEnd
        End If
        SQLStatement = SQLStatement.Substring(0, SQLStatement.Length - 1)
        SQLStatement &= ")"
        'More listboxes
        
        Using Conn As New OleDbConnection(ConnString)
            Dim Cmd As New OleDbCommand(SQLStatement, Conn)
            Dim Da As OleDbDataAdapter = New OleDbDataAdapter(Cmd)
            Conn.Open()
            Try
                Da.Fill(Ds, "Customers")
            Catch ex As Exception
                Response.Write("Error with db query: " & ex.Message.ToString)
            End Try
        End Using
        
        With Me.GridView1
            .DataSource = Ds
            .DataBind()
        End With
 
    End Sub
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="ListBox1" runat="server" DataSourceID="AccessDataSource3" DataTextField="CompanyName"
                DataValueField="CustomerID" SelectionMode="multiple"></asp:ListBox><br />
            <br />
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Filter" />
            <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Clear" /><br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
                BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="CustomerID"
                EmptyDataText="There are no data records to display." ForeColor="Black" GridLines="Vertical">
                <Columns>
                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" />
                    <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
                    <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                </Columns>
                <FooterStyle BackColor="#CCCC99" />
                <RowStyle BackColor="#F7F7DE" />
                <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
            <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="Nwind.mdb"
                SelectCommand="SELECT `CustomerID`, `CompanyName` FROM `Customers`"></asp:AccessDataSource>
        </div>
    </form>
</body>
</html>

Open in new window

0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20319803
actually, ignore that last post, I was going to add another button in there and decided not to..try this one
<%@ Page Language="VB" %>
 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        BindGrid()
    End Sub
 
    Private Sub BindGrid()
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("Nwind.mdb")
        Dim Ds As DataSet = New DataSet
        
        Dim SQLStatement As String = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country FROM Customers"
        
        Dim sb As StringBuilder = New StringBuilder
        
        sb.Append(" WHERE CustomerID IN (")
        Dim HasCustomersSelected As Boolean = False
        For i As Integer = 0 To ListBox1.Items.Count - 1
            If ListBox1.Items(i).Selected Then
                sb.Append("'" & ListBox1.Items(i).Value.ToString & "', ")
                HasCustomersSelected = True
            End If
        Next
        If HasCustomersSelected = True Then
            SQLStatement &= sb.ToString.TrimEnd
            SQLStatement = SQLStatement.Substring(0, SQLStatement.Length - 1)
            SQLStatement &= ")"
        End If
        
        'More listboxes
        
        Using Conn As New OleDbConnection(ConnString)
            Dim Cmd As New OleDbCommand(SQLStatement, Conn)
            Dim Da As OleDbDataAdapter = New OleDbDataAdapter(Cmd)
            Conn.Open()
            Try
                Da.Fill(Ds, "Customers")
            Catch ex As Exception
                Response.Write("Error with db query: " & ex.Message.ToString)
            End Try
        End Using
        
        With Me.GridView1
            .DataSource = Ds
            .DataBind()
        End With
 
    End Sub
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="ListBox1" runat="server" DataSourceID="AccessDataSource3" DataTextField="CompanyName"
                DataValueField="CustomerID" SelectionMode="multiple"></asp:ListBox><br />
            <br />
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Filter" />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
                BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="CustomerID"
                EmptyDataText="There are no data records to display." ForeColor="Black" GridLines="Vertical">
                <Columns>
                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" />
                    <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
                    <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                </Columns>
                <FooterStyle BackColor="#CCCC99" />
                <RowStyle BackColor="#F7F7DE" />
                <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
            <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="Nwind.mdb"
                SelectCommand="SELECT `CustomerID`, `CompanyName` FROM `Customers`"></asp:AccessDataSource>
        </div>
    </form>
</body>
</html>

Open in new window

0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20320140
Here is an example with 2 listboxes, it can use some refinement, but it works and the logic is there.
<%@ Page Language="VB" %>
 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        BindGrid()
    End Sub
 
    Private Sub BindGrid()
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("Nwind.mdb")
        Dim Ds As DataSet = New DataSet
        
        Dim SQLStatement As String = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country FROM Customers"
        
        Dim sb As StringBuilder = New StringBuilder
        
        sb.Append(" WHERE CustomerID IN (")
        Dim HasCustomersSelected As Boolean = False
        For i As Integer = 0 To ListBox1.Items.Count - 1
            If ListBox1.Items(i).Selected Then
                sb.Append("'" & ListBox1.Items(i).Value.ToString & "', ")
                HasCustomersSelected = True
            End If
        Next
        If HasCustomersSelected = True Then
            SQLStatement &= sb.ToString.TrimEnd
            SQLStatement = SQLStatement.Substring(0, SQLStatement.Length - 1)
            SQLStatement &= ")"
        End If
        
        Dim sb2 As StringBuilder = New StringBuilder
        Dim HasCountriesSelected As Boolean = False
        For j As Integer = 0 To ListBox2.Items.Count - 1
            If ListBox2.Items(j).Selected Then
                sb2.Append("'" & ListBox2.Items(j).Value.ToString & "', ")
                HasCountriesSelected = True
            End If
        Next
        
        sb.Remove(0, sb.Length) 'Clear stringbuilder
        If HasCustomersSelected = True And HasCountriesSelected = True Then
            sb.Append(" " & RadioButtonList1.SelectedValue.ToString & " Country IN (")
        ElseIf HasCustomersSelected = False And HasCountriesSelected = True Then
            sb.Append(" WHERE Country IN (")
        End If
        
        If HasCountriesSelected = True Then
            sb.Append(sb2.ToString)
            SQLStatement &= sb.ToString.TrimEnd
            SQLStatement = SQLStatement.Substring(0, SQLStatement.Length - 1)
            SQLStatement &= ")"
        End If
        
        'Response.Write(SQLStatement)
        'Exit Sub
        
        Using Conn As New OleDbConnection(ConnString)
            Dim Cmd As New OleDbCommand(SQLStatement, Conn)
            Dim Da As OleDbDataAdapter = New OleDbDataAdapter(Cmd)
            Conn.Open()
            Try
                Da.Fill(Ds, "Customers")
            Catch ex As Exception
                Response.Write("Error with db query: " & ex.Message.ToString)
            End Try
        End Using
        
        With Me.GridView1
            .DataSource = Ds
            .DataBind()
        End With
 
    End Sub
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="ListBox1" runat="server" DataSourceID="AccessDataSource3" DataTextField="CompanyName"
                DataValueField="CustomerID" SelectionMode="multiple"></asp:ListBox>
            <br />
            <asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatDirection="Horizontal"
                RepeatLayout="Flow">
                <asp:ListItem Selected="true">OR</asp:ListItem>
                <asp:ListItem>AND</asp:ListItem>
            </asp:RadioButtonList><br />
            <asp:ListBox ID="ListBox2" runat="server" DataSourceID="AccessDataSource1" DataTextField="Country"
                DataValueField="Country" SelectionMode="multiple"></asp:ListBox>
            <br />
            <br />
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Filter" />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
                BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="CustomerID"
                EmptyDataText="There are no data records to display." ForeColor="Black" GridLines="Vertical">
                <Columns>
                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" />
                    <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
                    <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                </Columns>
                <FooterStyle BackColor="#CCCC99" />
                <RowStyle BackColor="#F7F7DE" />
                <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
            <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="Nwind.mdb"
                SelectCommand="SELECT `CustomerID`, `CompanyName` FROM `Customers`"></asp:AccessDataSource>
            <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="Nwind.mdb"
                SelectCommand="SELECT DISTINCT `Country` FROM `Customers`"></asp:AccessDataSource>
        </div>
    </form>
</body>
</html>

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 20320427
I used the following in Access VB. Is it possible to use this in my ASP.NET 2.0 page ??? I don't have look at each listbox manually...Thanks
    Dim Ctl As Control
    Dim ctls As Controls
    Dim i As Integer
    Dim strFieldName As String
    Dim strWhere As String
    Dim strFullWhere As String
 
    For Each Ctl In Me.Controls
        strWhere = ""
 
        If TypeOf Ctl Is ListBox Then
            Debug.Print Ctl.Name
    
            With Ctl
                For i = 0 To Ctl.ListCount - 1
    
                    If Ctl.Selected(i) = True Then
                        strFieldName = Mid(Ctl.Name, 4)
                        If strWhere <> "" Then
                            strWhere = strWhere & " OR '" & Ctl.Column(1, i) & "'"
                        Else
                            strWhere = "[" & strFieldName & "] = '" & Ctl.Column(1, i) & "'"
                        End If
                    End If
                Next i
            End With
    
            If strWhere <> "" Then
                If strFullWhere <> "" Then
                    strFullWhere = strFullWhere & " AND " & "(" & strWhere & ")"
                Else
                    strFullWhere = strFullWhere & "(" & strWhere & ")"
                End If
            End If
        End If
    Next
 
    If strFullWhere <> "" Then: strSQL = strSQL & " WHERE " & strFullWhere
    Debug.Print strSQL
    txtSQL = strSQL
    'Passer en paramètre la string SQL à l'objet d'affichage
    Me.SubSelection.Form.SetSource strSQL

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 20321225
Tried the following...no errors too but it stills always output "SELECT * FROM CatalogApp" only...NOTE: my listbox are in a Multiview names Multiview1 in View1. Thanks

        Dim ctl As Control
        Dim lst As ListBox
        Dim strSQL As String
        strSQL = "SELECT * FROM CatalogApp"
 
        Dim i As Integer
        Dim strFieldName As String
        Dim strWhere As String
        Dim strFullWhere As String
 
        strWhere = ""
        strFullWhere = ""
 
        For Each ctl In MultiView1.Controls
 
            If TypeOf ctl Is ListBox Then
                lst = ctl
                For i = 0 To lst.Items.Count - 1
                    If lst.Items(i).Selected Then
                        strFieldName = lst.ID
                        If strWhere <> "" Then
                            strWhere = strWhere & " OR '" & lst.Items(i).Value.ToString & "'"
                        Else
                            strWhere = "[" & strFieldName & "] = '" & lst.Items(i).Value.ToString & "'"
                        End If
                    End If
                Next
 
                If strWhere <> "" Then
                    If strFullWhere <> "" Then
                        strFullWhere = strFullWhere & " AND " & "(" & strWhere & ")"
                    Else
                        strFullWhere = strFullWhere & "(" & strWhere & ")"
                    End If
                End If
            End If
        Next
 
        If strFullWhere <> "" Then : strSQL = strSQL & " WHERE " & strFullWhere
        End If
        txtSQL.Text = strSQL

Open in new window

0
 

Author Comment

by:GPSAddict
ID: 20353741
Nobody knows ?
0
 
LVL 37

Accepted Solution

by:
samtran0331 earned 500 total points
ID: 20357579
Sorry, I haven't had time to work up an example...but the problem with your Access code is that in ASP.Net, the control collection does not work the same way...your last code post is getting closer, but I think that you still need a recursive function to actually get at the controls because of the way ASP.Net builds the control "tree"...
this is c#, but the IterateThroughChildren function might be what you need...
http://aspnet.4guysfromrolla.com/articles/081402-1.2.aspx

vb version below, if you run it "as is" you'll see how the asp.net page builds and nests the controls
Private Sub IterateThroughChildren(ByVal parent As Control) 
    For Each c As Control In parent.Controls 
        lblControlList.Text += "<li>" + c.ToString() + "</li>" 
        If c.Controls.Count > 0 Then 
            lblControlList.Text += "<ul>" 
            IterateThroughChildren(c) 
            lblControlList.Text += "</ul>" 
        End If 
    Next 
End Sub 
 
 
'you would mod it to your code:
Private Sub IterateThroughChildren(ByVal parent As Control) 
    For Each c As Control In parent.Controls 
        'listbox code here
 
        If c.Controls.Count > 0 Then 
            IterateThroughChildren(c) 
        End If 
    Next 
End Sub 

Open in new window

0
 
LVL 1

Expert Comment

by:Computer101
ID: 21135856
Forced accept.

Computer101
EE Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question