SQL Server database query

Question

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

The first thing I notice is you're using string concatenation in your sql statements. You'll want to use Bind Variables: richquackenbush.com/2011/02/bind-variables.html for several reasons. Next, accept some answers you slacker! — RQDQ
– RQDQ, Commented Apr 6, 2011 at 13:29
If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Apr 8, 2011 at 21:09

Lexicon · Accepted Answer · 2011-04-06 13:34:30Z

2

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.

answered Apr 6, 2011 at 13:34

Lexicon

4831 gold badge3 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ryan Alford · Accepted Answer · 2011-04-06 13:32:40Z

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).

RQDQ · Accepted Answer · 2011-04-06 13:34:31Z

0

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.

answered Apr 6, 2011 at 13:34

RQDQ

15.6k2 gold badges36 silver badges61 bronze badges

Comments

Pleun · Accepted Answer · 2011-05-11 20:42:54Z

0

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.

answered May 11, 2011 at 20:42

Pleun

8,9102 gold badges33 silver badges51 bronze badges

Collectives™ on Stack Overflow

SQL Server database query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related