CODEWITHSUNDEEP
asp.netsqldata-access-layertableadapter
Tom Gullen
Tom Gullen

Reputation: 61755

ASP.net simple delete query, pass in string value

DELETE FROM tblArtworkApprovalUsers
WHERE     (userID NOT IN (@UserIDList)) AND (approvalID =
                          (SELECT     ID
                            FROM          tblArtworkApprovals
                            WHERE      (templateID = @TemplateID)))

This is in my table adapter. @UserIDList needs to accept something like:

2,44,12,70

How can I make this query accept that string?

Upvotes: 1

Views: 1118

Answers (3)

Gorgsenegger
Gorgsenegger

Reputation: 7856

You might want to have a look at Arrays in SQL2005 and Arrays in SQL2008, depending on the version of your SQL server.

Upvotes: 1

Aliostad
Aliostad

Reputation: 81680

NOT IN <expr> requires an expression and not a string. So if you are passing the parameter and not constructing the SQL dynamically this cannot be done.

Alternative is to create the SQL dynamically (while being aware of SQL Injection):

string commad = @"DELETE FROM tblArtworkApprovalUsers " +
             "WHERE (userID NOT IN ({0})) AND (approvalID ="+
                          "(SELECT     ID " +
                            "FROM          tblArtworkApprovals " +
                            "WHERE      (templateID = {1})))";

command = string.Format(command, userIDList, templateID);

UPDATE

Craig pointed to a better solution here which does not provide much better performance (since parameters are variable and query plan does not get cached unless it is exactly the same) but help with SQL injection attack: Parameterize an SQL IN clause

Upvotes: 2

Ocelot20
Ocelot20

Reputation: 10800

You have a couple of options...

  1. Dynamically create the whole SQL string and execute (like Aliostad suggests).
  2. Write a stored procedure that accepts the string, parses it into a temporary table, then run your query against that temp table. A quick search will provide many ways to do this (here is one).

Upvotes: 1

Related Questions