Solved

DataGridView - code to insert last row to MS SQL database using table adapter

Posted on 2010-08-28
6
1,315 Views
Last Modified: 2013-12-17
Greetings,

The picture below shows what I have going so far.  The applicatin will load the contents of the 'Person.Contact' table of AdventureWorks.  Afterwards, I need to enable the user to enter a person on the last row, yet not allow the user to edit any of the other rows.

Microsoft provides a sample using the Northwind database which looks like this:

Insert New Records Using TableAdapters
-------------------------------------------------------------------------------------
// Create a new row.
NorthwindDataSet.RegionRow newRegionRow;
newRegionRow = northwindDataSet.Region.NewRegionRow();
newRegionRow.RegionID = 5;
newRegionRow.RegionDescription = "NorthWestern";

// Add the row to the Region table
this.northwindDataSet.Region.Rows.Add(newRegionRow);

// Save the new row to the database
this.regionTableAdapter.Update(this.northwindDataSet.Region);
-------------------------------------------------------------------------------------

The code I'm using to populate the DataGridView is the snippet below.

Can anyone help me to finish this thing off ??
Thanks !

using System;
using System.Configuration.Assemblies;
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 AdvWorksFormApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            InitializeDataGridView();
            //SetConnection();

        }

        private void SetConnection()
        {
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                    MessageBox.Show("Connection Opened!", "Connection Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (SqlException exc)
            {
                MessageBox.Show("EXCEPTION TRAPED: Technical Report\n" + exc.ToString());
            }
        }

        private void InitializeDataGridView()
        {
            
            try
            {
                // Set up the DataGridView.
                //dataGridView1.Dock = DockStyle.Fill;

                // Automatically generate the DataGridView columns.
                dataGridView1.AutoGenerateColumns = true;
                dataGridView1.AllowUserToAddRows = true;

                // Set up the data source.
                bindingSource1.DataSource = GetData("SELECT Title, FirstName As 'First Name'," +
                                                    "MiddleName As 'Middle'," +
                                                    "LastName As 'Last Name'," +
                                                    "EmailAddress As 'Email'," + 
                                                    "Phone FROM Person.Contact");
                //bindingSource1.DataSource = GetData("SELECT * FROM Person.Contact");
                dataGridView1.DataSource = bindingSource1;

                // Automatically resize the visible rows.
                dataGridView1.AutoSizeRowsMode =
                    DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;

                // Set the DataGridView control's border.
                dataGridView1.BorderStyle = BorderStyle.Fixed3D;

                // Put the cells in edit mode when user enters them.
                dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter;

            }
            catch (SqlException)
            {
                MessageBox.Show("Unable to connect to the database specified!", "ERROR",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                System.Threading.Thread.CurrentThread.Abort();
            }
        }

        private static DataTable GetData(string sqlCommand)
        {
            string connectionString = "Integrated Security=SSPI;" +
                "Persist Security Info=False;" +
                "Initial Catalog=AdventureWorks;Data Source=localhost";

            SqlConnection northwindConnection = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand(sqlCommand, northwindConnection);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;

            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adapter.Fill(table);

            return table;
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'adventureWorksDataSet.Contact' table. You can move, or remove it, as needed.
            //this.contactTableAdapter.Fill(this.adventureWorksDataSet1.Contact);

        }
    }
}

Open in new window

Loaded-table.JPG
0
Comment
Question by:John500
  • 3
6 Comments
 
LVL 10

Assisted Solution

by:Mathiyazhagan
Mathiyazhagan earned 200 total points
ID: 33552164
Hi,

   While you allowing user to add a row, you should allow user to edit rows. so, you should restrict user to edit row while current row is not new row.you can do this with "RowEnter"event of datagridview.I have attached sample code .
private void dgv_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (dgv.Rows[e.RowIndex].IsNewRow)
                dgv.EditMode = DataGridViewEditMode.EditOnEnter;
            else
                dgv.EditMode = DataGridViewEditMode.EditProgrammatically;  //restrict users to edit
        }

Open in new window

0
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 300 total points
ID: 33552944
Hi,
 
 When user enter data in datagridview the same data gets save in  database back. And yes the same data which has been saved cannot be  modifed further.
 
 There are two buttons and one datagridview on form. One of the button is  to insert data or save data in database and the second one is to  refresh the data.
 Regards,
 VSS

