Solved

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

Posted on 2010-08-28
9
437 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Name space syntax error 12 38
.Net Web Site IIS Web.Config Content-Security-Policy 1 33
C# Single Form 8 26
Entity Framework 7 27
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

948 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

18 Experts available now in Live!

Get 1:1 Help Now