IN Clause DB Helper

I have posted a thread here regarding deleting of selected items using webgrid+checkbox http://forums.asp.net/post/4839004.aspx.

it works now with this code:

var selectedId = Request["messageId"];
var getFromSample = "SELECT * FROM sample WHERE id IN ({0})";
selectedMessageData = db.QueryIn(getFromSample, selectedId);

@* I have no idea here how can I get the data from the previous query *@

var insertToTrash = "INSERT INTO sample_trash (idOfTrashedMessage, subject, sender, date_sent) VALUES (@0, @1, @2, @3)";
db.Execute(insertToTrash, data from the getFromSample query)
var delFromSample = "DELETE FROM sample WHERE id IN ({0})";
db.ExecuteIn(delFromSample, selectedId);

Now, I want to know if how can I get first the selected items from the db using SELECT then transfer it to another table using INSERT before deleting. Something like this = SELECT -> INSERT -> DELETE. Thanks!

Re: IN Clause DB Helper

You can use a single SQL statement for Insert .

eg : - Insert into sample_trash (idOfTrashedMessage, subject, sender, date_sent) select [column name /* ]

FROM sample WHERE id IN ([selectedId])

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

Re: IN Clause DB Helper

The correct statements should be:

var insertToTrash = "INSERT INTO sample_trash (idOfTrashedMessage, subject, sender, date_sent) "
    + "SELECT id, subject, sender, date_sent FROM sample WHERE id IN ({0})";
db.ExecuteIn(insertToTrash, selectedId);
var delFromSample = "DELETE FROM sample WHERE id IN ({0})";
db.ExecuteIn(delFromSample, selectedId);

Re: IN Clause DB Helper

Hi,

You just need to concatenate...use + to concatenate.

Re: IN Clause DB Helper

vivekreddy
You just need to concatenate

We do not recommend concatenating user input to generate SQL dynamically. Read this to see why: http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

Re: IN Clause DB Helper

I tried the concatenated query string and it didn't worked. And in the first place, I want all my query to be parameterized. Is there anyway for me to achieve the goal?

Re: IN Clause DB Helper

You can read Mikesdotnetting's article.

It's really what you need.