SqlCommand w/ Params Select Issue Using the IN Clause

31 Mar 2005 8:08 PM

hharry

Hello All,

I'm trying to run a Select query with a IN clause using a SqlCommand
Object with Paramters, but cannot get the query to return the expected
rows.

Here is my code snippets:

Dim sQryStrDOB As String = "SELECT DENORM_FK FROM dbo.NAME_DOB_LOOKUP
WHERE FIRSTNAME IN (@FIRST)

NameDobCmd.Parameters.Add("@FIRST", sFirst)
NameDobCmd.CommandText = sQryStrZIP

I've tried

sFirst = "JOHN, JOHNNY, JOHNOTHAN"
AND
sFirst = "'JOHN', 'JOHNNY', 'JOHNOTHAN'"
AND
sFirst = "JOHN', 'JOHNNY', 'JOHNOTHAN"

All fail to return any rows. I have run the query using Query Analyzer
and 11 rows were returned.

Thanks in advance

NameZipRow = NameDobCmd.ExecuteScalar

I'm thinking that @FIRST translates to '

31 Mar 2005 8:15 PM

Marina

That results in the where clause being something like:

WHERE FIRSTNAME IN ('JOHN, JOHNNY, JOHNOTHAN')

So as you see, it is all just one long string.

You can also always turn on sql profiler to see what is being sent to the
databse.

Show quoteHide quote

"hharry" <paulquig***@nyc.com> wrote in message
news:1112299730.183151.288670@z14g2000cwz.googlegroups.com...
> Hello All,
>
> I'm trying to run a Select query with a IN clause using a SqlCommand
> Object with Paramters, but cannot get the query to return the expected
> rows.
>
> Here is my code snippets:
>
> Dim sQryStrDOB As String = "SELECT DENORM_FK FROM dbo.NAME_DOB_LOOKUP
> WHERE FIRSTNAME IN (@FIRST)
>
> NameDobCmd.Parameters.Add("@FIRST", sFirst)
> NameDobCmd.CommandText = sQryStrZIP
>
> I've tried
>
> sFirst = "JOHN, JOHNNY, JOHNOTHAN"
> AND
> sFirst = "'JOHN', 'JOHNNY', 'JOHNOTHAN'"
> AND
> sFirst = "JOHN', 'JOHNNY', 'JOHNOTHAN"
>
> All fail to return any rows. I have run the query using Query Analyzer
> and 11 rows were returned.
>
> Thanks in advance
>
>
> NameZipRow = NameDobCmd.ExecuteScalar
>
> I'm thinking that @FIRST translates to '
>

31 Mar 2005 8:22 PM

Larry Lard

hharry wrote:
Show quoteHide quote

> Hello All,
>
> I'm trying to run a Select query with a IN clause using a SqlCommand
> Object with Paramters, but cannot get the query to return the
expected
> rows.
>
> Here is my code snippets:
>
> Dim sQryStrDOB As String = "SELECT DENORM_FK FROM dbo.NAME_DOB_LOOKUP
> WHERE FIRSTNAME IN (@FIRST)
>
> NameDobCmd.Parameters.Add("@FIRST", sFirst)
> NameDobCmd.CommandText = sQryStrZIP
>
> I've tried
>
> sFirst = "JOHN, JOHNNY, JOHNOTHAN"
> AND
> sFirst = "'JOHN', 'JOHNNY', 'JOHNOTHAN'"
> AND
> sFirst = "JOHN', 'JOHNNY', 'JOHNOTHAN"
>
> All fail to return any rows. I have run the query using Query
Analyzer
> and 11 rows were returned.

Not exactly; you have tried

SELECT DENORM_FK FROM dbo.NAME_DOB_LOOKUP WHERE FIRSTNAME IN ('JOHN',
'JOHNNY', 'JOHNOTHAN')

which worked. Your problem is a T-SQL one rather than a VB one; to see
this, try this, which is a more accurate reflection of what you are
trying to do, in Query Analyzer:

declare @first varchar(100)

set @first = "'JOHN', 'JOHNNY', 'JOHNOTHAN'"

SELECT DENORM_FK FROM dbo.NAME_DOB_LOOKUP WHERE FIRSTNAME IN (@first)

Basically, the T-SQL IN operator doesn't work as you want it to. This
comes up a lot in SQL forums.

Possible ways forward depend on how many options there will typically
be in the list, in your application.

--
Larry Lard
Replies to group please