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

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


"William (Bill) Vaughn" wrote:

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.databases.ms-sqlserver/browse_thread/thread/71249ca2c6a9a4a7/65937ae0bdc865cc?lnk=st&q=function+delimited+string+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.
__________________________________

"Muhammad Mosa" <MuhammadMosa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A60C18E3-5999-4336-96AD-01A220C7B530@xxxxxxxxxxxxxxxx
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


"twahl" wrote:

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


"William (Bill) Vaughn" wrote:

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.
__________________________________

"twahl" <twahl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B11DCF18-38AF-4806-9013-D3F95C6B4AFC@xxxxxxxxxxxxxxxx
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!!!






.

Relevant Pages

  • Re: Stored procedure date time format into date only
    ... Even if you have a datetime parameter you can specify a date only string. ... > Iss it possible to change the stored procedure date time parameter format ... > A Crystal report is created using stored procedure and users want to enter ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL stored procedure executing twice
    ... from one parent record to another. ... Dim stDocName As String ... My pass-thru query syntax going to stored procedure ...
    (microsoft.public.access.modulesdaovba)
  • RE: MS Query "Connections"
    ... handle that with my connection string. ... for using the stored procedure route is that it is faster. ... If you truly want to pass in the query string, ...
    (microsoft.public.excel.programming)
  • Re: Web DAV only works when authenticated to OWA
    ... > Dim objRequest 'As MSXML.XMLHTTPRequest ... > Dim strXMLNSInfo 'As String ... > ' Specify the URL of the new object to be created. ... > ' Put it all together in an HTTP request. ...
    (microsoft.public.exchange.development)
  • Re: Grabbing string values from the end of a string of an unknown
    ... having to specify the parameter values/never run macros unless excel has just ... Not if the string is produced by a formula. ... shown below in this recorded macro are restored. ... The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each ...
    (microsoft.public.excel.programming)