SqlCommand w/ Params Select Issue Using the IN Clause

[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 '

 

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

 

[Larry Lard]

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.