locked
Query using WHERE IN passing values returned from another query RRS feed

  • Question

  • User325035487 posted

    I saw this article http://www.mikesdotnetting.com/Article/156/WebMatrix-Database-Helpers-for-IN-Clauses but it used return from a form post. Any way to adapt it for my situation below or is there something I am missing. I am using an access database as it is an intranet application only.

    This doesnt work.

    var welcomeuser = "Q1212"
    sql = "SELECT RSID, ReferrerType FROM AIReferrerSub WHERE RID = @0 AND ((RSID IN (6,24,29,30,33)) OR (RSID IN (SELECT RSID FROM AIComMembers WHERE MemberID=@1)) )";
    var rsid = db.Query(sql, RID, welcomeuser);
    Json.Write(rsid, Response.Output);

    But the same thing works when i pass the parameter welcomeuser directly in the string

    sql = "SELECT RSID, ReferrerType FROM AIReferrerSub WHERE RID = @0 AND ((RSID IN (6,24,29,30,33)) OR (RSID IN (SELECT RSID FROM AIComMembers WHERE MemberID='Q1212')) )";
    var rsid = db.Query(sql, RID);
    Json.Write(rsid, Response.Output);

    I am using this to populate my second dropdown list based on selection of first drop down based on mikesdotnettings jquery method with json

    Any ideas?

    Saturday, August 9, 2014 3:00 PM

Answers

  • User325035487 posted
    sql = "SELECT RSID, ReferrerType FROM AIReferrerSub WHERE RID = @0 AND ((RSID IN (6,24,29,30,33,34)) OR (RSID IN (SELECT RSID FROM AIComMembers WHERE MemberID='" + welcomeuser +"')) )";

    Ok. This seems to be working so far.. i concatenate before using .." + welcomeuser +"..

    I change it into ..' " welcomeuser + " '.. (notice the single quote before and after the string concatenation. This seems to do the trick.. Please do correct me if this is not the best solution.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 9, 2014 3:17 PM
  • User-1454326058 posted

    Hi jkjhse,

    Maybe the code should be like this:

    sql = "SELECT RSID, ReferrerType FROM AIReferrerSub WHERE RID = @0 AND ((RSID IN (6,24,29,30,33)) OR (RSID IN (SELECT RSID FROM AIComMembers WHERE MemberID='@1')) )";

    You could use the SQL Server Profiler to trace the SQL statement.
    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 10, 2014 9:26 PM

All replies

  • User325035487 posted
    sql = "SELECT RSID, ReferrerType FROM AIReferrerSub WHERE RID = @0 AND ((RSID IN (6,24,29,30,33,34)) OR (RSID IN (SELECT RSID FROM AIComMembers WHERE MemberID='" + welcomeuser +"')) )";

    Ok. This seems to be working so far.. i concatenate before using .." + welcomeuser +"..

    I change it into ..' " welcomeuser + " '.. (notice the single quote before and after the string concatenation. This seems to do the trick.. Please do correct me if this is not the best solution.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 9, 2014 3:17 PM
  • User-1454326058 posted

    Hi jkjhse,

    Maybe the code should be like this:

    sql = "SELECT RSID, ReferrerType FROM AIReferrerSub WHERE RID = @0 AND ((RSID IN (6,24,29,30,33)) OR (RSID IN (SELECT RSID FROM AIComMembers WHERE MemberID='@1')) )";

    You could use the SQL Server Profiler to trace the SQL statement.
    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 10, 2014 9:26 PM