locked
HOW TO SAVE DATAGRIDVIEW DATA INTO A DATABASE

    Question

  • HI,

    KINDLY ASSIST ME. I HAVE SEARCH ALL OVER AND TRIED EVERYTHING BUT I CANNOT SEEM TO FIND THE SOLUTION TO THIS PROBLEM.I AM STUCK AT SAVING THE CONTENTS OF A DATAGRIDVIEW IN CSHARP.

    I AM CREATING AN INVENTORY SYSTEM AND I HAVE A DATAGRIDVIEW WITH 5 COLUMNS.I WANT TO SAVE EACH VALUE OF THE COLUMN AS ENTERED BY USER.

     

    KINDLY ASSIST.

    THANKS IN ADVANCE

     

    Friday, August 06, 2010 3:52 AM

Answers

  • Hi,

    Did you check the sqlcomm.ExecuteNonQuery();'s return value, if this value is 1, that means you have inserted 1 record successfully. If it's 0 then you don't have any record inserted.

    I have tested your code locally and didn't have the same problem you describe, all the record have been inserted fine. Here is the code I have used:

    sing System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace InsertTest
    {
      public partial class Form1 : Form
      {
        public Form1()
        {
          InitializeComponent();
        }
        public SqlConnection GetsqlCon()
        {
          string connstring = "Data Source=BINZEMIX-PC\\SQLEXPRESS;Initial Catalog=MyTestingDB;Integrated Security=True";//Persist Security Info=False";
          SqlConnection mycon = new SqlConnection(connstring);
          return mycon;
        }
    
        public bool getComm(string connstring1)
        {
          try
          {
            SqlConnection sqlcon = this.GetsqlCon();
            SqlCommand sqlcomm = new SqlCommand(connstring1, sqlcon);
            sqlcon.Open();
            sqlcomm.ExecuteNonQuery();
            sqlcomm.Dispose();
            sqlcon.Close();
            sqlcon.Dispose();
            return true;
          }
          catch (Exception ex)
          {
            return false;
          }
        }
    
        private void button1_Click(object sender, EventArgs e)
        {
          try
          {
            // string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
    
            string insert_sql = "INSERT INTO Person(PersonName,PersonAge)VALUES('" + col2 + "','" + col3 + "')";
    
            if (this.getComm(insert_sql))
            {
              MessageBox.Show("Insert Success");
            }
            else
            {
              MessageBox.Show("Insert Failed");
            }
            //this.getComm(insert_sql);
          }
          catch
          {
    
          }
    
        }
      }
    }
    

    Thanks

    Binze


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by MWASHUMA Wednesday, August 11, 2010 7:35 AM
    Wednesday, August 11, 2010 6:00 AM

