gridview with multi select listbox

HiI have a list box that is bound to a table in a database. it produces a list of companies. The user will then come along and sele开发者_C百科ct multiple companies that they want to view information about, and then they hit a selet button which ill display the company information (e.g company name, company site, address e.t.c) in a gridview underneath. however the issue that i am having is that it only displays ONE of the multiple companies selected and its always the top one.

Can someone please shed some light on how i get all the companies to be displayed in the gridview?

i am programming in vb.net

please see source code below

<asp:ListBox ID="ListBox1" runat="server" 
        DataSourceID="SqlDataSource11" DataTextField="compName" 
        DataValueField="compDataID" SelectionMode="Multiple" AutoPostBack="True"></asp:ListBox>
    <asp:SqlDataSource ID="SqlDataSource11" runat="server" 
        ConnectionString="<%$ ConnectionStrings:IWSRiskAssessmentConnectionString %>" 
        SelectCommand="SELECT [compDataID], [compName] FROM [tblCompany] WHERE ([compDataID] &lt;&gt; @compDataID) ORDER BY [compName]">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="compDataID" 
                PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>
<asp:GridView 
        ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource2">
        <Columns>
            <asp:BoundField DataField="compName" HeaderText="compName" 
                SortExpression="compName" />
            <asp:BoundField DataField="Site Name" HeaderText="Site Name" 
                SortExpression="Site Name" />
            <asp:BoundField DataField="Reference Number" HeaderText="Reference Number" 
                SortExpression="Reference Number" />
            <asp:BoundField DataField="Asset" HeaderText="Asset" ReadOnly="True" 
                SortExpression="Asset" />
            <asp:BoundField DataField="Location" HeaderText="Location" 
                SortExpression="Location" />
            <asp:BoundField DataField="Block" HeaderText="Block" SortExpression="Block" />
            <asp:BoundField DataField="Room" HeaderText="Room" SortExpression="Room" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:IWSRiskAssessmentConnectionString %>" 
        SelectCommand="SELECT tblCompany.compName, tblSite.siteName AS [Site Name], tblSite.siteUPRN AS [Reference Number], tblIncMain.incAsset + ' ' + CAST(tblIncMain.incNumber AS varchar) AS Asset, tblIncMain.incLocation AS Location, tblIncMain.incBlock AS Block, tblIncMain.incRoom AS Room FROM tblIncMain INNER JOIN tblSite ON tblIncMain.incSite = tblSite.siteID INNER JOIN tblCompany ON tblSite.siteCompany = tblCompany.compDataID WHERE (tblIncMain.incActive = 1) AND (tblSite.siteActive = 1) AND (tblIncMain.incRemoved = 0) AND (tblCompany.compDataID = @compDataID) ORDER BY [Site Name], tblIncMain.incAsset, tblIncMain.incNumber">
        <SelectParameters>
            <asp:ControlParameter ControlID="ListBox1" Name="compDataID" 
                PropertyName="SelectedValue" />
        </SelectParameters>
    </asp:SqlDataSource>

this is the source code :-) please take a look and any help is greatly appreciated

May be DataBind code executes on every request or you are not collecting selected items from the listbox.

  Sub page_load()
     if Not IsPostBack Then

     End If
   End sub

and to iterate Listbox items,

For Each item in ListBox1.Items
  if item.Selected then
  End If
Next

Your data source is only selecting 1 record. The ListBox will return the first value when you use a multiple select ListBox as a ControlParameter.

(tblCompany.compDataID = @compDataID)

What you want to do is have something with an IN statement such as

(tblCompany.compDataID in @compData) 

You may need to do something in the code behind.

<asp:ListBox ID="ListBox1" runat="server" 
        AutoPostBack="True"
        DataTextField="compName" 
        DataSourceID="SqlDataSource11"
        DataValueField="compDataID"
        OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"
        SelectionMode="Multiple">
</asp:ListBox>

Code behind

protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
  string s = string.Empty;
  foreach (ListItem li in ListBox1.Items)
  {
    if (li.Selected == true)
      s += li.Value + ",";
  }

  if (s != string.Empty)
  {
    s = s.Substring(0, s.Length - 2); // chop off trailing ,
    SqlDataSource2.SelectParameters["compData"].DefaultValue = s;
  }  
}

NOTE this hasn't been tested but it is one option you can try. Basically you want a IN not a = for your select to get all results.

ok so i added 'in' to my sql statement, however it still only picked up one selection from the list box instead of multiple this is the sql statement:

SELECT tblCompany.compName, tblSite.siteName AS [Site Name], tblSite.siteUPRN AS [Reference Number], tblIncMain.incAsset + " " + CAST(tblIncMain.incNumber AS varchar) AS Asset, tblIncMain.incLocation AS Location, tblIncMain.incBlock AS Block, tblIncMain.incRoom AS Room FROM tblIncMain INNER JOIN tblSite ON tblIncMain.incSite = tblSite.siteID INNER JOIN tblCompany ON tblSite.siteCompany = tblCompany.compDataID WHERE (tblIncMain.incActive = 1) AND (tblSite.siteActive = 1) AND (tblIncMain.incRemoved = 0) AND tblCompany.compDataID in(@compDataID) ORDER BY [Site Name], tblIncMain.incAsset, tblIncMain.incNumber