Diego Diego - 1 year ago 53
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 Source

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