Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-28
9
Medium Priority
?
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:Jini Jose
Jini Jose earned 200 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 200 total points
ID: 33549740
0
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 1600 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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