Is this an exact query?

Question

I have two tables

Inbox(id,accountid,emailfrom,emailsubject,emailbody,emaildate,attachment)
Trash(as above)

When a user wants to delete a row from the inboxDataGridView, first that row is copied to the trash table. I'm trying this query to copy the row first:

string query = "INSERT INTO Trash(inboxid, accountid, emailfrom, subject, emailbody,emaildate,attachment) SELECT inboxid, accountid, emailfrom, subject, emailbody,emaildate,attachment FROM Inbox WHERE(inboxid = " + accountinfo.PEmailId + ")";(where accountinfo.PEmailId = selectedRow value

but this query gives me an error.

Kindly guide me on how I can do it, copy and remove.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

This should work:

Insert:

INSERT INTO Trash(id, accountid, emailfrom, subject, emailbody,
   emaildate,attachment)
SELECT id, accountid, emailfrom, subject, emailbody,emaildate,attachment 
FROM Inbox 
WHERE id = @id

Delete:

DELETE Inbox
WHERE id = @id

You need to execute the statements, either in the same command or as two separate commands within a transaction depending on if you want to perform something else inbetween. The @id parameter should be provided as a command parameter in order to prevent SQL injection attacks.

If you want part of the solution on the database side, you could either create this as a stored procedure or as @ThitLwinOo suggests create a trigger. In case you're using the trigger approach, the Delete statement is enough to execute from your program.

score 0 · Answer 2 · answered Jan 31 '12 at 09:49

Actually there is simple and better way to complete what you want is by writing trigger on delection. What is ur DB? SQL Server?

I assume your DB is Sql Server.. Plese try the following trigger in ur db.

CREATE TRIGGER [dbo].[tri_BACKUP_DELETED_ROWS] 

ON [dbo].[Inbox]

AFTER DELETE

AS 

BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (SELECT * FROM deleted)
    BEGIN
        -- keep old record
        INSERT INTO Trash(inboxid, accountid, emailfrom, subject, emailbody,emaildate,attachment)       
        SELECT inboxid, accountid, emailfrom, subject, emailbody,emaildate,attachment 
        FROM DELETED
    END

END

Is this an exact query?

2 Answers2