Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello every one,
I'm developing forums app on ASP.net, Sql Server2008, it is on completetion level but I'm getting a small problem that I've forgoten the sqlQuery for deleting the post.

I mean, I want that only a login user can delete his own post only. When I tested my app then this problem was found out that if I'm login to my account and when go to the postings page then I've privilege to delete any post. That I don't want :( I need help .. Please just tell me the SQL query to delete only that post which is posted by the login user ...

I'm given details of my tables structure. Please help me

thanks



table name: (userTB)    
--------------------
user_id
user_pwd
user_country

table name: (postTB)
--------------------
post_id
post
user_id -----> this user_id id foreign key here from userTB ,in this table posts are saved with its id and user id who posted this post ...


I'm also saving the the login user id in application variable i-e Application["userid"] and when user logout then this variable would be null..

Please help me what query of sql I would use for deleting the post of the login user only... :(
Posted
Updated 14-Dec-11 11:10am
v2

1 solution

If you're trying to delete a post, shouldn't you delete it using the primary key of the post table (post_id)?

If you're UI is giving the user the possibility to try to delete a post posted by another user, consider changing the UI so that this option isn't shown to the user unless it's the same user that posted the post.

If that's not applicable you can still add a condition to the delete statement. Something like:
SQL
DELETE FROM postTB
WHERE post_id = @post_id -- the selected post
AND   user_id = @user_id -- from your variable
 
Share this answer
 
Comments
aounali 15-Dec-11 3:54am    
Thnx Mika ,Wel The Same Query I've Already tried but its not working...Let me tell you further that i m using grid view to show all posts of the asked question below that question , i 've inserted a linkbutton in templete editor and neme it "delete" im giving command arument to evaluate the post id i-e Eval("post_id"), on the click of that linkbutton ,i m giving a defination of function, function code is given below...

for Binding gridview the code is:
string query2 = @"SELECT userTB.user_id, userTB.user_name, userTB.user_contact, userTB.user_country, userTB.user_city, userTB.user_proffesion, userTB.user_pic, postTB.post, postTB.post_id, postTB.question_id, postTB.user_id AS Expr1, postTB.postdate FROM postTB INNER JOIN userTB ON postTB.user_id = userTB.user_id where postTB.question_id='"+Application["question_id"]+"'";
SqlDataAdapter da2 = new SqlDataAdapter(query2, con);
DataSet ds2 = new DataSet();
da2.Fill(ds2);
GridView2.DataSource = ds2;
GridView2.DataBind();

and for the linkbutton_click:


protected void LinkButton8_Click(object sender, EventArgs e)
{
LinkButton lnk = (LinkButton)sender;
Application["post_id"]= lnk.CommandArgument;
SqlConnection con = new SqlConnection(_cs);
con.Open();
SqlCommand cmd1 = new SqlCommand("delete from postTB where post_id = '"+lnk.CommandArgument+"' AND user_id ='"+Application["user_id"]+"' ",con);

cmd1.ExecuteNonQuery();
con.Close();
}


prob is that the records are shown correctly but when i click the linkButton (Delete) in gridview then the record button then all records can be deleted,,,,which is not required,,,, the only record of the login user must be delted,,, plz Help ...Thnx
Wendelius 15-Dec-11 14:13pm    
Strange if the code in LinkButton8 would delete all rows from postTB. Have you tried debugging it?

Also note that the ExecuteNonQuery returns the number of affected rows. So change your code so that you'll see the amount of deleted rows:

int numofrows = cmd1.ExecuteNonQuery();

If you place a breakpoint there, what is the value of returned integer 1 or 0 or something else. Also check the values you add to the statement. If the number of deleted rows is more than 1 then I'd suspect that post_id isn't unique in the table.
aounali 15-Dec-11 15:45pm    
just 1 row is effected
Wendelius 15-Dec-11 15:49pm    
Ok, so it seems that the statement is working correctly (you did want to delete only 1 record, right?) So if too many rows are deleted, could it be that you're actually calling LinkButton8_Click several times?
aounali 16-Dec-11 8:13am    
no actualy the reqiurments are that only a perticular record should be deleted that is posted by a login user ...else record should not be able to deleted cux it doesnt belongs or written by a login user...thatx it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900