none
How to insert multiple records into a database table that are entered into a datagridview?

    Question

  • Hi Friends

    How to insert multiple records into a database table that are entered into a datagridview. With a single shot of Save all the records in the datagridview should stored into data table.

    for new records (multiple) entered by the user into empty datagridview and these records to be inserted into database. But not updating existing records in the datagridview.

    please any one give me the solution for this.....

    thank you

    Faisal.A

     

     

    Saturday, July 03, 2010 7:47 AM

Answers

  • Hi You have mentioned inserting multiple rows in datagridview.

    I have assumed you are entered multiple values in data grid and trying to save in database.

    For the sense you have to collect the data into DataTable.

    you want to insert multiple rows it means you can use SqlCommandBuilder class it will create (Insert/Update/Delete) command for your table.

    // getting values for No. of Rows and temporarily store in datatable

    for (int i = 1; i <= NoOfPlots; i++)

    {

    dr = dt.NewRow();

    dr["LayoutNo"] = LayoutNo;

    dr["PlotNo"] = i;

    dr["AreaSize"] = DBNull.Value;

    dt.Rows.Add(dr);

    }

    dataGridView1.Visible = true;

    dataGridView1.DataSource = dt;

     

    //update into sqlserver

    da = new SqlDataAdapter("Select * from REDetail", cn);

    SqlCommandBuilder builder = new SqlCommandBuilder(da);

    da.InsertCommand = builder.GetInsertCommand();

    da.Update(dt);

    • Marked as answer by Harry Zhu Monday, July 12, 2010 6:22 AM
    Sunday, July 04, 2010 8:09 AM

All replies

  • Hi !

    To Insert multiple records in datagridview you have to use SqlCommandBuilder it will automatically generate query for that...

    Sample Application Code

     

    using 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;

    using System.Configuration;

    namespace DataGridApplication

    {

    public partial class Form2 : Form

    {

    SqlConnection cn = new SqlConnection(ConfigurationSettings.AppSettings["RealEstateCn"].ToString());

    DataSet ds = new DataSet();

    DataTable dt = new DataTable();

    DataRow dr;

    SqlDataAdapter da;

    SqlDataAdapter da1;

    public Form2()

    {

    InitializeComponent();

    }

    private void Submit_Click(object sender, EventArgs e)

    {

    int LayoutNo = Convert.ToInt32(textBox1.Text);

    int NoOfPlots = Convert.ToInt32(textBox5.Text);

    da = new SqlDataAdapter("Select * from REDetail where LayoutNo = " + LayoutNo + "", cn);

    //da.Fill(ds,"REDetail");

    da.Fill(dt);

     

    for (int i = 1; i <= NoOfPlots; i++)

    {

    dr = dt.NewRow();

    dr["LayoutNo"] = LayoutNo;

    dr["PlotNo"] = i;

    dr["AreaSize"] = DBNull.Value;

    dt.Rows.Add(dr);

    }

    dataGridView1.Visible = true;

    dataGridView1.DataSource = dt;

    dataGridView1.Columns[0].ReadOnly = true;

    dataGridView1.Columns[1].ReadOnly = true;

    save.Visible = true;

    Submit.Visible = false;

    }

    private void Form2_Load(object sender, EventArgs e)

    {

    da = new SqlDataAdapter("Select max(LayoutID)+1 as Max from REstate", cn);

    da.Fill(ds);

    if (ds.Tables[0].Rows.Count > 0)

    {

    if (ds.Tables[0].Rows[0]["Max"].Equals(DBNull.Value))

    {

    textBox1.Text = "1";

    }

    else

    {

    textBox1.Text = ds.Tables[0].Rows[0]["Max"].ToString();

    }

    }

    }

    private void textBox5_TextChanged(object sender, EventArgs e)

    {

    Submit.Visible = true;

    }

    private void save_Click(object sender, EventArgs e)

    {

    da = new SqlDataAdapter("Select * from REDetail", cn);

    SqlCommandBuilder builder = new SqlCommandBuilder(da);

    da.InsertCommand = builder.GetInsertCommand();

    da.Update(dt);

    //da = new SqlDataAdapter("Select Sum(AreaSize) as TotalSqFt from REDetail", cn);

    //da.Fill(ds);

    //if (ds.Tables[0].Rows.Count > 0)

    //{

    // textBox4.Text = ds.Tables[0].Rows[0]["TotalSqFt"].ToString();

    //}

    int sum = 0;

    cn.Open();

    SqlCommand cmd = new SqlCommand("Select Sum(AreaSize) as Total from REDetail", cn);

    SqlDataReader dr = cmd.ExecuteReader();

    while (dr.Read())

    {

    string a = dr[0].ToString();

    int ab = Convert.ToInt32(a);

    sum += ab;

    }

    cn.Close();

    textBox4.Text = sum.ToString();

    int Total = 0;

    int PlotNo = 0;

    Total = Convert.ToInt32(textBox4.Text);

    PlotNo = Convert.ToInt32(textBox5.Text);

    da = new SqlDataAdapter("Insert into REstate values('" + textBox2.Text + "','" + textBox3.Text + "'," + Total + "," + PlotNo + ")", cn);

    da.Fill(ds);

    MessageBox.Show("Inserted");

    }

     

    }

    }

    Saturday, July 03, 2010 1:43 PM
  • tghank you for your reply

     

    it is not working correctly......please give me simple  sample code or just explain your code.

    i have confused with your code also.

     

    thank you

    reagrds

    Faisal.A

     

    Saturday, July 03, 2010 3:30 PM
  • Hi You have mentioned inserting multiple rows in datagridview.

    I have assumed you are entered multiple values in data grid and trying to save in database.

    For the sense you have to collect the data into DataTable.

    you want to insert multiple rows it means you can use SqlCommandBuilder class it will create (Insert/Update/Delete) command for your table.

    // getting values for No. of Rows and temporarily store in datatable

    for (int i = 1; i <= NoOfPlots; i++)

    {

    dr = dt.NewRow();

    dr["LayoutNo"] = LayoutNo;

    dr["PlotNo"] = i;

    dr["AreaSize"] = DBNull.Value;

    dt.Rows.Add(dr);

    }

    dataGridView1.Visible = true;

    dataGridView1.DataSource = dt;

     

    //update into sqlserver

    da = new SqlDataAdapter("Select * from REDetail", cn);

    SqlCommandBuilder builder = new SqlCommandBuilder(da);

    da.InsertCommand = builder.GetInsertCommand();

    da.Update(dt);

    • Marked as answer by Harry Zhu Monday, July 12, 2010 6:22 AM
    Sunday, July 04, 2010 8:09 AM