[SOLVED]multiple values by an argument to a same column using in SQL operator is not working

Question

1.00/5 (1 vote)

See more:

SELECT * FROM [M_Details] where Dcat IN (@Cats) if I give like this in dataset table adapter in vb.net 2010, multiple input values are not filtering. Suppose I pass one argument and then it's working. But if I pass by an argument with more than one value, then it's not working.

What I have tried:

SELECT * FROM [M_Details] where Dcat IN ('Home','Shop')
This direct code with values is working. But I need to pass by user input category values. Please Help me. Thanks in advance.

Posted 18-Oct-22 9:33am

Member 12254178

Updated 22-Oct-22 7:03am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2022-10-18T10:25:00

Solution 1

I'd suggest to read this excellent tip: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 18-Oct-22 10:25am

Maciej Los

Member 12254178 · Answer 2 · 2022-10-22T06:57:00

I found this solution for multiple word search to a same column in code.
Paste this code as Function. and call in to textbox control for required inputs.

Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox.TextChanged

'multi words search inputs
Me.DataGridView.DataSource = colMultiSearch(TextBox.Text, "yourColumn")

End Sub

Private Function colMultiSearch(ByVal strFindwords As String, ByVal strColumnName As String) As DataTable

Dim selitems() As String
Erase selitems

'multi input words will be converts into array and separted with comma.
selitems = Split(strFindwords, ",")

'check database connection if success from database module
If OpenDB() = True Then

Using cmd As SQLiteCommand = New SQLiteCommand

Dim paraName As String
Dim indx As Integer = 0

'if blank input then show all data of input column
If strFindwords = "" Then

Dim mySearchQuery1 As String = "SELECT * FROM yourtable WHERE trim(" & strColumnName & ") LIKE '%'"

'show all data query
cmd.CommandText = (mySearchQuery1)

Else
'multiple word search query
Dim mySearchQuery As New StringBuilder("Select * from yourtable")
'column name input here
mySearchQuery.Append(" WHERE trim(" & strColumnName & ") IN (")

For Each myWord As String In selitems

'define column parameter input here
'multi parameters will be adding here automatically by number of word search
paraName = "@column" & indx

'multi parameters will be separted by comma
If indx > 0 Then mySearchQuery.Append(", ")

mySearchQuery.Append(paraName)

cmd.Parameters.AddWithValue(paraName, Trim(myWord))
'Console.WriteLine(CatChk)

'parameter input count
indx += 1
Next
mySearchQuery.Append(")")

'multiple word search output
cmd.CommandText = (mySearchQuery.ToString)
End If

cmd.Connection = Conn

'final data evaluation
Using sda As SQLiteDataAdapter = New SQLiteDataAdapter(cmd)
Dim dt As DataTable = New DataTable
sda.Fill(dt)
Return dt
End Using

End Using
End If

End Function