.Net SQL Parameter for String List Problem
- by JK
I am writing a C# program in which I send a query to SQL Server to be processed and a dataset returns. I am using parameters to pass information to the query before it is sent to SQL server. This works fine except in the situation below.
The query looks like this:
reportQuery =
"
Select *
From tableName
Where Account_Number in (@AccountNum);
and Account_Date = @AccountDate
";
The AccountDate parameter works find but not the AccountNum parameter.
I need the final query to execute like this:
Select *
From tableName
Where Account_Number in ('AX3456','YZYL123','ZZZ123');
and Account_Date = '1-Jan-2010'
The problem is that I have these account numbers (actually text) in a C# string list. To feed it to the parameter, I have been declaring the parameter as a string. I turn the list into one string and feed it to the parameter. I think the problem is that I am feeding the paramater this:
"'AX3456','YZYL123','ZZZ123'"
when it wants this
'AX3456','YZYL123','ZZZ123'
How do I get the string list into the query using a parameter and have it execute as shown above?
This is how I am declaring and assigning the parameter.
SqlParameter AccountNumsParam = new SqlParameter();
AccountNumsParam.ParameterName = "@AccountNums";
AccountNumsParam.SqlDbType = SqlDbType.NVarChar;
AccountNumsParam.Value = AccountNumsString;
FYI, AccountNumString == "'AX3456','YZYL123','ZZZ123'"