CODEWITHSUNDEEP
asp.netvb.netdataset
MissCoder87
MissCoder87

Reputation: 2669

Use SQL IN inside vb.net dataset adaptor

I'm trying to use a dataset adapter in my asp.net/vb.net website. But I need to search using multiple values, Ideally, i'd just like to pass an in statement in to my dataset adapter. Is this possible, as how it is, it doesn't seem to bring back anything:

Heres my data adapter:

enter image description here

The query is:

select * from joblist where username in (@username)

I'm trying to call it with:

Private dtJobs As Barry.joblistDataTable
Private taJobs As New BarryTableAdapters.joblistTableAdapter

  dtJobs = New Barry.joblistDataTable
  dtJobs = taJobs.FilterSearch("'tomb','martinm','chrise'")

rptJobList.DataSource = dtJobs
rptJobList.DataBind()

What am I doing wrong?

Upvotes: 0

Views: 355

Answers (1)

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

You're passing in a comma separated string, a single parameter. You cannot use it as is in an IN statement.

You have to split it into table, for example using this solution. Using this function (I assume you're using SQL Server as your DB) you can rewrite your query as:

select joblist.* from joblist 
inner join dbo.Split(',', @username) t
on joblist.username = t.s

This approach splits your multi-value parameter into table and joins to the original table

Upvotes: 1

Related Questions