All replies

  • Try the Windows Forms Data Controls and Databinding forum at http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/threads

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Friday, August 06, 2010 4:14 AM
  • //insert 1 record from datagridview 
    
    	public SqlConnection GetSqlCon()
        {
          string M_str_sqlcon = "Data Source=(local);Database=DB;user Id=sa;PWD=";
          SqlConnection myCon = new SqlConnection(M_str_sqlcon); 
    	  myCon.Open();
          return myCon;
        }
    	
    	public void getcom(string M_str_sqlstr)
        {
          SqlConnection sqlcon = this.GetSqlCon();
          SqlCommand sqlcom = new SqlCommand(M_str_sqlstr, sqlcon);
          sqlcom.ExecuteNonQuery();
          sqlcom.Dispose();
          sqlcon.Close();
          sqlcon.Dispose();
        }
    	
    	private void btnInsert_Click(object sender, EventArgs e)
        {
          try
          {
            string col1 = datagridview1[0, datagridview1s.CurrentCell.RowIndex].Value.ToString();
            string col2 = datagridview1[1, datagridview1.CurrentCell.RowIndex].Value.ToString();
            string col3 = datagridview1[2, datagridview1.CurrentCell.RowIndex].Value.ToString();
    
    		string insert_sql = "INSERT INTO YourTable(col1,col2,col3)VALUES('"+ col1 +"','"+ col2 +"','"+ col3 +"'");
    		this.getcom(insert_sql);
    	  }
    	  catch(Exception ex)
    	  { Message.Box(ex);}
    	 }
    
    
    


    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    • Proposed as answer by Lentucky Friday, August 06, 2010 7:11 AM
    Friday, August 06, 2010 4:57 AM
  • am really thankful for your quick responses.

    this is my code...but i still  cant save into the database.juts the same as yours but i cant still get it to save the data.thanks again.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace msn
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            public SqlConnection GetsqlCon()
            {
                string connstring = "Data Source=IT\\SQLEXPRESS;Initial Catalog=fairdealdb;Integrated Security=SSPI;Persist Security Info=False;";
                SqlConnection mycon = new SqlConnection(connstring);
                return mycon;
            }
            public void getComm(string connstring1)
            {
                SqlConnection sqlcon = this.GetsqlCon();
                SqlCommand sqlcomm = new SqlCommand(connstring1,sqlcon);
                sqlcomm.ExecuteNonQuery();
                sqlcomm.Dispose();
                sqlcon.Close();
                sqlcon.Dispose();
            }

            private void btnInsert_Click(object sender, EventArgs e)
            {
                try
                {
                    string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                    string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                    string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();

                    string insert_sql = "INSERT INTO datagrid(col1,col2,col3)VALUES('"+col1+"','"+col2+"','"+col3+"')";
                    this.getComm(insert_sql);
                }
                catch
                { }
                finally
                {
                    //sqlcom
                }
               
            }

        }
    }

    Friday, August 06, 2010 7:51 AM
  • please provide me error message about why you cannot save data to database
    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    Friday, August 06, 2010 8:38 AM
  • actually there is no error message. i could not also code the exception code in catch{}.

    after debugging, i would enter data into the data grid view and wen i click on the save button, there is no response. when i check the database, there is no data entered.

     

    the exception code....is it messagebox or message.box

     

    thanks again for your kind reply

    Friday, August 06, 2010 9:27 AM
  • actually there is no error message. i could not also code the exception code in catch{}.

    after debugging, i would enter data into the data grid view and wen i click on the save button, there is no response. when i check the database, there is no data entered.

     

    the exception code....is it messagebox or message.box

     

    thanks again for your kind reply

    You can use this to detect result
    
        public bool ExecuteSqlcommBool(string sqlComm)
        {
          try
          {
            SqlConnection sqlconn = this.GetsqlCon();
            SqlCommand sqlcomm = new SqlCommand(sqlComm, sqlconn);
            sqlcomm.ExecuteNonQuery();
            sqlcomm.Dispose();
            sqlconn.Close();
            sqlconn.Dispose();
            return true;
          }
          catch (Exception ex)
          {
            return false;
          }
        }
    
        private void btnInsert_Click(object sender, EventArgs e)
        {
          try
          {
            string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
    
            string insert_sql = "INSERT INTO datagrid(col1,col2,col3)VALUES('"+col1+"','"+col2+"','"+col3+"')";
    
            if(this.ExecuteSqlcommBool(insert_sql))
            { MessageBox.Show("Insert Success");}
            else
            { MessageBox.Show("Insert Failed");}
          }
          catch
          { }
          finally
          {
            //sqlcom
          }
          
        }
    


    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    Friday, August 06, 2010 11:52 AM
  • Here is a standard technique using ADO.Net for writing data from anywhere to a sql server.  In this sample, I have a datagridview with defined columns, and a dataTable also with defined columns.  I loop through the datagridview which I populate from the User Interface and copy the contents of the datagridview to the dataTable (which is contained in a dataset object).  Then I use a sqlDataAdapter to write the contents of the table to a matching table on the sql server.  Note: you need the same number of columns in the datagridview, your local datatable and the sqlserver table:

    using System;
    using System.Data;
    using System.Data.SqlClient;

    namespace xyz
    {
      public partial class frm1: Form
      {
        SqlDataAdapter daDgrv; SqlConnection connDgrv;
        Dataset1 dsDgrv;  //--this dataset contains the table I created
                          //--for collecting the user data from the datagridview
       
        private void frmDgrv_Load(object sender, EventArgs e)
        {
            connDgrv = new SqlConnection("Data Source=yourSvr;Initial Catalog=yourDB;Integrated Security=True");           
            daDgrv = new SqlDataAdapter();
            daDgrv.SelectCommand = new SqlCommand();
            daDgrv.SelectCommand.Connection = connDgrv;
            daDgrv.InsertCommand = new SqlCommand();
            daDgrv.InsertCommand.Connection = connDgrv;
            dsDgrv = new Dataset1();

            daDgrv.InsertCommand.Parameters.Add("@p1", SqlDbType.VarChar, 100, "fld1");
            daDgrv.InsertCommand.Parameters.Add("@p2", SqlDbType.VarChar, 100, "fld2");
            daDgrv.InsertCommand.Parameters.Add("@p3", SqlDbType.VarChar, 100, "fld3");
            daDgrv.InsertCommand.CommandText = "Insert Into tmpX Select @p1, @p2, @p3";
        }

        private void btnDgrvToSqlSvr_Click(object sender, EventArgs e)
        {
           DataRow dr;           

           foreach (DataGridViewRow dgvr in dgrv1.Rows)
           {
              if (dgvr.IsNewRow.Equals(false))
              {                  
                 dr = dsDgrv.tblTmpX.NewRow();  //--my local dataTable is called 'tblTmpX'
                 dr[0] = dgvr.Cells[1].Value == null ? "" : dgvr.Cells[1].Value.ToString();
                 dr[1] = dgvr.Cells[2].Value == null ? "" : dgvr.Cells[2].Value.ToString();
                 dr[2] = dgvr.Cells[3].Value == null ? "" : dgvr.Cells[3].Value.ToString();
                 dsDgrv.tblTmpX.Rows.Add(dr);
               }               
            }

            daDgrv.SelectCommand.CommandText = "Truncate Table tmpX";  //--empty out table in the sql server
            if (connDgrv.State == ConnectionState.Closed) connDgrv.Open();
            daDgrv.SelectCommand.ExecuteNonQuery();

            daDgrv.SelectCommand.CommandText = "Select * from tmpX";  //--get sql server table structure into memory
            daDgrv.Fill(dsDgrv, "tmpXX");

            DataTableReader reader = dsDgrv.tblTmpX.CreateDataReader();  //--here is where we write the contents
            dsDgrv.Tables["tmpXX"].Load(reader, LoadOption.Upsert);    //--of the local dataTable to the sqlsvr table
            daDgrv.Update(dsDgrv, "tmpXX");
          }
       }
    }

     

    Friday, August 06, 2010 6:33 PM
  • hi all, thanks fro your replies.this is  my coding so far and i hope you can correct me.the response am getting so far is "Insert Failed".i believe am astep closer to solving this problem.....below is my code.

    i also had a qusetion, why do we have to initialise the sqlconnection twice...i.e sqlcon and sqlconn....the same applies to sqlcommands and connection strings.....just for my knowledge

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace msn
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            public SqlConnection GetsqlCon()
            {
                string connstring = "Data Source=IT\\SQLEXPRESS;Initial Catalog=fairdealdb;Integrated Security=True;";//Persist Security Info=False";
                SqlConnection mycon = new SqlConnection(connstring);
                return mycon;
            }
            public bool getComm(string connstring1)
            {
                try
                {
                    SqlConnection sqlcon = this.GetsqlCon();
                    SqlCommand sqlcomm = new SqlCommand(connstring1, sqlcon);
                    sqlcomm.ExecuteNonQuery();
                    sqlcomm.Dispose();
                    sqlcon.Close();
                    sqlcon.Dispose();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

                 
                /*public bool ExecuteSqlcommBool(string sqlcomm)
                {
                    try
                    {
                        SqlConnection sqlconn = this.GetsqlCon();
                        SqlCommand sqlcomm = new SqlCommand(sqlcomm,sqlconn);
                        sqlcomm.ExecuteNonQuery();
                        sqlconn.Close();
                        sqlconn.Dispose();
                        return true;
                    }
                    catch(Exception ex)
                    {
                        return false;
                    }
                }
                 */
            private void btnInsert_Click(object sender, EventArgs e)
            {
                try
                {
                    string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                    string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                    string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();

                    string insert_sql = "INSERT INTO datagrid(col1,col2,col3)VALUES('"+col1+"','"+col2+"','"+col3+"')";

                    if(this.getComm(insert_sql))
                    {
                        MessageBox.Show("Insert Success");
                    }
                    else
                    {
                        MessageBox.Show("Insert Failed");
                    }
                    //this.getComm(insert_sql);
                }
                catch
                {
                   
                }
            }
            }

        }

     

     

     

    thanks again....totally appreciate you all

    Monday, August 09, 2010 8:55 AM
  • Hi,

    Try to add this line of code like following:

      SqlCommand sqlcomm = new SqlCommand("set arithabort on;", conn) 

    Reference:

    http://stackoverflow.com/questions/2970488/how-to-set-arithabort-on-for-connections-in-linq-to-sql

    Thanks

    Binze
     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 10, 2010 8:25 AM
  • hi again, i changed and added that line of code

    , SqlCommand sqlcomm = new SqlCommand ( "set arithabort on;" , sqlcon )

    and i got to the site u had recommended and i realised we had not opened the connection.so finaly wen i put the opening code, when i debug the program, i get insert success.....but in the database, there is nothing.

     

    kindly correct me ...i know am nearly there.

    here again is my code.thanks again.i appreciate  all your help.

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace msn
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            public SqlConnection GetsqlCon()
            {
                string connstring = "Data Source=IT\\SQLEXPRESS;Initial Catalog=fairdealdb;Integrated Security=True;";//Persist Security Info=False";
                SqlConnection mycon = new SqlConnection(connstring);
                return mycon;
            }
            public bool getComm(string connstring1)
            {
                try
                {
                    SqlConnection sqlcon = this.GetsqlCon();
                    //SqlCommand sqlcomm = new SqlCommand(connstring1, sqlcon);
                    SqlCommand sqlcomm = new SqlCommand("set arithabort on;", sqlcon);
                    sqlcon.Open();
                    sqlcomm.ExecuteNonQuery();
                    sqlcomm.Dispose();
                    sqlcon.Close();
                    sqlcon.Dispose();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

                 
                /*public bool ExecuteSqlcommBool(string sqlcomm)
                {
                    try
                    {
                        SqlConnection sqlconn = this.GetsqlCon();
                        SqlCommand sqlcomm = new SqlCommand(sqlcomm,sqlconn);
                        sqlcomm.ExecuteNonQuery();
                        sqlconn.Close();
                        sqlconn.Dispose();
                        return true;
                    }
                    catch(Exception ex)
                    {
                        return false;
                    }
                }
                 */
            private void btnInsert_Click(object sender, EventArgs e)
            {
                try
                {
                    string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                    string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                    string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();

                    string insert_sql = "INSERT INTO datagrid(col1,col2,col3)VALUES('"+col1+"','"+col2+"','"+col3+"')";

                    if(this.getComm(insert_sql))
                    {
                        MessageBox.Show("Insert Success");
                    }
                    else
                    {
                        MessageBox.Show("Insert Failed");
                    }
                    //this.getComm(insert_sql);
                }
                catch
                {
                   
                }
            }
            }

        }

    Wednesday, August 11, 2010 3:15 AM
  • Hi,

    Did you check the sqlcomm.ExecuteNonQuery();'s return value, if this value is 1, that means you have inserted 1 record successfully. If it's 0 then you don't have any record inserted.

    I have tested your code locally and didn't have the same problem you describe, all the record have been inserted fine. Here is the code I have used:

    sing System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace InsertTest
    {
      public partial class Form1 : Form
      {
        public Form1()
        {
          InitializeComponent();
        }
        public SqlConnection GetsqlCon()
        {
          string connstring = "Data Source=BINZEMIX-PC\\SQLEXPRESS;Initial Catalog=MyTestingDB;Integrated Security=True";//Persist Security Info=False";
          SqlConnection mycon = new SqlConnection(connstring);
          return mycon;
        }
    
        public bool getComm(string connstring1)
        {
          try
          {
            SqlConnection sqlcon = this.GetsqlCon();
            SqlCommand sqlcomm = new SqlCommand(connstring1, sqlcon);
            sqlcon.Open();
            sqlcomm.ExecuteNonQuery();
            sqlcomm.Dispose();
            sqlcon.Close();
            sqlcon.Dispose();
            return true;
          }
          catch (Exception ex)
          {
            return false;
          }
        }
    
        private void button1_Click(object sender, EventArgs e)
        {
          try
          {
            // string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
    
            string insert_sql = "INSERT INTO Person(PersonName,PersonAge)VALUES('" + col2 + "','" + col3 + "')";
    
            if (this.getComm(insert_sql))
            {
              MessageBox.Show("Insert Success");
            }
            else
            {
              MessageBox.Show("Insert Failed");
            }
            //this.getComm(insert_sql);
          }
          catch
          {
    
          }
    
        }
      }
    }
    

    Thanks

    Binze


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by MWASHUMA Wednesday, August 11, 2010 7:35 AM
    Wednesday, August 11, 2010 6:00 AM
  • you did it.....it has solved...i just took your code made a few changes for the indexes of the datagridview and it saved....thank you very much...i am very greatfful.....and to all....thank you very much......thank yo again...i totally  appreciate you
    Wednesday, August 11, 2010 7:35 AM
  • thank you very much....it has worked....am very greatful.....thanks to everyone too....this is a very good site...
    Wednesday, August 11, 2010 7:43 AM


  • // button save click event

            private void btSave_Click(object sender, EventArgs e)
            {

                try
                {
                    
    // connection  in mysql 
                    con = new SqlConnection();
                    con.ConnectionString = @"Password=manager$123;Persist Security Info=True;User ID=sa;Initial Catalog=test1;Data Source=Manasa-Laptop\sqlExpress";
                    con.Open();
    // table columnames in query my table is bill( billno, .................................)
                    string s2 = "insert into bill(billno,itemname,quantity,price,bill,Date) values(" + billno + ",@itemname,@quantity,@price,@bill,'" + lblDate.Text + "')";
                    /// @itemname..... are parameters in query , after taht we r passing values in datgrid
    // for adding the each row in datgridview in database.............               
    //  dgvOrderlist is my datagridview name
    for (int row = 0; row < dgvOrderlist.Rows.Count; row++)   or foreach( ) loop also u can
                    {
                        SqlCommand cmd1 = new SqlCommand(s2, con);
                        cmd1.Parameters.AddWithValue("@itemname", dgvOrderlist.Rows[row].Cells[0].FormattedValue.ToString());
                        cmd1.Parameters.AddWithValue("@quantity", Convert.ToInt32(dgvOrderlist.Rows[row].Cells[1].FormattedValue));
                        cmd1.Parameters.AddWithValue("@price", Convert.ToInt32(dgvOrderlist.Rows[row].Cells[2].FormattedValue));
                        cmd1.Parameters.AddWithValue("@bill", Convert.ToInt32(dgvOrderlist.Rows[row].Cells[3].FormattedValue));
                        cmd1.ExecuteNonQuery();

                    }

                    MessageBox.Show("Billno Is   :" + billno + "\nAmount is   :" + txtAmount.Text);
                    dgvOrderlist.DataSource = orderedlist;

                    
    //  for clearing datgridview for new one create an new object.......
    // orderedlist is my List<OredeedList>= new List<Orderedlist>();               
              orderedlist = new List<OrderedList>();
                    dgvOrderlist.DataSource = null;
                    dgvOrderlist.Rows.Clear();
                    dgvOrderlist.AutoGenerateColumns = false;
                    

                }
                catch (Exception ex)
                {
                    MessageBox.Show("error in inserting" + ex.Message);

                }

            }

    Saturday, August 14, 2010 12:00 PM