Home » C# ProgrammingRSS

HOW TO SAVE DATAGRIDVIEW DATA INTO A DATABASE

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

 

 

14 Answers Found

 

Answer 1

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

Answer 2

//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;
    }
	
	publicvoid 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();
    }
	
	privatevoid 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);}
	 }



 

Answer 3

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
            }
           
        }

    }
}

 

Answer 4

please provide me error message about why you cannot save  data to database
 

Answer 5

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

 

Answer 6

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

    publicbool ExecuteSqlcommBool(string sqlComm)
    {
      try
      {
        SqlConnection sqlconn = this.GetsqlCon();
        SqlCommand sqlcomm = new SqlCommand(sqlComm, sqlconn);
        sqlcomm.ExecuteNonQuery();
        sqlcomm.Dispose();
        sqlconn.Close();
        sqlconn.Dispose();
        returntrue;
      }
      catch (Exception ex)
      {
        returnfalse;
      }
    }

    privatevoid 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
      }
      
    }

 

Answer 7

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");
      }
   }
}

 

 

Answer 8

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

 

Answer 9

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
 

 

Answer 10

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
            {
               
            }
        }
        }

    }

 

Answer 11

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

 

Answer 12

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
 

Answer 13

thank you very much....it has worked....am very greatful.....thanks to everyone too....this is a very good site...
 

Answer 14



// 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);

            }

        }

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter