|
.net
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Passing array of numbers to SQL queryI am using an objectdatasource with a .Net 2.0 ASP page.
The SQL for the tableadapter needs to use the IN operator as in WHERE job_id in (111, 222, 333, 444, 555) Job_id is a DBType Decimal and ProviderType Number I have set the default value for the parameter to be 19620,19610,19580,19550 for testing However, .Net strips the , and turns it into one large number. How do I specify that it is rather an array or list of numbers, esentially passing an array of numbers to the parameter? Hi,
The short answer is... you can't. There is no SQL mechanism for passing an array for use with IN (...). If the number of list of numbers is small you could just do each element as a specific parameter. If the list is large you'll have to store the values in a table and change the IN (...) to be "N ((SELECT n FROM t WHERE ....))"... or maybe an INNER JOIN (once the numbers are stored in a table... why not do a join instead of an IN). Regards, Rob MacFadyen <jrogers***@gmail.com> wrote in message Show quoteHide quote news:1157818845.409180.167050@e3g2000cwe.googlegroups.com... >I am using an objectdatasource with a .Net 2.0 ASP page. > > The SQL for the tableadapter needs to use the IN operator as in > WHERE job_id in (111, 222, 333, 444, 555) > Job_id is a DBType Decimal and ProviderType Number > > I have set the default value for the parameter to be > 19620,19610,19580,19550 for testing > > However, .Net strips the , and turns it into one large number. > > How do I specify that it is rather an array or list of numbers, > esentially passing an array of numbers to the parameter? > jrogers***@gmail.com wrote:
> The SQL for the tableadapter needs to use the IN operator as in Pass it as a string. Then let the stored procedure that returns the data, > WHERE job_id in (111, 222, 333, 444, 555) > > How do I specify that it is rather an array or list of numbers, > esentially passing an array of numbers to the parameter? parse out the individual items. There are some issues with this approach.... Search google groups in comp.databases.ms-sqlserver. I remember Erland Sommarskog (or something like that) has a HP about this specific problem. /jim  
Other interesting topics
How to put a list of items with hyperlinks under a label ?
ADO.NET problem? Assemblies & DLLs? Detailsview shows real numbers with commata: Error converting data type nvarchar to real! VS2005 toolbox is not visible Dynamic table and postback Passing errors - not in the url Best way to handle a bad parameter in the url? Data Acess aplication block } expected errors in VS2005 |
|||||||||||||||||||||||
