In relation to my previous post. I was able to delete the rows in the datagridview (thanks to ema005), but now I am having problems with the updating of it in the database. It's deleting in my datagridview alright. But not in my database. My codes are already going on spaghetti just trying to figure it out. Can you help me with this? Here's my code:

This is from my separate class I made:

class dbConn
    {
        public SqlConnection myConn = new SqlConnection();
        public SqlDataAdapter da;
        public SqlDataAdapter da2;
        public SqlDataAdapter da3;
        SqlCommand Command;
        SqlCommand aCommand;
        SqlCommand rdrCommand;
        SqlDataReader rdr;
        SqlCommandBuilder cmdBld;
        public DataTable dt = new DataTable();
        public DataTable dt2 = new DataTable();
        public DataTable dt3 = new DataTable();
        public DataTable dTable = new DataTable();
        public DataSet ds = new DataSet();
        public DataSet ds2 = new DataSet();
        public DataSet ds3 = new DataSet();
        int check;
        int iRowIndex = 0;
        List<string> strArray = new List<string>();

public void OpenConnect()
        {
            myConn = new SqlConnection(@"Data Source =.\sqlexpress; integrated security = true; Initial Catalog = mmdaserver;");
        }

        public void DisplayInfo()
        {
            OpenConnect();
            Command = new SqlCommand("DisplayRecords", myConn);
            Command.CommandType = CommandType.StoredProcedure;
            aCommand = new SqlCommand("AlarmList", myConn);
            aCommand.CommandType = CommandType.StoredProcedure;

            da = new SqlDataAdapter(Command);
            da.Fill(ds, "Records");
            da = new SqlDataAdapter(aCommand);
            da.Fill(ds2, "Records");

            da2 = new SqlDataAdapter("SELECT * FROM DriverInfo", myConn);
            da3 = new SqlDataAdapter("SELECT * FROM Violations", myConn);
            da2.Fill(dt);
            da2.Fill(ds, "DriverInfo");
            da3.Fill(dt);
            da3.Fill(ds, "Violations");
            myConn.Close();
        }
}

And this is my code from my Form1

dbConn dbc = new dbConn();
        BindingSource bSrc = new BindingSource();
        Login thisFirst = new Login();
        
        public Form1()
        {
            InitializeComponent();
            dbc.DisplayInfo();
            dataGridRecords.AllowUserToDeleteRows = true;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            timer1.Start();
            groupBox1.Enabled = false;
            groupBox2.Enabled = false;
            groupBox3.Enabled = false;
            dataGridRecords.DataSource = null;
            dataGridAlarm.DataSource = null;
            dataGridViolations.DataSource = null;
            dataGridDriverInf.DataSource = null;
            thisFirst.ShowDialog();

            groupBox1.Enabled = true;
            groupBox2.Enabled = true;
            groupBox3.Enabled = true;
            RefreshGrid();
        }

        public void RefreshGrid()
        {
            dbc.ds.Clear();
            dbc.ds2.Clear();
            dbc.da.Update(dbc.dTable);
            dbc.DisplayInfo();
            dataGridRecords.DataSource = dbc.ds.Tables["Records"];
            dataGridAlarm.DataSource = dbc.ds2.Tables["Records"];
            dataGridViolations.DataSource = dbc.ds.Tables["Violations"];
            dataGridDriverInf.DataSource = dbc.ds.Tables["DriverInfo"];
        }

This is my delete button from this form:

private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to delete this row?", "Confirm deleting", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            
                if (dr == DialogResult.Yes)
                {
                    dbc.dTable = new DataTable("Records");
                    foreach (DataGridViewRow row in dataGridRecords.SelectedRows)
                    {
                        if (row.Index != dataGridRecords.Rows.Count - 1)
                        {
                            dbc.OpenConnect();
                            dbc.myConn.Open();
                            dbc.dTable = dbc.ds.Tables[0];
                            dbc.dTable.Rows[row.Index].Delete();
                            dbc.dTable.GetChanges();
                            dbc.dTable.AcceptChanges();
                            bSrc.DataSource = dbc.dTable;
                            dataGridRecords.DataSource = bSrc;
                            dbc.da.Update(dbc.dTable);
                        }
                    }

                    dbc.ds.GetChanges();

                    if (dbc.ds != null)
                    {
                        dbc.ds.AcceptChanges();
                    }
                    dbc.myConn.Close();
                }
       }

Recommended Answers

All 3 Replies

Hi,

Your problem is that you have deleted rows from the DataTable(this act like a cache but its not related to the fisical DB wich means that whatever you do in the DT doesn't reflecs in the DB) and not in the actual data base what you need to do is run delete domands(SQL) against the DB .

Regards,
Camilo

I see. How can I run delete queries in SQL specifying the row number then?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.