none
deleting record from a sql server database RRS feed

  • Question

  • I just trying to delete a record from a database. I'm not getting any errors and the breaks I put in seem to be returning the results. So I fail to under stand why this doesn't work. More then likely it's something I missed in the code.

    When a user clicks on a choice in a listbox. Then clicks a button and then deleted that info from the database where = listbox1.text is.

    private void btnDelete_Click(object sender, EventArgs e)
            {
    
                try
                {
                    using (SqlConnection conn = new SqlConnection())
                    {
                        conn.ConnectionString = @"Data Source=.\SQLEXPRESS; Initial Catalog=mlhelper;Trusted_Connection = true;";
    
                        conn.Open();
    
                        string dName = listBox1.Text;
    
                        string cmdd = "delete from games where shortname=@Gname ";
                        string t;
    
    
                        using (SqlCommand cm = new SqlCommand(cmdd, conn))
                        {
    
                            cm.Parameters.AddWithValue("@Gname", dName);
    
                            int rows = cm.ExecuteNonQuery();
                            
    
                            if(rows == 1)
                            {
                                t = " Completed ";
                            }
                            else
                            {
                                t = " Not Completed ";
                            }
    
                        }
    
                        conn.Close();
                        MessageBox.Show("Game Deleted! "+ t + dName,Application.ProductName);
                    }
                }
                catch(Exception rr)
                {
                    MessageBox.Show("Error Deleting Game! : " + rr.Message, Application.ProductName);
                }
    
                Loadgames();
    
                //Ask to delete json file. 
    
            }
        }

    The if statement is what I use to make sure that the record is deleted. How ever I get the "Not completed" is displayed. So I have no idea. I think I just have something missing or the code is missing something.

    Joe


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Friday, March 22, 2019 1:07 PM

Answers

  • yes I know. That's why I wanted to update this code and retire the project. I'm turning 60 in May and it's been crazy for me. See I removed the view grid and put a listview in it's place. I got it all working. I'm just having an issue getting the the ID from the list view for I can delete that record. and I keep getting a 0 returned. Makes no sense. Let me show you my code. If you want to help PLEASE use simple code. Anything else I won't under stand stand what your doing. The sample you sent me makes no sense to me. any way. Here

    Here's how I load in the games the user added.

    public void Loadgames()
            {
                listView1.SelectedItems.Clear();
    
                using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                {
                    conn.Open();
                    conn.ChangeDatabase("mlhelper");
    
                    string cmddb = "SELECT * FROM games";
    
                    using (SqlCommand cmd = new SqlCommand(cmddb, conn))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                
    
                                while (reader.Read())
                                {
    
    
                                    ListViewItem item = new ListViewItem(reader["id"].ToString());
                                    item.SubItems.Add(reader["shortname"].ToString());
                                    //item.SubItems.Add(reader["shortname"].ToString());
    
                                    listView1.Items.Add(item);
    
    
                                }
                            }
                        }
    
                    }
    
                    conn.Close();
                }
            }
    

    here's now I try and delete the record.

     private void btnDelete_Click(object sender, EventArgs e)
            {
                //Gets the record Id number to delete
                int gameId = Convert.ToInt32(listView1.SelectedItems[0].Text);
    
                if (gameId < 1)
                {
                    MessageBox.Show("Record Id Missing ",Application.ProductName);
                    return;
                }
    
                try
                {
                    using (SqlConnection conn = new SqlConnection())
                    {
                        conn.ConnectionString = @"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";
    
                        conn.Open();
                        conn.ChangeDatabase("mlhelper");
                        
                        string cmdd = "delete from games where id = @gameId";
                        string t;
    
                        using (SqlCommand cm = new SqlCommand(cmdd, conn))
                        {
    
                            cm.Parameters.AddWithValue("@gameId", gameId);
    
                            int rows = cm.ExecuteNonQuery();
                            
    
                            if(rows == 1)
                            {
                                t = " Completed ";
                            }
                            else
                            {
                                t = " Not Completed ";
                            }
    
                            conn.Close();
                            MessageBox.Show(string.Format("{0} rows deleted Id {1}", rows, gameId),Application.ProductName);
    
                        }                    
                    }
                }
                catch(Exception rr)
                {
                    MessageBox.Show(String.Format("Error {0} deleting Id {1}",rr.Message,gameId), Application.ProductName);
                }
    
                //Ask to delete json file. 
    
                if(DialogResult.Yes == MessageBox.Show("Delete Config file",Application.ProductName,MessageBoxButtons.YesNo))
                {
                    File.Delete(FullGamePath + listView1.SelectedItems[0].SubItems[1].Text.Trim() + ".json");
                }
    
    
                Loadgames();            
    
            }
    

    The issue is that the int gameId comes back as 0 and it shouldn't. sense the selectitem[0].text should be and has displayed the Id number for that record. For strange reason. The only number it returns is 0.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    • Marked as answer by Joesoft11a Sunday, March 24, 2019 11:57 AM
    Saturday, March 23, 2019 3:16 PM

