Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

c# 2010 Updating SQL Server 2008 R2 with a DataGridView

Posted on 2012-08-14
2
Medium Priority
?
1,315 Views
Last Modified: 2012-08-14
Hi all,

I would like to be able to update changes I make in a DataGridView back to SQL Server.  I can populate a DataGridView using a DataAdaptor.  I can then Add rows to the DataGridView but I cannot seem to return those changes (UPDATE, ADD or DELETE) back to SQL Server.  I have looked through an awful lot of articles but cannot seem to get the simple code that I need.  I believe I may need a CommandBuilder but I just can't quite seem to make it happen.

I was hoping someone could give me some 'Update' button code for my project to make it work.  

Here is what I have done so far:


SQL Server 2008 R2

I have a local SQL Server 2008 database called ‘MyWorkers’.  In it I created a table called ‘tblPeople’ with a Primary Key and inserted three values:

CREATE TABLE tblPeople
(
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [nvarchar](50) NULL,
      CONSTRAINT [PK_tblPeople] PRIMARY KEY CLUSTERED ([ID] ASC)
 )

Insert into tblPeople(FirstName) VALUES ('Adrian')
Insert into tblPeople(FirstName) VALUES ('Brenda')
Insert into tblPeople(FirstName) VALUES ('Craig')


In Visual Studio 2010 C# > Create a New Windows Form Application

1.      Add a TextBox called ‘textBox1’.  Set the text to ‘select * from tblPeople’.

2.      Add a DataGridView called ‘dataGridView1’.

3.      Add a button called ‘Button1’.  Set the Text property to ‘Get Data’.

4.      Add a button called ‘Button2’.  Set the Text property to ‘Update’.

5.      I then added code to ‘Button1’ to connect to SQL Server and retrieve the contents of the table into a dataset and then I assign that to dataGridView1.  Here is the full code for that:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;  // Make sure this is included to be able to connect to SQL
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection con;
        DataSet ds;
        SqlDataAdapter da;

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            con = new SqlConnection();
            con.ConnectionString = "Data Source=.;Initial Catalog=MyWorkers;Integrated Security=True";
            ds = new DataSet();
            da = new SqlDataAdapter(textBox1.Text, con);
            con.Open();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            con.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // What do I put here ???
        }
    }
}

Could someone give me some Update code, please?

Many thanks,


Craig.
0
Comment
Question by:DATABAS3
  • 2
2 Comments
 

Accepted Solution

by:
DATABAS3 earned 0 total points
ID: 38291437
0
 

Author Comment

by:DATABAS3
ID: 38291443
http://csharp.net-informations.com/dataadapter/datagridview-sqlserver.htm

Here is the code just in case the page goes away..



using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        string connetionString;
        SqlConnection connection;
        SqlDataAdapter adapter;
        SqlCommandBuilder cmdBuilder;
        DataSet ds = new DataSet();
        DataSet changes;
        string Sql;
        Int32 i;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            connection = new SqlConnection(connetionString);
            Sql = "select * from Product";
            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(Sql, connection);
                adapter.Fill(ds);
                connection.Close();
                dataGridView1.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                cmdBuilder = new SqlCommandBuilder(adapter);
                changes = ds.GetChanges();
                if (changes != null)
                {
                    adapter.Update(changes);
                }
                MessageBox.Show("Changes Done");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

580 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