Odd parameter/sytax problem

[B. Chernick]

I'm writing a Winforms Dot Net 2.0 program that has an XSD. I need to call
a query with a list of key values so I created a query:
SELECT * FROM Table1 WHERE (key1 IN (@parm1))
where key1 is a char field and the only key field.

Now if I run this in the SQL Query analyzer like this, with literals:
SELECT * FROM Table1 Where (key1 In ('Value1', 'Value2'))
it runs fine and returns what I expect. (2 rows)

If I call it from my program or run it in VS Query builder, it returns
nothing.

Is it even possible to use a parameter in this fashion?

 

[Cor Ligthert[MVP]]

B

How did you set your parameter, you know that if you add it endless while
not clearing that, it will become more and more parameters.

Cor

 

[B. Chernick]

I'm sorry but I'm not absolutely sure what you mean. Could you rephrase that?

Perhaps I should also explain that I usually create the xsd and then create
a data manager class that inherits it. I then create a method which in turn
call the xsd's fill routine, using the query with parameters.

(I've done a little testing since the first message. Perhaps my question
should be: Can I set up a fill routine that has a variable number of
parameters? I normally don't use the IN clause and this is the first time
I've encountered this problem.)

 

[Cor Ligthert[MVP]]

B,

Normally a parameter is set like this (or something like this).

cmd.Parameters.Add(new SqlParamter("@myfirstparameter", MyProperty)

Mostly is forgotten to set before that.

cmd.Parameters.Clear

I hope that this gives an idea what I was giving as suggestion, I only know
that you have a problem with paramters, not much more. Than that you drag
everything, however somewhere you have in my idea to fill the parameters.

Cor

 

[B. Chernick]

I think I see what you were originally suggesting. When writing raw code
from scratch, it would be possible to vary the number of parameters in the
query.

I have been focusing almost exclusively (perhaps too exclusively) on the
code automatically generated by Visual Studio for the XSD, and it has become
apparent to me that it cannot handle a variable length list of parameters in
an 'IN' clause.

It would be nice if a future version was able to do this. Hint. Hint....