code SQL to retrieve records from multiple random selection list box RSS

• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

  code SQL to retrieve records from multiple random selection list box

  Oct 29, 2010 06:35 PM|LINK

  Having problems coding a query.

  Got a list box that allows for multiple selections (holding down the control key) the selection quantity is basically unlimited, but should never be more than 50 to 100 items from the listing of about 3000 items.

  How can I code the SQL statement to select the choosen record data? I think I have the selection list worked out by populating an array but I am not clear on how to build the SQL query.

  Any help would be appreciated.

   

  Thanks again

• • Reply
  

  rajum

  Member

  477 Points

  86 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Oct 29, 2010 08:11 PM|LINK

  Check this link, It will help you to select multiple values in a WHERE clause.

  http://www.w3schools.com/sql/sql_in.asp
  

  SELECT column_name(s)
  FROM table_name
  WHERE column_name IN (value1,value2,...)

  
  
  

• • Reply
  

  TabAlleman

  All-Star

  15557 Points

  2698 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Oct 29, 2010 08:14 PM|LINK

  Instead of an array, if you can convert your selection list to a comma-separated string, you can supply it as a single parameter to a stored procedure.   

  The procedure would build a dynamic sql string and execute it.

  You would plug in the parameter like this:

  '...WHERE [SomeColumn] IN (' + @MyParameter + ')'

  keep in mind that if your column is character data, as opposed to numeric, each value will have to be delimited with single-quotes.

  -Tab Alleman
  • Marked as answer by march11 on Oct 30, 2010 10:44 PM
• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Oct 30, 2010 10:51 PM|LINK

   

  I populate my Listbox with this SQL stmt: 

   SELECT keyID, itemCode, itemUPCID FROM item ORDER BY itemCode

  <asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="itemUPCID" DataValueField="keyID" Height="176px" SelectionMode="Multiple" Width="178px"></asp:ListBox>

   

  I collect the values selected in this statement in VB code behind:

  numSelected = ListBox1.GetSelectedIndices()    ' Index of items selected in box

  
  Now I would like to build the SQL to return all the fields for each record selected from the listbox.

   

  I have no problem using an SP to execute the query just not sure how to pass the string. 

   

  Hope this helps explain it better..

  • Edited by march11 on Nov 01, 2010 05:50 PM
• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Nov 01, 2010 08:10 PM|LINK

  I have gathered my items from the listbox like this....

  Dim li As ListItem
  
  
          For Each li In ListBox1.Items
              If li.Selected = True Then
                  If myString = "" Then
                      myString = li.Value
                  Else
                      myString = myString & ", " & li.Value
                  End If
              End If
          Next

  Now I need to pass this to the Stored Procedure.

  The Value is the key from my items table. So I am looking at a SQL statement like this.

   

  SELECT * FROM item WHERE keyID IN ('myString')

   

• • Reply
  

  TabAlleman

  All-Star

  15557 Points

  2698 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Nov 01, 2010 08:21 PM|LINK

  Yes, exactly. 

  -Tab Alleman
• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Nov 02, 2010 02:28 PM|LINK

  I am passing the string from my code behind asp page to a stored proedure.
  
  
  There is only one table that I need to return records from, 'items'. 'myString' contains a list of comma delimited primary KeyIds that is collected from the aspx page.

  Can I pass the comma delimited string directly to the SP for execution in the SELECT stmt in the SP?
  
  And if not, how can I go about converting the 'mySting' variable to a clause that can be used in the WHERE section of the query? 
  
  I am reading 'mySting' in the SP with the follwoing varialble...
  
  
    
  	@keyIds varchar(200) 
    
  SELECT id FROM items WHERE keyID = (@keyIDS)
   
  
  

  
   

• • Reply
  

  TabAlleman

  All-Star

  15557 Points

  2698 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Nov 02, 2010 02:47 PM|LINK

  Instead of keyID = (@keyIDS),

  it should be 'keyID IN (' + @keyIDS +')'

  (make sure you are using dynamic sql to create and execute your query inside the proc.)

  -Tab Alleman
• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Nov 02, 2010 03:00 PM|LINK

  This is the SP..

  @keyIds varchar(300)
  
  
  
  
    
  
  AS
  SET NOCOUNT ON
    
  
  SELECT  *
  FROM    item AS s
  WHERE   keyID IN ('+ @keyIds +');
  RETURN

  
  it throws the following error....

  Running [dbo].[usp_itemReport] ( @keyIds = 1, 2 , 11 ).

  Conversion failed when converting the varchar value '+ @keyIds +' to data type int.

  Conversion failed when converting the varchar value '+ @keyIds +' to data type int.

  keyID

   

• • Reply
  

  TabAlleman

  All-Star

  15557 Points

  2698 Posts

  Re: code SQL to retrieve records from multiple random selection list box

  Nov 02, 2010 03:07 PM|LINK

  AS
  SET NOCOUNT ON;
  
  
  DECLARE @sql varchar(max);
  
  
  SET @sql = 'SELECT  *
  FROM    item AS s
  WHERE   keyID IN ('+ @keyIds +')';
  
  
  execute @sql;
  
  
  RETURN

  
   

  -Tab Alleman
  • Marked as answer by march11 on Nov 04, 2010 01:46 PM