asp.net SqlDataSource select statement with IN (...) clause

Question

I need to pass a server-side parameter to my SqlDataSource SELECT IN-clause as follows (notice the parameter @InClause and it's location in the SQL-select that is defined in the aspx (for the SqlDataSource):

SELECT UID_REPORT, TXT_RPT_TEXT, TXT_RPT_NAME, TXT_RPT_ASPX_PAGE, TXT_RPT_TYPE 
FROM REPORTS WHERE (UID_REPORT IN (@InClause)) 
ORDER BY INT_SORT_ORDER

But this does not pass validation in the Test-Query operation. The '@InClause' parameter gets it's value from a HiddenField control. I want to set this value based on some conditions. for example If DIVISION=5 then @InClause would ="45,46" Else ="14,15". Any suggestions on how to pass the IN ( ... ) values by the hiddenfield.Value (which is a string, of course). Thanks.

This question again? http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm — Tab Alleman, Jan 05 '16 at 18:55
I was searching for similar questions/answers within Stack-Overflow, not asking the internet. I marked @VR46 as the answer, although your link buries the solution within the six different methods under the context of stored procedures. Thanks. — John D, Jan 05 '16 at 19:49

score 1 · Accepted Answer · answered Jan 05 '16 at 18:04

1

You need split string function to do this

SELECT uid_report, 
       txt_rpt_text, 
       txt_rpt_name, 
       txt_rpt_aspx_page, 
       txt_rpt_type 
FROM   reports 
WHERE  uid_report IN (SELECT split_value 
                      FROM   Udf_splitstring(@InClause, ',')) 
ORDER  BY int_sort_order

Check the below links to create a split string function

or of-course you can use dynamic sql

answered Jan 05 '16 at 18:04

Pரதீப்

91,748
19
131
172

Is your posted code working in ASPX page select command or inside a SQL-Server script window? The select-code I posted resides within a SqlDataControl in aspx code. – John D Jan 05 '16 at 18:46
i dont have anything to validate – Pரதீப் Jan 05 '16 at 18:47
I used your posted SQL code within the SqlDataSource select-command within the aspx code and it worked perfectly. I have a splitString function in my dbo but I did not know I could embed it within the aspx page. I use it many times within stored procedures. Thanks. – John D Jan 05 '16 at 19:41

asp.net SqlDataSource select statement with IN (...) clause

1 Answers1