3 Replies - 4996 Views - Last Post: 08 July 2010 - 01:42 AM Rate Topic: -----

#1 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

datasource - multiple listbox parameters & select command

Posted 07 July 2010 - 06:09 AM

Is it possible to loop a select command and pass through several parameters from one list box, for example, if a user wanted to display all tables with firstName 'john' or 'joe' or 'ed' so they highlighted 'john', 'joe' and 'ed' for selection. Is there anyway I can do this without changing to a different sqldatasource?

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
             ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [FamilyDetails] WHERE ([firstName] = @firstName)" 
">
             <SelectParameters>
                 <asp:ControlParameter ControlID="ListBox_Names" Name="firstName" 
                     PropertyName="SelectedValue" Type="Int32" />
             </SelectParameters>




Is This A Good Question/Topic? 0
  • +

Replies To: datasource - multiple listbox parameters & select command

#2 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: datasource - multiple listbox parameters & select command

Posted 07 July 2010 - 07:16 AM

Ok i've looped in codebehind and created a string with the values I want to insert into my query. Ive assigned these to a hidden label. How can I access the label as a control parameter? I tried the same way as above but grid shows up blank.

query is

SelectCommand="SELECT * FROM [FamilyDetails] WHERE [firstName] IN (@firstName)"

This post has been edited by irishgirl: 07 July 2010 - 07:17 AM

Was This Post Helpful? 0
  • +
  • -

#3 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: datasource - multiple listbox parameters & select command

Posted 07 July 2010 - 08:02 AM

I'm concatenating a string in codebehind to use in a sql select statement.

in aspx


<asp:Label ID="LabelHidden" runat="server" Visible="False"></asp:Label>

..

SelectCommand="SELECT * FROM [FamilyDetails] WHERE [firstName] IN (@firstName) AND ([ID] = @ID)"

<SelectParameters>
                 <asp:ControlParameter ControlID="DropDownList_ID" Name="ID"
                     PropertyName="SelectedValue" Type="Int32" />
                     <asp:ControlParameter ControlID="LabelHidden" Name="firstName"
                     Type="String" />
             </SelectParameters>




in codebehind


foreach (ListItem li in ListBox_Names.Items)
                    {

                        if (li.Selected)
                        {
                            
                            queryBuilder += li + "', ";

                        }

          queryBuilder = queryBuilder.Substring(0, queryBuilder.Length - 1);
                    LabelHidden.Text = queryBuilder;




When i run the code it comes up blank. I did a query trace and it seems to be running as


exec sp_executesql N'SELECT * FROM [FamilyDetails] WHERE [firstName] IN (@firstName) AND ([ID] = @ID)',N'@ID int,@firstName nvarchar(50)',@ID=4,@firstName=N'Jay'', John'','




with exta "'s

How can i format the queryBuilder string correctly in my code?
Was This Post Helpful? 0
  • +
  • -

#4 megglz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: datasource - multiple listbox parameters & select command

Posted 08 July 2010 - 01:42 AM

I've been debugging and I think my problem is just the sql query part of

Quote

WHERE [ActivityName] in (@ActivityName)


but why wont it work in the sqldatasource, yet it works manually in sql management studio?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1