none
Remove 1 Value from SQL database c# RRS feed

  • Question

  • Hello i want remove Value i got from SQL database.

    public string Key() { var dt = new DataTable(); using (var cn = new MySqlConnection(ConnectionString)) { using (var cmd = new MySqlCommand() { Connection = cn }) {

    #1 cmd.CommandText = "SELECT * FROM sql7253778.Keys"; cn.Open(); dt.Load(cmd.ExecuteReader()); if (dt.Rows.Count > 0) s = dt.Rows[0]["key"].ToString(); else { MessageBox.Show("Sorry, No more keys :( Send screenshot of this message To me on discord."); }

    #2 } } return s; }

    That loads and returns me one value from Database.

    But now i want to remove value of   string s  from database.

    i can only give the Remove command on #2 zone.

    Thats my SQL code:

    SELECT * FROM `Keys`
    I dont know how to insert another command becouse it never work for me.
    Tuesday, September 4, 2018 3:48 PM

All replies

  • Best practice is to get the string value along with the primary key/id that identifies the row so you can then write;

    DELETE FROM sql7253778.Keys WHERE ID = @Id

    Where ID is the primary field name and @Id is a parameter you add to the Parameter collection of the command object, e.g. cmd.Parameters.AddWithValue("@Id, N) where N is the primary key value you would get from reading the string from the database table during the read operation above.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 4, 2018 4:09 PM
    Moderator
  • Oh thanks, ill contact you later.

    Thats error what ive got

    MySql.Data.MySqlClient.MySqlException: 'Unknown column 'ID' in 'where clause''

    my code now looks like

    public string Key()
            {
                var dt = new DataTable();
                using (var cn = new MySqlConnection(ConnectionString))
                {
                    using (var cmd = new MySqlCommand() { Connection = cn })
                    {
                        cmd.CommandText = "SELECT * FROM sql7253778.Keys";
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                        if (dt.Rows.Count > 0)
                            s = dt.Rows[0]["key"].ToString();
                        else
                        {
                            MessageBox.Show("Sorry, No more keys :( Send screenshot of this message To me on discord.");
                        }
                        cn.Close();
                        cmd.Parameters.AddWithValue("@Id", s);
                        cmd.CommandText = "DELETE FROM sql7253778.Keys WHERE ID = @Id";
                        cn.Open();
                        cmd.ExecuteReader();
                    }
                }
                return s;
            }


    Tuesday, September 4, 2018 4:22 PM
  • In regards to 

    'Unknown column 'ID' in 'where clause''

    In the example I gave ID was an existing column name in the database table, if you don't have a primary key named ID of course it will not work, you need to have a primary key, give it a name then use that in the WHERE clause.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 4, 2018 7:09 PM
    Moderator
  • In regards to 

    'Unknown column 'ID' in 'where clause''

    In the example I gave ID was an existing column name in the database table, if you don't have a primary key named ID of course it will not work, you need to have a primary key, give it a name then use that in the WHERE clause.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    What can i do? Can i somehow add id? Or is there any other way?

    can i do something like delete 1 from keys Where value of string s?

    Wednesday, September 5, 2018 2:09 PM
  • Hello,

    can i do something like delete 1 from keys Where value of string s?

    -----

    What about study a bit about tools which you trying to use?

    If you use an SQL - study SQL syntax and semantic.

    Answer for your question is:

    - for data deletion used command DELETE FROM <table>

    - in WHERE cause of the command you need to specify which row or rows you dealing with.

    if you have ID - it's assumed as PRIMARY KEY (PK) on the table - you can specify your primary key for deletion of single row.

    if you do not have PK on the table - you can specify any number of columns and values which will distinct rows with which you have to deal from those which will left untouched.

    Get the good book and read about SQL.

    Best regards,

    Andrey

    Wednesday, September 5, 2018 3:01 PM
  • DELETE FROM sql7253778.Keys WHERE key = @Id
    Wednesday, September 5, 2018 6:26 PM
  • Perhaps a screenshot will show what I'm talking about.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, September 5, 2018 8:59 PM
    Moderator
  • Btw, not directly related to your problem, but if you're not selecting rows back, why not use cmd.ExecuteNonQuery() ?

    It'll return the number of row affected, so you know whether your query deleted/inserted/updated some rows.


    Thursday, September 6, 2018 1:21 AM
    Answerer