0

I have 2 tables that I want to delete data from: User table and feedback table.

Here are my codes (which doesn't work):

  string strConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    SqlConnection myConnect = new SqlConnection(strConnectionString);

    string strtext = "DELETE FROM [User] WHERE Username = @user";
    strtext += "DELETE FROM Feedback WHERE Username = @user";
    strtext += "DELETE FROM Light WHERE Username = @user";
    strtext += "DELETE FROM Temperature WHERE Username = @user";
    strtext += "DELETE FROM Airquality WHERE Username = @user";
    strtext += "DELETE FROM Bodycondition WHERE Username = @user";

    SqlCommand cmd = new SqlCommand(strtext, myConnect);

    cmd.Parameters.Add("@user", SqlDbType.VarChar);
    cmd.Parameters["@user"].Value = txtdeleteuser.Text;

    myConnect.Open();
    int result = cmd.ExecuteNonQuery();

Is there any way to delete the SAME data from both tables using 1 SQL query in asp.net?

luna
  • 31
  • 3
  • How is it a duplicate question? This one involves asp.net, not just sql alone. I can't seem to "seperate the sql queries" in asp.net as mentioned in the suggested duplicated answer. – luna Jan 25 '17 at 17:48
  • You need to make this into two queries. The simplest path is to replace that AND with a ;. In other words your strtext will contain two individual delete statements. – Sean Lange Jan 25 '17 at 17:54
  • You also need to read about the USING statement. Your connection and command should be inside those to properly dispose of those objects. – Sean Lange Jan 25 '17 at 17:55
  • Hi, I've made changes using multiple queries but it still doesn't work. There is an error `Must declare the scalar variable "@userDELETE" ` I think that the mutlple queries can't work in asp.net. @sean lange – luna Jan 25 '17 at 18:03
  • You don't have semicolons in your query, you have a whole bunch of string concatenation statements. Your string should be "DELETE from [User] Where Username = @user; Delete from ..." You are confusing sql from your c# code. – Sean Lange Jan 25 '17 at 20:28
  • Thanks very much! It works now! – luna Jan 25 '17 at 22:53

0 Answers0