Parameterized Query Problem 1

[xcaliber2222]

Hello, I have a query which I am adding a service_id parameter to like so:

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

I am passing it from my web page and adding it as an IN statement to the WHERE clause like so:

'''From LAUNCH page code behind:
Private Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        If Me.ddlSite.SelectedIndex = 0 And Me.ddlCust.SelectedIndex = 0 Then
            Me.lblMessage.Text = "You must select a Client or Site."
            Exit Sub
        End If

        If Me.ddlSite.SelectedIndex <> 0 And Me.ddlCust.SelectedIndex <> 0 Then
           
            Dim cmd As New SqlCommand()
            Dim intcount As Integer
            strSQL = "SELECT count(invloc_id)" & _
                    "FROM inv2_location s " & _
                    "WHERE s.inactive_date IS NULL " & _
                    "AND s.invclient_id = " & Me.ddlCust.SelectedValue & _
                    "AND s.invsite_id = " & Me.ddlSite.SelectedValue
            cmd = New SqlCommand(strSQL, ncsConn)
            intcount = cmd.ExecuteScalar

            If intcount = 0 Then
                Me.lblMessage.Text = "There are no matches for this site and client."
                Exit Sub
            End If
        End If

        Dim li As ListItem
        For Each li In lstService.Items
            If li.Selected = True Then
                ListItemSelected = ListItemSelected & "'" & li.Value & "',"
            End If
        Next

        ListItemSelected = "(" & Left(ListItemSelected, Len(ListItemSelected) - 1) & ")"

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

'''From REPORT page code behind:
 Private Sub LoadGrid()
        Dim strSQL As String
        Dim cmd As New SqlCommand()
        Dim cmdAvg As New SqlCommand
        Dim dtr As SqlDataReader
        Dim dtrAvg As SqlDataReader
        Dim strWhere As String = ""

        If Me.strClient <> "0" Then
            strWhere += "AND inv2_itemdtl.invclient_id = @intClientID "
        End If
        If Me.strServiceLevel <> "0" Then
            strWhere += "AND job.service_id IN @strServiceID "
        End If

...adding it to the strWhere in my SQL statement and finally adding the parameter:

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

I keep getting a syntax error of the @strServiceID. There's no way to visualize the actual values in Visual Studio (I see "syntax error on @strServiceID" but not the actual values. However, I was able to run a SQL trace and see this (snippet from longer SQL string):

@intSiteID = 442, @strServiceID = '(''H3P'')', @dtStart = 'Jan  1 2008 12:00AM', @dtEnd = 'Nov  1 2008 11:59PM'

I've tried different things, including not adding single quotes on my own, thinking that this is done for me like what seems to be done automatically with the date types. I've tried various SqlDbTypes (Text, Char, NVarChar), but I cannot figure out how to get rid of that extra single quote. I'm assuming that it's the single quote that appears on the outside of the parenthesis because if I'm correct the trace profiler puts single quotes around the actual values anyway, in other words, '(''H3P'')' would be ('H3P') outside of the profiler. Is this a correct assumption. I've been pulling what little hair I have left out on this one so if someone could put me out of my misery and give me a clue on this I would really appreciate it.

I hope this all makes sense but am more than happy to clarify this if someone wants to help.

Thanks,
Alejandro

 

[RiverGuy]

With your IN clause, you should wrap the values in parentheses:

AND job.service_id IN (@strServiceID)

However, people normally use an IN clause to test against a list of values. If your @strServiceID variable contains something along the lines of 'Value1, Value2, Value3', as far as I know, SqlClient will not rewrite your query as you are expecting. So instead of submitting the following query to the server:

SELECT * FROM TableName WHERE job.service_id IN ('Value1', 'Value2', 'Value3')

, you are going to end up with something like

SELECT * FROM TableName WHERE job.service_id IN ('Value1, Value2, Value3')

If you need to submit a list of values to SQL Server, I suggest you do a Google Search for "SQL Server Split Function." This will allow you to incorporate code to pass in a delimited list of values to use as separate values for your IN clause.

 

[xcaliber2222]

Thank you RiverGuy. Much obliged.