Using a listbox to declare a parameter in my sql statement

Question

I have a list box that allows multiple values to be selected.

Here is my query for my gridview

saocmd.CommandText = "SELECT B603SalesAsOFMASTER.SDESCR, B603SalesAsOFMASTER.DYYYY, B603SalesAsOFMASTER.AsOFSales, B603SalesAsOFMASTER.ASOFPAX, B603SalesAsOFMASTER.YESales, B603SalesAsOFMASTER.YEPAX, B603SalesAsOFMASTER.PCTofSales, B601SalesAsOF.Sales AS CurrentSales, B601SalesAsOF.PAX AS CurrentPAX FROM B603SalesAsOFMASTER INNER JOIN B601SalesAsOF ON B603SalesAsOFMASTER.SDESCR = B601SalesAsOF.SDESCR WHERE (B603SalesAsOFMASTER.DYYYY =@Dyyyy) AND (B601SalesAsOF.DYYYY = (year( getdate() ))) and B603SalesAsOFMASTER.SDESCR in (@regions)order by B603SalesAsOFMASTER.SDESCR"

Here is my query for my listbox

listcmd.CommandText = "SELECT distinct B603SalesAsOFMASTER.SDESCR FROM B603SalesAsOFMASTER"

I want the user to select all the regions they want to query in the gridview.

as of now i am putting each selected list item into a textbox

Function list()
    Dim li As ListItem
       For Each li In ListBox1.Items
         If li.Selected Then
            TextBox1.Text &= "'" & li.Text & "' ," & vbCrLf
       End If
Next
End Function

then before the query is run i use to subtract the final , so i dont get an error and i have to use textbox1.text in my query and not the parameter. that is slower and sql injection, i need help doing this a better way thanks

If TextBox1.Text.EndsWith(",") Then
        TextBox1.Text = TextBox1.Text.Substring(0, TextBox1.Text.Length - 1)
            End If

You can't use your comma separated list as parameter for your select-command. You will have to write a table-valued-function that iterates your parameters in your database(MS-SQL-Server?): sommarskog.se/arrays-in-sql-2005.html#CSV — Tim Schmelter
– Tim Schmelter, Commented Dec 23, 2010 at 15:53
so a stored procedure is inevitable, haha i have never used one — MyHeadHurts
– MyHeadHurts, Commented Dec 23, 2010 at 16:00
If you want no sql-injection, yes. Also i would recommend not to use the Region-Text but the ID of the Region for the select-command. It is essential faster to select a primary key than a varchar column. — Tim Schmelter
– Tim Schmelter, Commented Dec 23, 2010 at 16:03

Tim Schmelter · Accepted Answer · 2010-12-23 16:01:15Z

1

You can't use your comma separated list as parameter for your select-command. You will have to write a table-valued-function that iterates your parameters in your database(MS-SQL-Server?): Comma-separated List of Values

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
   RETURN
END

answered Dec 23, 2010 at 16:01

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MyHeadHurts Over a year ago

thanks so this function would go into my stored procedure, or is this a function in my database that i can call in my sql statements

MyHeadHurts Over a year ago

i know its a dumb question, but im very new at dealing with databases

Tim Schmelter Over a year ago

This is a table-value-function. It returns a Table representation of your comma separated list. So you can query and join it like a normal table from everywhere. You should take the time to learn it ;-)

MyHeadHurts Over a year ago

o so thats awesome, i can use this in my in statement of my query, but how do i relate my listbox to it

Collectives™ on Stack Overflow

Using a listbox to declare a parameter in my sql statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related