Diego Diego - 6 months ago 12
MySQL Question

Dynamic delete code erases whole table

I have a major problem with my delete statement in my code.

Running C#, MySql for Visual Studio and the .NET connector.

It is really basic:

FYI: Teile = Parts in German

I have a

List<Teile>
where the item has an ID, a description and a count.
This list is given to my delete function (i know, no parameters):

public static void Delete(List<TeilShort> Teileliste)
{
string strSQL = "DELETE FROM Teile WHERE tID ";
MySqlConnection conn = GetConnection();
MySqlCommand cmd = new MySqlCommand(strSQL, conn);
if (Teileliste.Count > 1)
{
strSQL += "IN (";
foreach (var item in Teileliste)
{
strSQL += item.ID + ", ";
}
strSQL = strSQL.Substring(0, strSQL.Length - 2) + ")";
}
else
{
strSQL += "= " + Teileliste[0].ID;
}

Debug.WriteLine(strSQL);

try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (MySqlException) { throw; }
finally { conn.Close(); }
}


The tID is the Primary Key of my table Teile.

When i comment the whole if-statement and just add
= 14
to
strSQL
then it works perfectly fine and deletes only the entry with the ID 14.

If i have only one item in my Teileliste and the code writes itself (the
Debug.WriteLine(strSQL);
outputs the same,
every entity in my table gets deleted

Luckily i only had test data in it.

Can anyone tell me where the problem could be?

Greetings
Diego

Answer

You define your command on top of your routine with

string strSQL = "DELETE FROM Teile WHERE tID ";
MySqlConnection conn = GetConnection();
MySqlCommand cmd = new MySqlCommand(strSQL, conn);

After this you build your command string but you never set this new command into your command object. So your int-ID is interpreted as boolean value. Everything other than 0 is true. That's why you delete everything...

Try to move the new MySqlCommand... after the command's generation...

Comments