0

I'm trying to develop a Webform where the user can filter and update SQL Server tables through queries with multiple values.

How it works

The user enters multiple values where each value is separated by a space on a textbox. Once all values are given the user clicks the execute button which replaces all the spaces with a comma and assigns the values to a single string variable. Then the query will filter for all the values contained within that variable.

Problem

The program insert the variables into the query as: 1000000,1000001.
I also made a manual test with as follows: '1000000','1000001'.

But for both attempts I get the following error:

Incorrect syntax near '1000000'

Question

How do I correctly pass multiple values within a variable in a query?

My code:

Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
    Dim testString As String = ArticleTextbox.Text
    Dim arrayOfTests As String() = testString.Split(New String() {" "}, StringSplitOptions.RemoveEmptyEntries)
    Dim newString As String = String.Join(", ", arrayOfTests)
    Label2.Text = newString

    'SQL.AddParam("@StyleID ", newString)

    Try
        dt = SQL.ExecQuery("Select STYLE_ID from ItemWebCategory where STYLE_ID in " & newString & "")
        'dt = SQL.ExecQuery("Select STYLE_ID from ItemWebCategory where STYLE_ID in @StyleID")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    GridView1.DataSource = dt
    GridView1.DataBind()
End Sub

SqlControl class:

Public Class SQLControl
    Private ReadOnly ConStr As String = "connection String "
    Private DBCmd As SqlCommand

    'Query Parameters
    Public Params As New List(Of SqlParameter)

    'This generates a blank sqlclient class with the deafult connection string
    Public Sub New()
    End Sub

    'Allow connection string override
    Public Sub New(connectionString As String)
        ConStr = connectionString
    End Sub

    'Execute Query Sub
    Public Function ExecQuery(query As String) As DataTable

        Dim DBDT = New DataTable
        Using DBCon As New SqlConnection(ConStr),
                DBCmd As New SqlCommand(query, DBCon)
            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
            Params.Clear()
            DBCon.Open()
            DBDT.Load(DBCmd.ExecuteReader)
        End Using
        Return DBDT
    End Function

    'Add variable as Paramerized objects 
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New SqlParameter(Name, Value)
        Params.Add(NewParam)
    End Sub
End Class
GSerg
  • 76,472
  • 17
  • 159
  • 346
darknight
  • 35
  • 7
  • 3
    The IN clause is followed by an opening parenthesis, the list of values and a closing parenthesis – Steve Feb 24 '21 at 18:39
  • 2
    Also you cannot parameterize the whole string, you need to parameterize each value – Steve Feb 24 '21 at 18:40
  • 2
    To expound on @Steve's comment, the proper format for an `IN` clause is `IN (1, 2, 3, 4)`. You forgot to add the parentheses around the list of values. Also know that your query as written is highly vulnerable to SQL injection. – Ken White Feb 24 '21 at 18:47
  • 2
    @KenWhite right, but with the splitting and then joining I think that is very difficult or impossible to mount an Sql Injection. However I would take different design decisions for a task like this. For example show the item description in a combo with a button to add the item id to an internal string avoiding the input of the ids directly from the user – Steve Feb 24 '21 at 18:57
  • @Steve you're kidding wrt. injection, right? The splitter is only removing space characters (u+0020) which leaves a whole lot of whitespace characters available to delimit tokens. – AlwaysLearning Feb 24 '21 at 21:36
  • Does this answer your question? [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – GSerg Jun 09 '23 at 13:11

2 Answers2

0

The question is old but maybe it helps someone else.

I do it like this with parameters.

Dim ListOfExisting As New List(Of Integer)
ListOfExisting.Add(1)
ListOfExisting.Add(2)
ListOfExisting.Add(3)
ListOfExisting.Add(4)

Dim ParameterString As String = Nothing
Dim SQLDelete As New SqlCommand
For Each element In ListOfExisting
    ParameterString = "@Elm" & element.ToString & "," & ParameterString
    SQLDelete.Parameters.AddWithValue($"@Elm{element.ToString}", element)
Next
ParameterString = ParameterString.Substring(0, ParameterString.Length - 1)

SQLDelete.CommandText = $"delete from MyTable where ID not in ({ParameterString})"
Roli
  • 1
  • 5
-1

If you are wanting to insert multiple rows into a table via query you will have to execute a INSERT INTO query multiple times, e.g.:

For each dr as DataRow In SomeList.Rows
ExecQuery("INSERT INTO Table1 VALUES '" & dr("value1").ToString.Trim & "'")
Next

Loop the above example query for however many values you wish to enter into a table.

Pav
  • 15
  • 5