Passing Multiple ListBox Values to WHERE clause 1

[xcaliber2222]

Hello, I have list box values than I'm going to multi select like so:

 Dim cmdServiceLevel As New SqlCommand
        Dim dtrServiceLevel As SqlDataReader
        strSQL = "SELECT distinct s.service_id, '(' + s.service_id + ') ' + s.name as name " & _
                 "FROM service_class s " & _
                 "ORDER BY service_id"

        cmdServiceLevel = New SqlCommand(strSQL, ncsConn)
        dtrServiceLevel = cmdServiceLevel.ExecuteReader()

        Me.lstService.DataSource = dtrServiceLevel
        Me.lstService.DataValueField = "service_id"
        Me.lstService.DataTextField = "name"
        Me.lstService.DataBind()
        Me.lstService.Items.Insert(0, New ListItem("All", 0))

        dtrServiceLevel.Close()

and pass to by next page with other values like so:

Response.Redirect("test.aspx?clientid=" & Me.ddlCust.SelectedItem.Value & "&siteid=" & Me.lstService.SelectedItem.Value & "&service_id=" & Me.ddlSite.SelectedItem.Value & "&datefrom=" & Me.txtDateFrom.Text & "&dateto=" & Me.txtDateTo.Text)

finally, I'm going to pass this to my report page where I will build my where clause (for the big strSQL not shown here):

Private Sub LoadGrid()
        Dim strSQL As String
        Dim cmd As New SqlCommand()
        Dim cmdAvg As New SqlCommand
        Dim dtr As SqlDataReader
        Dim strWhere As String = ""
        If Me.strClient <> "0" Then
            strWhere += "AND test.invclient_id = @intClientID "
        End If
        If Me.strSite <> "0" Then
            strWhere += "AND test.invsite_id = @intSiteID "
        End If
        If Me.strServiceLevel <> "All" Then
            strWhere += "AND job.service_id IN @strServiceID " 'LOOP THROUGH TO BUILD WHERE CLAUSE
        End If

also, I am adding parms like so:

 If Me.strServiceLevel <> "ALL" Then
            cmd.Parameters.Add(New SqlParameter("@strServiceID", SqlDbType.Char, 6))
            cmd.Parameters("@strServiceID").Value = Me.strServiceLevel
        End If

I know I will need to loop through the values to build the "IN" clause in my WHERE statement, doing something like this:

For Each li in lsbEmployee.Items
      If li.Selected Then
        strWhereClause &= "EmployeeID=" & li.Value & " Or "
      End If
    Next
    If strWhereClause.Length > 0 Then
      dtgEmployee.Visible = True
      'Chop off last " Or "
      strWhereClause = Left(strWhereClause, strWhereClause.Length() - 4)
      strWhereClause = "WHERE " & strWhereClause
      Dim strSql = "Select FirstName, LastName, Country, Region, City " _
                 & "From Employees " & strWhereClause & " Order By LastName"
      Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
      Dim objCmd As New SqlCommand(strSql, objConn)
      Try
        objConn.Open()
        dtgEmployee.DataSource = objCmd.ExecuteReader()
        dtgEmployee.DataBind()
      Catch exc As SqlException
        Response.Write(exc.ToString())
      Finally
        objConn.Dispose()
      End Try
    Else
      dtgEmployee.Visible = False
    End If

But, the above is a sample I've seen of getting the mtuli-selected values into a WHERE clause, but doesn't there need to be an "IN" statement, and is there a simpler approach?

Unfortunately I'm limited to my approach except for the last part of building the WHERE clause. Hope this makes sense, any help (examples, suggestions, article references) would be greatly appreciated.

Thanks,
Alejandro

 

[HarleyQuinn]

One small way to cut down on time used is to iterate the lsbEmployee.SelectedItems collection rather than the Items collection (so you don't have to go through every item in the list). Something like:

For Each li as String in lsbEmployee.SelectedItems
        strWhereClause &= "EmployeeID=" & li & " Or "
    Next

Hope that helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com