C# sql delete command

Question

SqlConnection con1 = new SqlConnection(strcon);
con1.Open();

string query = " ";
query += " BEGIN TRANSACTION ";
query += " DELETE FROM VehicleRentals FROM VehicleRentals INNER JOIN Vehicles N VehicleRentals.VehicleID = Vehicles.VehicleID WHERE LicensePlate=@LicensePlate ";
query += " DECLARE @x int ";
query += " SELECT @x = VehicleTypeCode FROM Vehicles WHERE LicensePlate=@LicensePlate ";
query += " DELETE FROM Manufacturers FROM Manufacturers INNER JOIN Models ON Manufacturers.ManufacturerCode = Models.ManufacturerCode INNER JOIN Vehicles ON Models.ModelID = Vehicles.ModelID WHERE LicensePlate=@LicensePlate ";
query += " DELETE FROM VehicleTypes FROM VehicleTypes WHERE VehicleTypeCode = @x ";
query += " COMMIT TRANSACTION ";

SqlCommand cmd1 = new SqlCommand(query, con1);
cmd1.Parameters.AddWithValue("@LicensePlate", txtPlaka.Text);
cmd1.ExecuteNonQuery();
con1.Close();

I fixed the code as you said. Spaces between lines and I'm using parameter @LicensePlate. But the code is not working

You don have spaces between the statements. You can use a verbatim string literal to avoid writing query += for every new line. You should also use sql-paremeters instead of string concatenation to prevent sql-injection. — Tim Schmelter
– Tim Schmelter, Commented May 23, 2014 at 15:09
Curious what will happen when you enter this license plate number? Try it out! — Remus Rusanu
– Remus Rusanu, Commented May 23, 2014 at 15:14
This question makes no sense. Did there use to be a question here that got edited out? — AndrewS
– AndrewS, Commented May 23, 2014 at 15:43
@AndrewS - Yea OP edited and is now asking about problems after the edit.. — Neil Smith
– Neil Smith, Commented May 23, 2014 at 15:44

Neil Smith · Accepted Answer · 2014-05-23 15:11:37Z

5

query += "BEGIN TRANSACTION";
query += "DELETE FROM VehicleRentals FROM VehicleRentals INNER JOIN Vehicles N..."

turns into

"BEGIN TRANSACTIONDELETE FROM..."

You need to include the spaces between each line:

query += "BEGIN TRANSACTION";
query += " DELETE FROM VehicleRentals FROM VehicleRentals INNER JOIN Vehicles N..."

And as others have stated, you should use parameters to avoid sql injection.

answered May 23, 2014 at 15:11

Neil Smith

2,5651 gold badge17 silver badges18 bronze badges

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

7 Comments

Dean.DePue Over a year ago

Plus, as tim schmeter has said, you need to use variables too.

Neil Smith Over a year ago

True. I added a note about it but usually don't include it in my answer when the question is about the query not executing properly.

TKsknl Over a year ago

I fixed as you said. Using spaces between lines and parameter. But still the code is not working. I edit the question above

Neil Smith Over a year ago

The second line of your query has typos. You repeat FROM VehicleRentals twice and you have N instead of ON in your join. Not sure if there's other syntax problems; I didn't look through the entire query so check that everything is spelled correctly. Also, you don't need spaces at the beginning and end of each line (but it doesn't hurt anything). You just need at least one space in between each word.

TKsknl Over a year ago

I appreaciate for your answer. It is working now. Thank you very much!

|

Collectives™ on Stack Overflow

C# sql delete command

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related