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!
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);
Marked as answer by Mikesdotnetting on Feb 28, 2012 05:54 AM
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?
freakysquash
Member
228 Points
155 Posts
IN Clause DB Helper
Feb 21, 2012 02:19 AM|LINK
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!
Database mysql
Shellymn
Contributor
2612 Points
485 Posts
Re: IN Clause DB Helper
Feb 21, 2012 02:40 AM|LINK
You can use a single SQL statement for Insert .
eg : - Insert into sample_trash (idOfTrashedMessage, subject, sender, date_sent) select [column name /* ]
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
Database mysql
GmGregori
Contributor
5470 Points
737 Posts
Re: IN Clause DB Helper
Feb 21, 2012 07:29 AM|LINK
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);vivekreddy
Member
200 Points
96 Posts
Re: IN Clause DB Helper
Feb 21, 2012 11:28 AM|LINK
Hi,
You just need to concatenate...use + to concatenate.
Database mysql
Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: IN Clause DB Helper
Feb 21, 2012 05:52 PM|LINK
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
Database mysql
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
freakysquash
Member
228 Points
155 Posts
Re: IN Clause DB Helper
Feb 21, 2012 06:13 PM|LINK
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?
SonicMan
Participant
1472 Points
228 Posts
Re: IN Clause DB Helper
Feb 28, 2012 01:34 AM|LINK
Hi
You can read Mikesdotnetting's article.
It's really what you need.