Solved

Saving changes in datagridview to sql database

Posted on 2010-11-27
10
1,642 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now