C# Mysql move row to another table and get max value of cell

364
July 08, 2017, at 11:14 AM

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("quey" + Convert.ToInt32(cmd.ExecuteScalar()));
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Thanks in advance

Answer 1

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

Answer 2

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("quey" + 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

Rent Charter Buses Company
READ ALSO
How to schedule events with msql

How to schedule events with msql

I'm trying to make an event that will execute every 1 hour

248
(Mysql) What is the most efficient way to store table changes?

(Mysql) What is the most efficient way to store table changes?

I am developing a web application with mysql, and I have several tables including user and article

263
Access array content through loop

Access array content through loop

I have a mysql query result which I have turned into an array with:

235
Get value between two time where time is store in time format in phpmyadmin

Get value between two time where time is store in time format in phpmyadmin

I am trying to get value from table based on two time suppose you have time start 23:00 PM and end time 03:00 AM but issue is that it date goes changed when time is 23:00 PM and value is not showing if I put condition where start_date

270