TableAdapter specifing 'IN' in the where clause for list of id's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a table that contains an interger primary key. I want to query
the table for rows that have values equal to a list of ID's that I specify.
The sql that I would use would be something like the following:

SELECT FunctionalGroupId, ReportId
FROM FunctionalGroupReportXRef
WHERE (FunctionalGroupId IN (@Ids))

When I specify this query in the TableAdapter wizard the wizard typecasts
@id to an interger. Thus I can only specify one id in my query rather then a
list of values. I would have expected @Ids to be a string thus allowing me
to specify a list of id's. How can I get around this issue?
Any help would be appreciated.
Thanks!!!
 
See the archives (and my blog) for (several) answers to this common
question.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
You can create a stored procedure like this and use it
CREATE PROC usp_Proc
(
@ids nvarchar(100)
)
AS
DECLARE @sql nvarchar(1000)

SET @sql = N'SELECT FunctionalGroupId, ReportId FROM
FunctionalGroupReportXRef WHERE (FunctionalGroupId IN ('+@ids+'))'

exec sp_executesql @sql
GO
--
Muhammad Mosa
Software Engineer & Solution Developer
MCT/MCSD.NET
MCTS: .Net 2.0 Web Applications
MCTS: .Net 2.0 Windows Applications
 
Hi,
Muhammad replied that I could use a stored procedure to accomplish this task
(Thanks Muhammad). But I'm struggling to find the answer of if I can specify
an 'IN' without using a stored procedure. Can you be more specific as to
where I can find the answer (archive title, blog title).
Thanks again for your help!
Terry
 
I think in all cases you'll use AD-HOC query.
becasue parameter cannot hold multi values. Also the @Ids should contain
value like this (1,2,3,4,5,6) and this is not an integer it is comma
separated string.
and the only type that can hold this value is string types.
So as conclusion, you'll stuck with ad-hoc query.
I'm also looking forward to see someother solution that is more straigh
forward. so if you find it please post it.
Regards,
Muhammad Mosa
Software Engineer & Solution Developer
MCT/MCSD.NET
MCTS: .Net 2.0 Web Applications
MCTS: .Net 2.0 Windows Applications
 
Thanks for the input. Just to clarify when you say ad-hoc query is this in
reference to the TableAdapter class or the command object or some other
class? Is there a way to specify an ad-hoc query using the TableAdapter?
Thanks,
Terry
 
No. You should not use an ad hoc query and Muhammad should know better. They
are dangerous and lead to potentially serious SQL injection attacks. Yes, I
searched my blog and the article had aged off but here is a link to a thread
that discusses the implementation I suggested.

Basically, you setup your SP to accept a delimited string ("5", "two",
"dog") as the IN parameter. Since IN cannot accept a delimited string but
can accept a table, you need to create an in-memory Table from the delimited
string. This is done in a function as described here:
http://groups.google.com/group/comp...ring+table+SQL&rnum=22&hl=en#65937ae0bdc865cc

This way you can (safely) code:

SELECT .... WHERE X IN (SELECT xval FROM
(StringToTable(@delimitedStringParameter) ))

There is a complete discussion of this in my book "Hitchhiker's Guide to
Visual Studio and SQL Server (7th Edition)" due to ship Nov 6th. I also show
how to implement the StringToTable function in a CLR Function.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks again for the info. Looks like what I was looking for. Just to put
things to rest when Muhammad talks about making use of ad-hoc queries is he
referring to doing this with the TableAdapter or the Command object? I want
to understand the what not to do as much as the what to do.
Thanks again!!!
Terry
 
Hi Bill,

William (Bill) Vaughn said:
No. You should not use an ad hoc query and Muhammad should know better.
They are dangerous and lead to potentially serious SQL injection attacks.
Click to expand...

Not quite. If you are dealing with non-strings then you are OK, as you can
easily parse the values.
And the database guys suck (those that devlop databases) because they
haven't implemented native support for passing lists as parameters for ages.
 
Ad-hoc queries can be built inside Stored Procedures.
And till now, there products which are huge products, they are using AD-HOC
queries, But, they protected and secure it from SQL Injections.
Anyway, I also prefere your solution as it seems more clear to me.
Regards
--
Muhammad Mosa
Software Engineer & Solution Developer
MCT/MCSD.NET
MCTS: .Net 2.0 Web Applications
MCTS: .Net 2.0 Windows Applications
 
Back
Top