0

I have a method that moves a row from one table to another. That itself works fine. What I am trying to do now is to select the highest value of a cell inside the table Callbacks. This is what I have tried:

public static void MoveLead(RadGridView Gridview, RadDropDownList SegmentDropdown, string UniqueID)
{
    try
    {
        string Table = SegmentDropdown.Text;
        using (MySqlConnection cn = new MySqlConnection(Varribles.ConString))
        {
            string query = "BEGIN;  select max(UniqueID) from Callbacks;     INSERT INTO Callbacks select * from " + Table + " where UniqueID = " + UniqueID + "; DELETE FROM " + Table + " where UniqueID = " + UniqueID + "; COMMIT;";
            cn.Open();
            using (MySqlCommand cmd = new MySqlCommand(query, cn))
            {
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();

                Console.WriteLine("query" + Convert.ToInt32(cmd.ExecuteScalar()));
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Taco2
  • 429
  • 1
  • 6
  • 18
  • I forgot to mention that the the value I get from this line: Console.WriteLine("quey" + Convert.ToInt32(cmd.ExecuteScalar())); Will always be 9 witch is kind of the problem – Taco2 Jul 07 '17 at 21:04
  • You should really start to study how to avoid Sql Injection – Steve Jul 07 '17 at 21:05
  • I know how to use paramatized query, but that is not the subject – Taco2 Jul 07 '17 at 21:06
  • Where do you get the value for the variable _UniqueID_ – Steve Jul 07 '17 at 21:06
  • The gridview contains a column called UniqueID, witch is a unique id when commiting changes etc.. So when current row changes it will check what the unique id is on that row – Taco2 Jul 07 '17 at 21:09

2 Answers2

1

Have you tried just changing your code a bit?

public static void MoveLead(RadGridView Gridview, RadDropDownList SegmentDropdown, string UniqueID)
{
    try
    {
        string Table = SegmentDropdown.Text;
        using (MySqlConnection cn = new MySqlConnection(Varribles.ConString))
        {
            cn.Open();

            string query = "select max(UniqueID) from Callbacks;";
            using (MySqlCommand cmd = new MySqlCommand(query, cn))
            {
                // Notice I removed the command text, you are already setting the command text in the constructor for the MySqlCommand
                int UID = Int32.Parse(cmd.ExecuteScalar());

                Console.WriteLine("query" + UID);
            }

            query = "BEGIN;  INSERT INTO Callbacks select * from " + Table + " where UniqueID = ?UniqueID; DELETE FROM " + Table + " where UniqueID = ?UniqueID; COMMIT;";
            using (MySqlCommand cmd = new MySqlCommand(query, cn))
            {
                // Use parameters to sanitize input. There are very rare circumstances where you would want to do a direct concatenation to a query as its susceptible to sql injection
                cmd.Parameters.Add(new MySqlParameter("UniqueID", UniqueID))
                cmd.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Also, I would highly recommend you read up on SQL injections. The way you are building your queries are scary depending on where "Table" and "UniqueID" are coming from. Seeing the Table variable cant be parameterized, you will need to be extra careful on where that value is being populated from. Check out https://stackoverflow.com/a/652999/5947241 & https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mythikos
  • 119
  • 12
  • Ugh, complete oversight. I was a break at work replying to this lol. I corrected the post. – Mythikos Jul 07 '17 at 21:41
  • 1
    Yes now it is more correct (at least for table parameter) I just would add the escape character around the table name (backticks in MySql) – Steve Jul 08 '17 at 09:03
0

You are executing command two times - with "ExecuteNonQuery" and "ExecuteReader". Just remove "cmd.ExecuteNonQuery()" and you are done.

Pavel Dmitrenko
  • 310
  • 2
  • 7
  • @Taco2, it's hard to say how UniqueID is generating in you DB, but keep in mind, what you are calling Sql command (and inserting values into Callbacks) two times -- first time with ExecuteNonQuery, and second time same query is called on ExecuteScalar. – Pavel Dmitrenko Jul 07 '17 at 21:29
  • Can you check tables from where you are inserting records? Since UniqueId is not autoincrement, It seems what "9" is just a max value for table(s) from where you are inserting rows. If so, you will always receive "9" as max UniqueId from Callback table. – Pavel Dmitrenko Jul 07 '17 at 21:36
  • The UniqueID is realy not relevant to this question, it has nothing to do with what I am trying to accomplish, I simply want to select the highest value inside the table Callbacks, but it need to be done in the same query as above – Taco2 Jul 08 '17 at 11:01