All replies

  • Hello,

    A better way to do this is load the ListBox with a DataTable with two fields, the primary key and the string value to display. So we remove a record by primary key rather than a string value.

    If we load in this case a product table (one I had handy)

    public DataTable ProductTable()
    {
        var dt = new DataTable();
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn })
            {
                cmd.CommandText = "SELECT CustomerIdentifier, CompanyName FROM Customers";
                cn.Open();
                dt.Load(cmd.ExecuteReader());
            }
        }
        return dt;
    }
    In the form I call the above method which is in a class named DataOperations in form shown event. Then in a button assert there is a selected item, if so get the primary key which would then be passed to your delete method. Assumption is the primary key exists in the database table you just read from which means the delete will work, if it does not and no exception is thrown means the record does not exists in the database table you are targeting. I've seen developers read from one table and somehow try and work on a record in a different database. A validation point would be to do a SELECT WHERE on the key to validate the record exists by primary key. Another problem I've seen is with attached SQL-Server databases that are an item in the project with "Copy to Output" folder setup to copy each time the project is built which overwrites changes. No matter with no exception my conclusion is the record is not in the table you targeted.

    using System;
    using System.Data;
    using System.Windows.Forms;
    using BaseLibrary;
    
    namespace WindowsFormsApp2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                var ops = new DataOperations();
                listBox1.DataSource = ops.ProductTable();
                listBox1.DisplayMember = "CompanyName";
            }
    
            private void removeButton_Click(object sender, EventArgs e)
            {
                if (listBox1.SelectedItem != null)
                {
                    // use this id to remove a record
                    var customerIdentifier = ((DataRowView) listBox1.SelectedItem)
                        .Row.Field<int>("CustomerIdentifier");
                }
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, March 22, 2019 6:50 PM
    Moderator
  • Karen, Thank you. Please try and remember that I'm not going to change all of that just to delete one record. Please use the code posted.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Friday, March 22, 2019 6:53 PM
  • Karen, Thank you. Please try and remember that I'm not going to change all of that just to delete one record. Please use the code posted.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    There is nothing wrong with the code you have other than the unknown of the value passed to the DELETE FROM statement so I can't assist with the code presented.

    I have never and would never delete a record by string value, always primary key.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, March 22, 2019 7:05 PM
    Moderator
  • Thanks again. I was about to ask that. So when deleting records all ways use the primary key. Great.

    how am I going to get the primary key for each game in that row. That's why it's not working.

    OK, look at this screen shot. If you look at the far bottom right of this window. Or form. You will see a list of games. All it does is display the short name for that game.

    You can't miss it. It's where it says "Games". If you look just below that is a bottom to delete each game. So how do I get the primary key for each game that's added.

    Karen I never used a datagrid before so I have no idea how to use that. I mite have to change it from a listbox to a listview. What do you think. I'm just not use. It other parts of my code I use the primary key. It's easy to do it that way. I need to see if i can find a tutorial on datagrids and how to use them

    • Edited by Joesoft11a Friday, March 22, 2019 7:51 PM Updated
    Friday, March 22, 2019 7:41 PM
  • Forgive the obvious question, but you're quite sure that there is a matching row is in the table?  Have you tried doing a quick "select" to validate that first?  Same capitalization, same punctuation?  Are the results different if you build the query yourself instead of using a parameter?

    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    Friday, March 22, 2019 7:50 PM
  • Read the primary key from the database table and not display it e.g. if this were a DataGridView where columns are generated by setting the DataSource you would hide it via ColumnMapping as per below. For the ListBox I've shown that already, no need for ColumnMapping.



    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, March 22, 2019 7:54 PM
    Moderator
  • Try calling ‘listBox1.Items.Clear()’ at the beginning of Loadgames.


    • Edited by Viorel_MVP Friday, March 22, 2019 8:25 PM
    Friday, March 22, 2019 8:24 PM
  • I tried this and nothing loaded. So I guess I'll have to wait for a reply from you Karen. I never done this before.

    public void Loadgames()
            {
    
                using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                {
                    conn.Open();
                    conn.ChangeDatabase("mlhelper");
                    var td = new DataTable();
    
                    string cmddb = "SELECT * FROM games";
    
                    using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                    {
                        //SqlDataReader reader = sqlCmd.ExecuteReader();
    
                        // How to clear the datagridview
                        //dataGridView1. Items.Clear();
    
                        //listBox1.Items.Add(reader["shortname"]);
    
                        td.Clear();
    
                        td.Columns.Add("Game Name");
                        td.Load(sqlCmd.ExecuteReader());
                        td.Columns["id"].ColumnMapping = MappingType.Hidden;
                            
    
                            
                        
                    }
    
                    conn.Close();
                }
            }


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Friday, March 22, 2019 8:30 PM
  • Okay here is a complete example which

    • Reads a SQL-Server database table and presents this data in a ListBox.
    • Provides code to ask permission to remove the current selected record in the ListBox and remove if they reply yes.

    VS2017 source code.

    Database is copied fresh on each build so you can try it many times.

    Data class

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApp3.Classes
    {
        public class DataOperations
        {
            private bool hasException;
            /// <summary>
            /// Permits caller to know if there was a runtime exception
            /// </summary>
            public bool HasException
            {
                get { return hasException; }
            }
    
            private Exception lastException;
            /// <summary>
            /// Provides last exception if hasException is true
            /// </summary>
            public Exception Exception
            {
                get { return lastException; }
            }
            /// <summary>
            /// Read database table
            /// </summary>
            /// <returns></returns>
            public DataTable ReadDataTable()
            {
                hasException = false;
    
                var dt = new DataTable();
                // Connection string has been setup in project settings
                using (var cn = new SqlConnection() { ConnectionString = Properties.Settings.Default.ConnectionString })
                {
                    using (var cmd = new SqlCommand() {Connection = cn})
                    {
                        cmd.CommandText = "SELECT Identifier,CompanyName FROM Customer";
                        try
                        {
                            cn.Open();
                            dt.Load(cmd.ExecuteReader());
                        }
                        catch (Exception ex)
                        {
                            hasException = true;
                            lastException = ex;
                        }
                    }
                }
    
                return dt;
            }
            /// <summary>
            /// Remove record by primary key
            /// </summary>
            /// <param name="pIdentifier">Existing primary key</param>
            /// <returns>success of delete operation</returns>
            public bool RemoveCustomer(int pIdentifier)
            {
                hasException = false;
                var success = false;
    
                using (var cn = new SqlConnection() { ConnectionString = Properties.Settings.Default.ConnectionString })
                {
                    using (var cmd = new SqlCommand() { Connection = cn })
                    {
                        cmd.CommandText = "DELETE FROM Customer WHERE Identifier = @Identifier";
                        try
                        {
                            cmd.Parameters.AddWithValue("@Identifier", pIdentifier);
                            cn.Open();
                            success = cmd.ExecuteNonQuery() == 1;
                            
                        }
                        catch (Exception ex)
                        {
                            hasException = true;
                            lastException = ex;
                        }
                    }
                }
    
                return success;
            }
        }
    }
    


    Form code

    using System;
    using System.Data;
    using System.Windows.Forms;
    using WindowsFormsApp3.Classes;
    using static WindowsFormsApp3.Classes.Dialogs;
    
    namespace WindowsFormsApp3
    {
        public partial class Form1 : Form
        {
            private BindingSource _bsCustomers = new BindingSource();
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                LoadTable();
            }
            /// <summary>
            /// Load DataTable from SQL-Server database table
            /// </summary>
            private void LoadTable()
            {
                var ops = new DataOperations();
                _bsCustomers.DataSource = ops.ReadDataTable();
                listBox1.DataSource = _bsCustomers;
    
                if (ops.HasException)
                {
                    removeButton.Enabled = false;
                    MessageBox.Show(ops.Exception.Message);
                    return;
                }
    
                listBox1.DisplayMember = "CompanyName";
            }
    
            private void removeButton_Click(object sender, EventArgs e)
            {
                // exit if not selection
                if (listBox1.SelectedItem == null) return;
    
                // use this id to remove a record
                var customerIdentifier = ((DataRowView)_bsCustomers.Current)
                    .Row.Field<int>("Identifier");
    
                // ask if they are sure, default button is no.
                if (Question($"Remove '{listBox1.Text}'"))
                {
                    var ops = new DataOperations();
                    // perform delete
                    if (ops.RemoveCustomer(customerIdentifier))
                    {
                        // since the record was removed now remove it from the ListBox
                        _bsCustomers.RemoveCurrent();
                        // disable remove button if no more items
                        removeButton.Enabled = _bsCustomers.Count > 0;
                    }
                }
            }
        }
    }
    

    Dialog helper

    using System.Diagnostics;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp3.Classes
    {
        public static class Dialogs
        {
            [DebuggerStepThrough]
            public static bool Question(string Text)
            {
                return (MessageBox.Show(
                            Text, 
                            Application.ProductName, 
                            MessageBoxButtons.YesNo, 
                            MessageBoxIcon.Question, 
                            MessageBoxDefaultButton.Button2) == DialogResult.Yes);
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, March 22, 2019 9:00 PM
    Moderator
  • I gave you my code so you could set that up and then you give me a project file that is too hard for me to under stand. Can't you just use my code. if you look at my code. I'm using a datagridview. not a listbox. I removed that and added in the datagridview. I thought that's what you wanted me to do.

    You have to make it simple.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Friday, March 22, 2019 9:49 PM
  • I can get this to work.

    public void Loadgames()
            {
    
                using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                {
                    conn.Open();
                    conn.ChangeDatabase("mlhelper");
                    
                    string cmddb = "SELECT * FROM games";
    
                    using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                    {
    
                        SqlDataAdapter adpt = new SqlDataAdapter(cmddb, conn);
                        DataTable td = new DataTable();
    
                        adpt.Fill(td);
                        dataGridView1.DataSource = td;    
                        
                    }
    
                    conn.Close();
                }
            }
    

    The issue is that it loads in every thing. I need to find some code that I can under stand.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Saturday, March 23, 2019 1:49 AM
  • OK, well I'll have to try and use a listbox.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Saturday, March 23, 2019 12:48 PM
  • I gave you my code so you could set that up and then you give me a project file that is too hard for me to under stand. Can't you just use my code. if you look at my code. I'm using a datagridview. not a listbox. I removed that and added in the datagridview. I thought that's what you wanted me to do.

    You have to make it simple.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    The reason I provided code that doesn't match your's is my code works while for whatever reason your's does not. Sure my code may be hard to understand but there is nothing written to say all code is easy to understand. I strive to provide the best code possible for a question asked which is true here. All I want to do is have you be successful, nothing more, nothing less.

    We all have been were you are now including myself and those who are willing will take code that works but hard to understand, study, learn and become better at coding.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, March 23, 2019 2:29 PM
    Moderator
  • yes I know. That's why I wanted to update this code and retire the project. I'm turning 60 in May and it's been crazy for me. See I removed the view grid and put a listview in it's place. I got it all working. I'm just having an issue getting the the ID from the list view for I can delete that record. and I keep getting a 0 returned. Makes no sense. Let me show you my code. If you want to help PLEASE use simple code. Anything else I won't under stand stand what your doing. The sample you sent me makes no sense to me. any way. Here

    Here's how I load in the games the user added.

    public void Loadgames()
            {
                listView1.SelectedItems.Clear();
    
                using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                {
                    conn.Open();
                    conn.ChangeDatabase("mlhelper");
    
                    string cmddb = "SELECT * FROM games";
    
                    using (SqlCommand cmd = new SqlCommand(cmddb, conn))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                
    
                                while (reader.Read())
                                {
    
    
                                    ListViewItem item = new ListViewItem(reader["id"].ToString());
                                    item.SubItems.Add(reader["shortname"].ToString());
                                    //item.SubItems.Add(reader["shortname"].ToString());
    
                                    listView1.Items.Add(item);
    
    
                                }
                            }
                        }
    
                    }
    
                    conn.Close();
                }
            }
    

    here's now I try and delete the record.

     private void btnDelete_Click(object sender, EventArgs e)
            {
                //Gets the record Id number to delete
                int gameId = Convert.ToInt32(listView1.SelectedItems[0].Text);
    
                if (gameId < 1)
                {
                    MessageBox.Show("Record Id Missing ",Application.ProductName);
                    return;
                }
    
                try
                {
                    using (SqlConnection conn = new SqlConnection())
                    {
                        conn.ConnectionString = @"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";
    
                        conn.Open();
                        conn.ChangeDatabase("mlhelper");
                        
                        string cmdd = "delete from games where id = @gameId";
                        string t;
    
                        using (SqlCommand cm = new SqlCommand(cmdd, conn))
                        {
    
                            cm.Parameters.AddWithValue("@gameId", gameId);
    
                            int rows = cm.ExecuteNonQuery();
                            
    
                            if(rows == 1)
                            {
                                t = " Completed ";
                            }
                            else
                            {
                                t = " Not Completed ";
                            }
    
                            conn.Close();
                            MessageBox.Show(string.Format("{0} rows deleted Id {1}", rows, gameId),Application.ProductName);
    
                        }                    
                    }
                }
                catch(Exception rr)
                {
                    MessageBox.Show(String.Format("Error {0} deleting Id {1}",rr.Message,gameId), Application.ProductName);
                }
    
                //Ask to delete json file. 
    
                if(DialogResult.Yes == MessageBox.Show("Delete Config file",Application.ProductName,MessageBoxButtons.YesNo))
                {
                    File.Delete(FullGamePath + listView1.SelectedItems[0].SubItems[1].Text.Trim() + ".json");
                }
    
    
                Loadgames();            
    
            }
    

    The issue is that the int gameId comes back as 0 and it shouldn't. sense the selectitem[0].text should be and has displayed the Id number for that record. For strange reason. The only number it returns is 0.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    • Marked as answer by Joesoft11a Sunday, March 24, 2019 11:57 AM
    Saturday, March 23, 2019 3:16 PM