Asp.net Forum

Ask Question   UnAnswered
Home » Forum » Asp.net       RSS Feeds

Select Parameter query driven by listbox

  Asked By: Ruben    Date: Jul 27    Category: Asp.net    Views: 1422

I want to use a multiselect listbox to choose multiple states as
criteria for selecting records with a sql server stored procedure.

I'm able to loop through the Listitems to obtain the desired key
values and build a query string, but have not found a syntax that
works to pass multiple values of the same parameter to the stored
procedure. Clearly things like:

MyCommand.SelectCommand.Parameters.Add(New SqlParameter
("@PerStateorProv",SQLDbType.real, 4))

("@PerStateorProv").value = 122 OR MyCommand.SelectCommand.Parameters
("@PerStateorProv").value = 135

do not work, nor does

MyCommand.SelectCommand.Parameters.Add(New SqlParameter
("@PerStateorProv",SQLDbType.real, 4))
("@PerStateorProv").value = 122 OR 135 .

Any ideas how to fix this?



4 Answers Found

Answer #1    Answered By: Darrell Harvey     Answered On: Jul 27

Have you tried concatenating you SQL string:

strSQL = "select from ... Where "
strSQL = strSQL & "(fieldForState = '" & 1stselState & "')"
strSQL = strSQL & " AND "(fieldForState = '" & 1stselState & "')"
strSQL = strSQL & " AND "(fieldForState = '" & 1stselState & "')"

Answer #2    Answered By: Bethany Hughes     Answered On: Jul 27

How about calling the stored  procedure within the
loop, passing one value in each iteration.

Answer #3    Answered By: Herbert Weaver     Answered On: Jul 27

Yes, that works fine when I use a select  statement based on a table.
What I'd like to do is use a stored  proc that has no criteria then
use a dynamically generated where clause. I can do this in Access
but can't get it to work with SQL Server. Something like:

strSQL = "SELECT * FROM MyStoredProc WHERE (tblPerson.State = " &"'"&
cboState.value &"')"

Answer #4    Answered By: Adalric Fischer     Answered On: Jul 27

Another solution for doing this is to batch your state IDs up and passing
them to the stored  proc in one call. You can do this by changing your data
type from INT (or real) to VARCHAR(1000) -- or some size other than 1000
that would be large enough to handle all your possible selections. Then, in
your stored proc you can do something like...

CREATE procedure  sp_Blah
@SomeIntParam INT,
@InputParam VARCHAR(1000)

SET @sSQL = '
DELETE FROM tblYourTable
WHERE SomeIntField = ' + cast(@SomeIntParam as varchar) + '
and StateField in (' + @InputParam + ')


--- I added another INT field to the stored proc param list to show you that
you'll need to cast all your variables to varchar when building this query
-- but since @InputParam already is -- it's good to go w/out a cast. This
is how we handle all situations where we need to pass multiple  params in -
but only want to execute one call. Only caveat is that you may exceed your
variable size limits -- for example @sSQL will truncate if your input params
build the string  over 4000 characters - so you may want to do some input
validation in the proc - or before you call it.

Didn't find what you were looking for? Find more on Select Parameter query driven by listbox Or get search suggestion and latest updates.