Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Nov 04, 2010 01:50 PM by march11
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.
Oct 29, 2010 08:11 PM|LINK
Check this link, It will help you to select multiple values in a WHERE clause.
WHERE column_name IN (value1,value2,...)
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.
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..
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
myString = myString & ", " & li.Value
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')
Nov 01, 2010 08:21 PM|LINK
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...
SELECT id FROM items WHERE keyID = (@keyIDS)
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.)
Nov 02, 2010 03:00 PM|LINK
This is the SP..
SET NOCOUNT ON
FROM item AS s
WHERE keyID IN ('+ @keyIds +');
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.
Nov 02, 2010 03:07 PM|LINK
SET NOCOUNT ON;
DECLARE @sql varchar(max);
SET @sql = 'SELECT *
FROM item AS s
WHERE keyID IN ('+ @keyIds +')';