Solved

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

Posted on 2010-08-28
9
434 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

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.
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 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

22 Experts available now in Live!

Get 1:1 Help Now