Saving changes in datagridview to sql database

Building on an earlier question, once the record had been removed from the datagridview, the changes are not saved to the database.
//Delete button click event
void dgvAllMovies_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {  
             // remove the row in the datasource which will automatically remove row from the grid                
                    DataGridViewRow grigRow = dgvAllMovies.Rows[e.RowIndex];
                    DataRowView drv = grigRow.DataBoundItem as DataRowView;
                    DataTable dt = (DataTable)dgvAllMovies.DataSource;
                    //Delete confermation
                    if (MessageBox.Show("Delete "+ drv.Row[1].ToString()+"?","Confirm!", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        dt.Rows.Remove(drv.Row);
                    }
                }
                else
                {
                    return;
                }
            }

 //Save button
private void btnSave_Click(object sender, EventArgs e)
        {
            {
                DataTable dt = (DataTable)dgvAllMovies.DataSource;
                 DAL data = new DAL();
                data.SaveData(dt, "select * from tblMovies");
            }

//DAL method
public void SaveData(DataTable dataTable, string sourceQuery)
 {
     using (SqlConnection conn = new SqlConnection(ConnectionString))
     {
         conn.Open();

         SqlDataAdapter adapter = new SqlDataAdapter();

         adapter.SelectCommand = new SqlCommand(sourceQuery, conn);

         SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

         adapter.Update(dataTable);
     }
 }

Open in new window

98fatboyriderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BuggyCoderCommented:
kindly go through this it will help:-

http://support.microsoft.com/kb/308507

0
P1ST0LPETECommented:
1. Your btnSave_Click() method is only passing a SELECT statement to your SaveData() method.  This doesn't delete anything from the Database.
2. Your SaveData() method is also only built to run a SELECT command and fill a table.

Based on how you have it setup, I assume you want to not make any changes until the user hits the *save* button.  In this case you need to compare the original data  with the current data in the DataGridView.  Something like this:


private void btnSave_Click(object sender, EventArgs e)
{
    DAL data = new DAL();
    DataTable currentData = (DataTable)dgvAllMovies.DataSource;
    DataTable orginalData = new DataTable();
    data.SaveData(originalData, "SELECT * FROM [tblMovies]");  //I'm sure you have another way to load data, but this would work.

    foreach(DataRow originalRow in orignalData.Rows)
    {
        bool rowFound = false;
        
        foreach(DataRow currentRow in currentData.Rows)
        {
            if(currentRow == originalRow)
            {
                rowFound = true;
                break;
            }
        }

        if(rowFound == false)
        {
            string deleteSQL = "DELETE FROM [tlbMovies] WHERE [ID] = " originalRow["ID"].ToString();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(deleteSQL, conn);
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Open in new window

0
98fatboyriderAuthor Commented:
I'd like to commit the change after the confirmation message box, I don't need a separate save button. There seems to be an issue with the delete statement at "originalRow. Will the code below work?
void dgvAllMovies_CellContentClick(object sender, DataGridViewCellEventArgs e)
        { 
            if (e.ColumnIndex == 0)
            {  
               // remove the row in the datasource which will automatically remove row from the grid                
               DataGridViewRow grigRow = dgvAllMovies.Rows[e.RowIndex];
               DataRowView drv = grigRow.DataBoundItem as DataRowView;
               DataTable dt = (DataTable)dgvAllMovies.DataSource;
                //Delete confermation
                 if (MessageBox.Show("Delete "+ drv.Row[1].ToString()+"?","Confirm!", MessageBoxButtons.YesNo) == DialogResult.Yes)
                  {
                   dt.Rows.Remove(drv.Row);
                   DAL data = new DAL();
                   DataTable currentData = (DataTable)dgvAllMovies.DataSource;
                   DataTable orginalData = new DataTable();
                   data.SaveData(originalData, "SELECT * FROM [tblMovies]");
                 
                     foreach(DataRow originalRow in orignalData.Rows)
                        {
                            bool rowFound = false;
        
                             foreach(DataRow currentRow in currentData.Rows)
                            {
                                if(currentRow == originalRow)
                                  {
                                    rowFound = true;
                                     break;
                                   }
                            }

                                if(rowFound == false)
                                {
                                    string deleteSQL = "DELETE FROM [tblMovies] WHERE [ID] = "originalRow[ID].ToString();
                                    SqlConnection conn = new SqlConnection(ConnectionString);
                                    SqlCommand cmd = new SqlCommand(deleteSQL, conn);
                                    cmd.Connection.Open();
                                    cmd.ExecuteNonQuery();
                                }
                            }
                         }
                       }
 
                  else
                   {
                     return;
                   }
            }

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

P1ST0LPETECommented:
Ok, if you want to commit the change in the database right after the confirmation box, then things become a lot easier.

You should have an index or "ID" column in your tlbMovies right?  So you should be able to do this:
 
void dgvAllMovies_CellContentClick(object sender, DataGridViewCellEventArgs e)
{ 
     if (e.ColumnIndex == 0)
     {    
         DataGridViewRow gridRow = dgvAllMovies.Rows[e.RowIndex]
         DataRowView drv = gridRow.DataBoundItem as DataRowView;
         
         //Delete confermation
         if (MessageBox.Show("Delete "+ drv.Row[1].ToString()+"?","Confirm!", MessageBoxButtons.YesNo) == DialogResult.Yes)
         {
             //Replace "ID" with column name of the index column in tlbMovies:
             string id = gridRow.Cells["ID"].Value.ToString();

             string deleteSQL = "DELETE FROM [tblMovies] WHERE [ID] = id;
             SqlConnection conn = new SqlConnection(ConnectionString);
             SqlCommand cmd = new SqlCommand(deleteSQL, conn);
             try
             {
                 cmd.Connection.Open();
                 cmd.ExecuteNonQuery();
             }
             catch(Exception ex)
             {
                 //Uncomment this code when testing/developing: 
                 //MessageBox.Show("Error: " + ex.ToString());
             }
             finally
             {
                 cmd.Connection.Close();
                 cmd.Dispose();
                 conn.Dispose();
             }
         }

         //Once delete has finished, reload the DataSource from the database, and rebind the DataSource to the DataGridView.

         //Again, you probably have a better method to load data (like how you load the DataGridView when the app first runs)
         //But this would work as well:
         DataTable dbData = new DataTable();
         data.SaveData(dbData, "SELECT * FROM [tblMovies]");
     }
}

Open in new window

However, I'm assuming that DAL stands for Data Access Layer?  In your DAL class you probably want to have a generic method named "ExecuteNonQuery" that receives an sql string as a parameter.  Then you do all the Database work in your database class.  If you make all the classes in your database (DAL) class generic, then you can copy/paste and reuse this class in multiple projects in the future.  Something like this:
 
public void ExecuteNonQuery(string SQL)
{
    try
    {
        conn = new SqlConnection(conn);
        cmd = new SqlCommand(SQL, conn);
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        //Handle error.....
    }
    finally
    {
        cmd.Connection.Close();
        cmd.Dispose();
        conn.Dispose();
    }
}

Open in new window

And a generic way to fill a DataTable like this:
 
public DataTable DataTable(string SQL)
{
    DataTable dt = new DataTable();
    try
    {
        conn = new SqlConnection(conn);
        da = new SqlDataAdapter(SQL, conn);
        da.Fill(dt);
    }
    catch (Exception ex)
    {
        //Handle error.....
    }
    finally
    {
        da.Dispose();
        conn.Dispose();
    }
    return dt;
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
98fatboyriderAuthor Commented:
Sorry for the delayed response. I'm reworking my data access layer class to include the generic method and datatable you recommended. I will post my updated code this weekend. Thank you.
0
P1ST0LPETECommented:
I have a Database class that I just copy/paste and use in many of my projects.
Looks like this:
 
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace YourProject.Classes
{
    public static class Database
    {
        private static SqlConnection connnection = null;
        private static SqlCommand command = null;
        private static SqlDataAdapter dataAdapter = null;
        private static String connString = ConfigurationManager.ConnectionStrings["YourConnectionStringFromWeb.Config"].ToString();
        private static String Error = string.Empty;

        public static void ExecuteNonQuery(string SQL)
        {
            try
            {
                connnection = new SqlConnection(connString);
                command = new SqlCommand(SQL, connnection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.ExecuteNonQuery()");
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
                connnection.Dispose();
            }
        }

        public static String ExecuteScalar(string SQL)
        {
            string result = string.Empty;

            try
            {
                connnection = new SqlConnection(connString);
                command = new SqlCommand(SQL, connnection);
                command.Connection.Open();
                result = command.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.ExecuteScalar()");
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
                connnection.Dispose();
            }

            return result;
        }

        public static SqlDataReader LoadSqlReader(string SQL)
        {
            SqlDataReader reader = null;
            try
            {
                connnection = new SqlConnection(connString);
                command = new SqlCommand(SQL, connnection);
                command.Connection.Open();
                reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.LoadSqlReader()");

                command.Connection.Close();
                command.Dispose();
                connnection.Close();
            }
            return reader;
        }

        public static DataTable GetDataTable(string SQL)
        {
            DataTable table = new DataTable();
            try
            {
                connnection = new SqlConnection(connString);
                dataAdapter = new SqlDataAdapter(SQL, connnection);
                dataAdapter.Fill(table);
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.FillDataTable()");
            }
            finally
            {
                dataAdapter.Dispose();
                connnection.Dispose();
            }
            return table;
        }

        public static class StoredProcedure
        {
            public static void ExecuteNonQuery(String StoredProcedureName, SqlParameter[] Parameters, Connections dbconnection)
            {
                try
                {
                    connnection = new SqlConnection(connString);
                    command = new SqlCommand(StoredProcedureName, connnection);
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in Parameters)
                        command.Parameters.Add(parameter);

                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    string error = "Stored Procedure Name: '" + StoredProcedureName + "'\r\n\t" + ex.ToString();
                    SystemErrors.RecordError(error, "Database.StoredProcedure.ExecuteNonQuery()");
                }
                finally
                {
                    command.Connection.Close();
                    command.Dispose();
                    connnection.Dispose();
                }
            }

            public static String ExecuteScalar(String StoredProcedureName, SqlParameter[] Parameters, Connections dbconnection)
            {
                string result = string.Empty;
                try
                {
                    connnection = new SqlConnection(connString);
                    command = new SqlCommand(StoredProcedureName, connnection);
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in Parameters)
                        command.Parameters.Add(parameter);

                    command.Connection.Open();
                    result = command.ExecuteScalar().ToString();
                }
                catch (Exception ex)
                {
                    string error = "Stored Procedure Name: '" + StoredProcedureName + "'\r\n\t" + ex.ToString();
                    SystemErrors.RecordError(error, "Database.StoredProcedure.ExecuteScalar()");
                }
                finally
                {
                    command.Connection.Close();
                    command.Dispose();
                    connnection.Dispose();
                }
                return result;
            }

            public static DataTable GetDataTable(String StoredProcedureName, SqlParameter[] Parameters, Connections dbconnection)
            {
                DataTable table = new DataTable();
                try
                {
                    connnection = new SqlConnection(connString);
                    command = new SqlCommand(StoredProcedureName, connnection);
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in Parameters)
                        command.Parameters.Add(parameter);

                    dataAdapter = new SqlDataAdapter(command);
                    dataAdapter.Fill(table);
                }
                catch (Exception ex)
                {
                    string error = "Stored Procedure Name: '" + StoredProcedureName + "'\r\n\t" + ex.ToString();
                    SystemErrors.RecordError(error, "Database.StoredProcedure.FillDataTable()");
                }
                finally
                {
                    dataAdapter.Dispose();
                    command.Dispose();
                    connnection.Dispose();
                }
                return table;
            }
        }
    }
}

Open in new window


So with it being static, you can do something easy like this:

DataTable table = Database.GetDataTable("SELECT * FROM [TableName]");

Also, as you may have guessed, "SystemErrors" is another static class I have created for storing my application errors.
0
P1ST0LPETECommented:
oops. forgot to delete the "Connections dbconnection" from the stored proc methods.  Forgot to strip that out before I posted.  Just remove it from the method and it should work just fine.
0
98fatboyriderAuthor Commented:
I think this is close but I'm getting a "Newline in constraint" error on the DELETE statement below.
string deleteSQL = "DELETE FROM [tblMovies] WHERE [MovieID] = id;

Open in new window

0
P1ST0LPETECommented:
Change it to this:

string deleteSQL = "DELETE FROM [tblMovies] WHERE [MovieID] = " + id;
0
98fatboyriderAuthor Commented:
I still need to workout an issue with the connection string but, this was a great help. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.