How can I pass more than one parameter from a multiselect listbox to SQL

  • Thread starter Thread starter Jeff Thur
  • Start date Start date
J

Jeff Thur

I have a listbox with all the US States. I need the user
to select multiple States from that listbox and pass them
on as parameters to a SQL Stored Procedure that will give
them a count of all the records in the Database that
apply to those States. I realize That an array must be
created, but I am not sure how to go about this. Any help
would be greatly appreciated.

Thanks...........
 
I've done something similar using a delimted string... If your list is
databound and has both a DisplayMember and a ValueMember, say your data looks
like this:

StateID(Value) StateName(Display)
AK Alaska
AL Alabama
AR Arkansas

User selects Alaska and Arkansas, in SelectedIndexChanged you iterate
through your list.SelectedItems property and build a string 'AK','AR' pass
this to your stored procedure to be used like so: where stateCode in
('AK','AR'), there is one problem with this, T-SQL will not allow you to
execute a statement using declared variables, the tendancy is to try and do
something like this where stateCode in (@inParamStateCodes), you actually
have to do build a string and execute it using sp_executesql the result would
look like this...

CREATE PROC esp_test
@inParamStateCodes VARCHAR(4000)
AS
BEGIN

DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT SOMETHING WHERE stateCode IN (' + @inParamStateCodes + ')'

EXEC sp_executesql @sql
END
 
Back
Top