6 Replies - 1235 Views - Last Post: 29 March 2013 - 02:01 AM Rate Topic: -----

#1 KasunL  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 11-April 11

Deleting MySql Row

Posted 28 March 2013 - 07:28 PM

Hi
i'm trying to make an "email book" with MySql as the database (using wamp server). i load all emails in the database into a listbox on my application form like this:
string connString = "Server=localhost;Database=emailbook;Uid=root;"; // Select DB
            conn = new MySqlConnection(connString);
            command = conn.CreateCommand();
            conn.Open();

            using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM emails", conn))
            {
                try
                {
                    MySqlDataReader Reader = cmd.ExecuteReader();
                    while (Reader.Read())
                    {
                        listBoxList.Items.Add(Reader.GetValue(1).ToString());
                    }
                    Reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            conn.Close();



and, when i select an email from the list and click the delete button, the selected entry must be removed from the database. I have done an incomplete code to begin to do that:

string connString = "Server=localhost;Database=emailbook;Uid=root;"; // Select DB
            conn = new MySqlConnection(connString);
            command = conn.CreateCommand();
            conn.Open();

            using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM emails", conn))
            {
                try
                {
                    MySqlDataReader Reader = cmd.ExecuteReader();
                    while (Reader.Read())
                    {
                        if (Reader.GetValue(1).ToString() == listBoxList.SelectedItem.ToString())
                        {
                           // Yet no idea to howto delete the selected entry from the DB
                        }
                    }
                    Reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            conn.Close();



What above code basically does is, select the "emails" table, then iterate the DB using Reader, and check if Reader.GetValue(1) -- email is matching with the selected email in the listbox. But I cannot figure out howto delete, when a matching is found. Howto do it?

Any help will be much appreciated. Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Deleting MySql Row

#2 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3452
  • View blog
  • Posts: 10,653
  • Joined: 05-May 12

Re: Deleting MySql Row

Posted 28 March 2013 - 07:34 PM

To delete from the database, you would use the SQL DELETE command:

http://www.w3schools.../sql_delete.asp
Was This Post Helpful? 0
  • +
  • -

#3 KasunL  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 11-April 11

Re: Deleting MySql Row

Posted 28 March 2013 - 09:59 PM

Thanks.

ok i tried this coding like this:

using (MySqlCommand cmd1 = new MySqlCommand("DELETE * FROM emails WHERE email=" + listBoxList.SelectedItem.ToString(), conn))
                            {
                                try
                                {
                                    MySqlDataReader Reader1 = cmd1.ExecuteReader();
                                    MessageBox.Show("Deleted");
                                }
                                catch(Exception ex)
                                {
                                    MessageBox.Show(ex.Message);
                                }
                            }



But it gives me the error on above line 5:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM emails WHERE email=JohnDeo@gmail.com' at line 1



What am i doing wrong?

This post has been edited by KasunL: 28 March 2013 - 10:05 PM

Was This Post Helpful? 0
  • +
  • -

#4 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: Deleting MySql Row

Posted 28 March 2013 - 10:31 PM

Strings need to be enclosed in ' marks if you are going to do your statement this way (I'd use parameters). So you need to make sure the JohnDeo@gmail.com is enclosed in ' marks.

And don't use ExecuteReader, it's not going to return more than one item (number of rows deleted). Use ExecuteScaler.
Was This Post Helpful? 0
  • +
  • -

#5 KasunL  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 11-April 11

Re: Deleting MySql Row

Posted 28 March 2013 - 11:59 PM

ok, is this correct? Coz, i still get the same error.

using (MySqlCommand cmd1 = new MySqlCommand("DELETE * FROM emails WHERE email='" + listBoxList.SelectedItem.ToString() + "'", conn))


Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3178
  • View blog
  • Posts: 10,640
  • Joined: 12-December 12

Re: Deleting MySql Row

Posted 29 March 2013 - 01:20 AM

The asterisk * is not required with MySql, just "DELETE FROM some_table WHERE ..".
Was This Post Helpful? 0
  • +
  • -

#7 KasunL  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 11-April 11

Re: Deleting MySql Row

Posted 29 March 2013 - 02:01 AM

ah that's the problem. it works now. many thanks! :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1