Solved

Saving changes in datagridview to sql database

Posted on 2010-11-27
10
1,707 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:98fatboyrider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 34222856
kindly go through this it will help:-

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

0
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34223093
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
 

Author Comment

by:98fatboyrider
ID: 34223327
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 10

Accepted Solution

by:
P1ST0LPETE earned 500 total points
ID: 34225767
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
 

Author Comment

by:98fatboyrider
ID: 34259378
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
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34294572
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
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34294603
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
 

Author Comment

by:98fatboyrider
ID: 34297942
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
 
LVL 10

Assisted Solution

by:P1ST0LPETE
P1ST0LPETE earned 500 total points
ID: 34301399
Change it to this:

string deleteSQL = "DELETE FROM [tblMovies] WHERE [MovieID] = " + id;
0
 

Author Closing Comment

by:98fatboyrider
ID: 34325106
I still need to workout an issue with the connection string but, this was a great help. Thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question