Solved

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

Posted on 2010-08-28
6
1,310 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

6 Experts available now in Live!

Get 1:1 Help Now