• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

List of IDs for Where In Clause

Hi there,
I have searched and found a few similar questions but the answers are confusing. I have a dataset that I need to pass a list of ID's. I can make it work with SQL Server with just 1,2,3 but even previewing data in the Dataset and using 1,2,3 as the param value fails

Select * from TableA
Where ID in (@IdList)

I am using VS2008 and VB.Net.
I can not use Stored Procs

Any help would be great!
Jase
0
Jorell
Asked:
Jorell
  • 3
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
one option is to use a dynamic sql

exec ('Select * from TableA Where ID in ('+@IdList+ ')'  )
0
 
JorellAuthor Commented:
How can you do that using a Dataset Table adapter??
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i am not sure abt that part.. may be some other expert will help you
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Kevin CrossChief Technology OfficerCommented:
Okay without stored procedures, you can try using a split function as described here - http:Q_23786715.html - or search site for dbo.ParmsToList written by angel eyes.  There is even a few articles that may be helpful.  
Usage would be:
Select * from TableA Where ID in (select entry from dbo.split(@IdList, ',' ));
0
 
JorellAuthor Commented:
I know how to create a dynamic SQL statement. I need to pass the list as a param to a Dataset:

I.E. tableadapter.fill(dataset, IDLIST)
0
 
Kevin CrossChief Technology OfficerCommented:
My solution wasn't using dynamic SQL, so you could basically setup a normal SqlCommand that takes a parameter.  The code utilizes the split function to take the comma delimited string and create a table inline to use in the IN statement to avoid the need for dynamic SQL.
0
 
JorellAuthor Commented:
Maybe I am missing something, how would I utilize the Typed Dataset and the Datagridview that is associated with that typed dataset using your method?
0
 
Kevin CrossChief Technology OfficerCommented:
Jorell,

Sorry I ended up leaving work early yesterday and I just got back to this today.  I tested in Visual Studio just to make sure I wasn't saying something incorrectly, but if you open the DataSet in the designer you should be able to click configure on the image of TableA which should take you to the TableAdapter Configuration Wizard.

In that screen, you can enter SQL directly or use Query Builder.

You would paste the query I gave you:

Select * from TableA Where ID in (select entry from dbo.split(@IdList, ',' ));

(assumes that you have created the dbo.split function on your SQL server)

Click finish and you will see that the GetData now shows as GetData(@IdList).

Then in the form load (at least that is where I did it on my simple project), you can fill the data adapter using .Fill() passing the variable.

For example, I did mine with bookings data and a date parameter.  So my DataSet is in a file DataSet1.xsd and table is "bookings" with bookingsTableAdapter query:

SELECT     bk_id, bk_date, bk_quantity, bk_amount, bk_region, bk_salesperson, bk_customer
FROM         bookings
WHERE     (bk_date >= @date)

In design view, I added Typed DataSet to my form and left default naming of DataSet11.  Then I chose for the data source of the DataGridView the bookings table of the DataSet11BindingSource, etc.  Hopefully you have all this already and so are following along.  This creates a BookingsTableAdapter within the form, so my .Fill() looks like this:

Me.BookingsTableAdapter.Fill(Me.DataSet11.bookings, Now.AddYears(-5))

All compiles and I get my bookings that are newer than 5 years ago. :)

Hope that helps.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now