Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Saving changes in datagridview to sql database

Posted on 2010-11-27
10
Medium Priority
?
1,765 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 10

Accepted Solution

by:
P1ST0LPETE earned 2000 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

704 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