CODEWITHSUNDEEP
c#asp.netsql-serverwindows
Inkey
Inkey

Reputation: 2207

SQL Server database query

I have used the standard user tables that ASP.net setup and I'm looking to be able to delete users. To do this first off I need to delete the user id from a table called memberships and then delete the user. To do this I have 2 text boxes setup one for user id and other for user name.

Any ideas of a T-SQL statement that will delete the membership user id first and then move onto the delete username this is my statement so far

else
{
   try
   {
      connection.Open();
      cmd = new SqlCommand("DELETE from Membershio
                            WHERE UserId ='" + deleteuserIDbox.Text + "'", connection);
      cmd = new SqlCommand("DELETE from Users WHERE UserName ='" + deleteuserbox.Text + "'", connection);
      cmd.ExecuteNonQuery();
      update.Text = "Your data has been removed";
   }
   catch
   {
      update.Text = "Your data has not been deleted";
   }
}

The two tables are related hence I need to delete the user id first and then the username

any help greatly appricated

Upvotes: 0

Views: 305

Answers (4)

Pleun
Pleun

Reputation: 8920

A bit late but I only noticed your question today.

By doing this on the database you are bypassing all the good stuff! You should do this in C# by calling the Membership::DeleteUser Method

http://msdn.microsoft.com/en-us/library/5xxz7y3a.aspx

You should not mess with the internals of the Membership system at all.

Upvotes: 0

RQDQ
RQDQ

Reputation: 15579

You're not executing the first command:

connection.Open();

cmd = new SqlCommand("DELETE from Membershio
      WHERE UserId ='" +
      deleteuserIDbox.Text + "'", connection);

cmd.ExecuteNonQuery();

cmd = new SqlCommand("DELETE from Users WHERE
      UserName ='" + deleteuserbox.Text +
      "'", connection);

cmd.ExecuteNonQuery();

Also, these commands should be executed in a transaction.

Upvotes: 0

Lexicon
Lexicon

Reputation: 483

If understand it right, your input method has serious issues.

For example, 

UserID   UserName
 1        testUser
 2        testUser2

With the logic in your application; I can enter "1" into deleteuserIDbox and "testUser2" into deleteuserbox which in turn would remove userID 1 but not username "testUser".

If you didn't do it already, you need to associate those two tables using Foreign Key on UserID. So the linkage is persisted with UserID field.

Another issue is, you are directly executing the query with the input from user thus enabling the possiblity of sql injection.

About your query, you can put " cmd.ExecuteNonQuery();" between your two cmd statements.

Upvotes: 2

Ryan Alford
Ryan Alford

Reputation: 7594

To use your current code, you will need to execute the first query, then set the CommandText for the second query and execute that.

  using (SqlCommand cmd = connection.CreateCommand())
  {
       cmd.CommandText = "DELETE FROM Membership WHERE UserID = @UserID";

       cmd.Parameters.AddWithValue("@UserID", deleteuserIDbox.Text);

       connection.Open();

       cmd.ExecuteNonQuery();

       cmd.Paramters.Clear();

       cmd.CommandText = "DELETE from Users WHERE UserName = @UserName";

       cmd.Parameters.AddWithValue("@UserName", deleteuserbox.Text);

       cmd.ExecuteNonQuery();
  }

Another option is to use a stored procedure that would allow you to run the two queries together.

Another option is to do cascading deletes. Here is a link on how to accomplish that.

Lastly, you are opening yourself up to SQL Injection. You should NEVER take input from a user and concatenate that data into a SQL statement. You should either use a Stored Procedure or a parameterized query(like I used above).

Upvotes: 1

Related Questions