Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1389
  • Last Modified:

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

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
John500
Asked:
John500
  • 3
2 Solutions
 
MathiyazhaganCommented:
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
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
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
 
John500Author Commented:
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
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
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
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now