----------------
// App.config
----------------
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add name="MyCon" connectionString="Data Source=ENCORE; Initial Catalog=CarDB; User ID=sa; Password=god; Integrated Security=false"/>
	</connectionStrings>
</configuration>

-------------------
// Form1.cs
-------------------
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 ee_DataGrid2
{
    public partial class Form1 : Form
    {
        private BindingSource bindingSource1 = new BindingSource();
        SqlDataAdapter adapter;
        DataTable dt;

        public Form1()
        {
            InitializeComponent();
            dataGridView1.AllowUserToAddRows = true;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            bindingSource1.DataSource = this.GetData("Select * From TestEE");
            dataGridView1.DataSource = bindingSource1;

            DataGridViewCellCollection collection = dataGridView1.Rows[0].Cells;

            // Making cells readonly and hence cannot be edited
            foreach (DataGridViewCell cell in collection)
            {
                if (cell.Value.ToString() != String.Empty)
                {                  
                    cell.ReadOnly = true;
                }
            }
        }

        private DataTable GetData(string sqlCommand)
        {
            dt = new DataTable();

            try
            {
                string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString;

                SqlConnection con = new SqlConnection(connectionString);

                adapter = new SqlDataAdapter(sqlCommand, con);

                // SqlCommandBuilder automatically generates SQL insert, update,
                // and delete statements to rectify changes made to DataTable
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                adapter.Fill(dt);
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.ToString());
            }

            return dt;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                // Update any changes made in DataGridView
                adapter.Update(dt);
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            dataGridView1.Refresh();
        }

    }
}

Open in new window

0
 

Author Comment

by:John500
ID: 33554870
Thanks for the input.

Mathiyazhagan - I get the idea but I don't know how to call dgv_RowEnter.  You don't mention anything about that.  Also, my main objective is to insert the data from the last row into th SQL database.  That's what I don't know how to do.   Looks like you provided one of the steps that preceeds the necessary code.  However, I still don't have ANY necessary code to perform an insert.  Niether do I have a clue on how that's done.

vs00saini - I compiled and ran your code.  Form1_Load() is never called.  The program runs, the Form is launched (somehow) and the DataGridView is seen.  However, there is no data in the grid.

When I set a break point in Form1 to debug, it is clear the code is never executed.  Also, when I look at your code I see *nothing* that performs an insert.  Are you telling me the workd *insert* is never even used for code like this?

The code I posted above looks like this:

-------------------------------------------------------------------------------------
// Create a new row.
NorthwindDataSet.RegionRow newRegionRow;
newRegionRow = northwindDataSet.Region.NewRegionRow();
newRegionRow.RegionID = 5;
newRegionRow.RegionDescription = "NorthWestern";

// Add the row to the Region table
this.northwindDataSet.Region.Rows.Add(newRegionRow);

// Save the new row to the database
this.regionTableAdapter.Update(this.northwindDataSet.Region);
-------------------------------------------------------------------------------------

However, I don't have the faintest clue on how to make this work.  I don't even know if this code is the right thing for me because there appears to be many ways to do this.......

John





0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33555083
Hi,

vs00saini - I compiled and ran your code.  Form1_Load() is never called.

(1) Remove the Form1_Load code from your code. And now double click the Form and you would see Load event handler in your code. Or you can find the Load event from Event in Properties window of Form. Double click from there. And write the code provided by me in Load.

(2) Also change your App.config file according to your database. Enter your user id and password specified while installing sql server.

Write back for help. The code provided is tested and its working fine.

Regards,
VSS


0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33555120
Hi,

Also, when I look at your code I see *nothing* that  performs an insert.  Are you telling me the workd *insert* is never even  used for code like this?

We have bind our datagridview with BindingSource. And bindingSource is getting its data from DataTable (see GetData method in code). So we have used SqlCommandBuilder and below is the comment about it:

// SqlCommandBuilder automatically generates SQL insert, update,
                 // and delete statements to rectify changes made to  DataTable
                SqlCommandBuilder builder = new  SqlCommandBuilder(adapter);

so this automatically make changes reflected in database. And for insert we are using

 // Update any changes made in DataGridView
                adapter.Update(dt);


that is adapter is updating values in DataTable and as the datatable values get updated the same gets save in database. Simple.

Regards,
VSS
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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