problem with the delete sql query

Posted by Farooq_torus under ASP.NET on 9/9/2011 | Points: 10 | Views : 2392 | Status : [Member] | Replies : 5
i have three tables in all users,roles,groups


i am trying to delete userid and groupid from the roles table on selecting userid from the user table . can anyone figure out wots the problem in query.

i wrote a query but it is deleting all the records of userid and roleid in the roles table.


The query i am trying is

SqlCommand cmd = new SqlCommand("Delete ROLES SELECT userid FROM users WHERE username= '" + lbldeletename.Text + "'", MAconn);



and the whole code for rowdeleting event in datagridview is

Collapse | Copy Code
protected void _rowdeleting(object sender, GridViewDeleteEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
Label lbldeletename = (Label)row.FindControl("lblusername");

MAconn.Open();
SqlCommand cmd = new SqlCommand("Delete ROLES SELECT userid FROM users WHERE username= '" + lbldeletename.Text + "'", MAconn);
cmd.ExecuteNonQuery();
MAconn.Close();
bind();

}




Responses

Posted by: SheoNarayan on: 9/9/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Why you have DELETE and SELECT both in a single statements?

Keep it separately and it should work as expected by you.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Farooq_torus, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 9/9/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi farook,

i'm not sure, but plz try this query:

"DELETE roles WHERE userid in (SELECT userid FROM users WHERE username= '" + lbldeletename.Text + "')";

i hope you will find your desired result by this change in your query.

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Farooq_torus, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 9/9/2011 [Member] Platinum | Points: 25

Up
0
Down
Query 1:

Delete From ROLES Where userid = (SELECT userid FROM users WHERE username= lbldeletename.Text )

Query 2:
DELETE from Roles Where UserID = (
Select r.UserID From Roles r
Join Users u
On u.userid = r.userid
Where u.username= lbldeletename.Text)

Hope this helps

Best Regards,
Niladri Biswas

Farooq_torus, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/19/2012 [Member] Bronze | Points: 25

Up
0
Down
Please execute these query..
"DELETE roles WHERE userid= (SELECT userid FROM users WHERE username= '" + lbldeletename.Text + "')"

Farooq_torus, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/19/2012 [Member] Bronze | Points: 25

Up
0
Down
Please mark as answer if it helpful to you...That helps others who search the same...

Farooq_torus, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response