Solved

Gridview show/refresh based on listboxes

Posted on 2007-11-18
18
990 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now