• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

How to insert one record from a DataGridView to a MSSQL database table

Greetings,

I am trying to understand how to perform an insert to a table and I'm using the AdventurWorks database with a C# DataGridView.  The picture below shows what I have so far.  That is, I am able to load the table into my DataGridView.

I am new to this stuff so I don't know what the particulars are from here.  I could read umptine articles on this subject from the Internet but I'm hoping somebody could provide the code that will work with what I have.

The code I have so far is seen in the snippet below.  What code (a method to call) is necessary to add one person with these fields:

      [FirstName] [dbo].[Name] NOT NULL,
      [LastName] [dbo].[Name] NOT NULL,
      [EmailPromotion] [int] NOT NULL CONSTRAINT
      [PasswordHash] [varchar](40) NOT NULL,
      [PasswordSalt] [varchar](10) NOT NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT
      [ModifiedDate] [datetime] NOT NULL CONSTRAINT

For right now, I'm not worried about cell validation.  I can open another question for that later.

Thanks in advance

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

        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.adventureWorksDataSet.Contact);

        }
    }
}

Open in new window

table-loaded.JPG
0
John500
Asked:
John500
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Jini JoseSenior .Net DeveloperCommented:
so you want the insert update delete page for your table in adventure database ?
0
 
John500Author Commented:
gmailjini -  I just want to be able to insert a new contact at the last row of the Form application above.  That picture shows I am able to load the table....  I should have taken a shot of the last row where it provides a blank row for a new entry.  I'm not worried about updates or deletes, only inserts.

puru1981 - I looked at that article but it says:

"The GridView was not designed to insert new rows, but there is a way to accomplish this with very little code."

That article doesn't apply because I'm using a DataGridView and I know this is possible.  I'm guessing the code would look something like this:

row.BeginInsert ();
row ["Title"]        = txtTitle.Text;
row ["FirstName"]    = txtFirstName.Text;
row ["MiddleName"]   = txtMiddleName.Text;
row ["LastName"]     = txtLastName.Text;
row ["Suffix"]       = txtSuffix.Text;
row ["Phone"]        = txtPhone.Text;
row ["EmailAddress"] = txtEmailAddress.Text;
row.EndEdit ();




0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Jini JoseSenior .Net DeveloperCommented:
you can add it through datagridview. either through the defalut insert/update throgh the datagirdview or you can write your own.

for that you can add the curresponding controls that matches your database table. and place a insert/update button. write code to insert the data to database .

on the selected index change event of the datagridview assign the values to that controls from the datagridview.


0
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
Hi,

First of all let me to share some information about the code attach here.

So 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 in either way.

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 but I think that is not so much necessary.

Regards,
VSS

--------------------
//App.config
--------------------
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add name="MyCon" connectionString="Data Source=ENCORE; Initial Catalog=YourDatabase; User ID=sa; Password=pass; 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)
        { 
            // Select query for Table TestEE in database
            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:
vs00saini,

Thanks but sorry, that code don't work...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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