locked
db.execute with an IN parameter --- Possible? RRS feed

  • Question

  • User692055318 posted

    Hello,

    I have a sinking suspicion that the answer is no, but I figured I would ask. Is it possible to have a list of items like so:

    SFC-FGE-0001707
    SFC-FGE-0001722
    SFC-FGE-0001732
    SFC-FGE-0001810
    SFC-FGE-0001815
    SFC-FGE-0001851

    That gets put into a textarea and sent to a query:

    var InsertCommand = @"INSERT INTO Notifications.dbo.NotificationLog
                                (name,email,servicenumber,vendors)
                                SELECT customername, email, servicenumber,vendorname 
                                FROM costguard.dbo.vwAllFiberSelect_V2
                                where  servicenumber IN ({0})";
    
    db.Execute(InsertCommand,SVCNumber);

    The issue appears to the WHERE SERVICENUMBER IN ({0})

    Can I do this? Have a parameter as a list? I know I can do it using QueryIn but I've never tried this before....Thanks for any thoughts.

    Laura

    Wednesday, May 20, 2015 1:32 PM

Answers

All replies

  • User-821857111 posted

    No. You can't pass a list like that in. That's why you need an approach like the one detailed here: http://www.mikesdotnetting.com/article/156/webmatrix-database-helpers-for-in-clauses

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 20, 2015 2:19 PM
  • User692055318 posted

    Thanks Mike. I did try that too it didn't work either....

     db.ExecuteIn(InsertCommand,SVCNumber);

    Wednesday, May 20, 2015 2:28 PM
  • User692055318 posted

    I guess I should quantify my statement the way I entered the data doesn't work. I will have to tell the user to make sure they enter the data as a comma separated list.

    Thanks Mike.

    Laura

    Wednesday, May 20, 2015 2:51 PM
  • User-821857111 posted

    Yes - IN clauses require a comma-separated list to be supplied, but you can either ask for it to be provided or construct one yourself from whatever data is submitted.

    Thursday, May 21, 2015 3:51 AM