Solved

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

Posted on 2010-08-28
9
453 Views
Last Modified: 2013-12-17
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
Comment
Question by:John500
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33549626
so you want the insert update delete page for your table in adventure database ?
0
 
LVL 9

Expert Comment

by:puru1981
ID: 33549629
0
 

Author Comment

by:John500
ID: 33549686
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 10

Assisted Solution

by:Jini Jose
Jini Jose earned 50 total points
ID: 33549721
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
 
LVL 9

Expert Comment

by:puru1981
ID: 33549737
0
 
LVL 9

Assisted Solution

by:puru1981
puru1981 earned 50 total points
ID: 33549740
0
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 400 total points
ID: 33552935
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
 

Author Comment

by:John500
ID: 33554769
vs00saini,

Thanks but sorry, that code don't work...
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

696 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