2 Replies - 15695 Views - Last Post: 25 April 2008 - 03:06 PM Rate Topic: -----

#1 quentinuys  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 9
  • Joined: 22-April 08

Cant Update SQL from DataGridview

Posted 23 April 2008 - 08:29 AM

Hi everyone

I am looking for a way to update a database with a button click from a datagridview. The code i have work. I change the data in the DataGridView and press my update button. When I reload the data, it stays updated. Even when I restart Visual Studio, but when i go to server explorer and view my database, everything goes back to normal.


I think my computer may be haunted.

Please Help me out...

public partial class Form3 : Form
	{
		
		private BindingSource bindingSource1 = new BindingSource();
		private SqlDataAdapter dataAdapter = new SqlDataAdapter();
		DataSet ds = new DataSet();

		public Form3()
		{

			InitializeComponent();
			dataGridView1.Dock = DockStyle.Fill;

			
			FlowLayoutPanel panel = new FlowLayoutPanel();
			panel.Dock = DockStyle.Top;
			panel.AutoSize = true;
			panel.Controls.AddRange(new Control[] { button1, button2 });

			this.Controls.AddRange(new Control[] { dataGridView1, panel });
			this.Text = "DataGridView databinding and updating demo";
		}

		private void invoiceToolStripMenuItem1_Click(object sender, EventArgs e)
		{

		}

		private void Form3_Load(object sender, EventArgs e)
		{
			dataGridView1.DataSource = bindingSource1;
		   // Utilities myUtilities = new Utilities();
		   // myUtilities.GetData("select * from Client");
		   GetData("select * from Client");

		}

		private void button1_Click(object sender, EventArgs e)
		{
			GetData(dataAdapter.SelectCommand.CommandText);
		   // Utilities myUtilities = new Utilities();
		   // myUtilities.GetData(dataAdapter.SelectCommand.CommandText);
		}

		private void button2_Click(object sender, EventArgs e)
		{
			UpdateDataBase("select * from Client");
			
			//dataAdapter.Update(ds,"Client");
			//this.dataGridView1.BindingContext[ds].EndCurrentEdit();
			//this.Update();
			//this.dataAdapter.Update(this.ds, "Client");

			
		}
		private void UpdateDataBase(string UpdateCommand)
		{
			try
			{
				// Specify a connection string. Replace the given value with a 
				// valid connection string for a Northwind SQL Server sample
				// database accessible to your system.
				String connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\FS.mdf;Integrated Security=True;User Instance=True";


				// Create a new data adapter based on the specified query.
				dataAdapter = new SqlDataAdapter(UpdateCommand, connectionString);
				dataAdapter.Fill(ds, "Client");
				// Create a command builder to generate SQL update, insert, and
				// delete commands based on selectCommand. These are used to
				// update the database.
				SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

				dataAdapter.Fill(ds);
				// Populate a new data table and bind it to the BindingSource.
				DataTable table = new DataTable();
				table.Locale = System.Globalization.CultureInfo.InvariantCulture;
			  
				dataAdapter.Update(table);
				bindingSource1.DataSource = table;

				// Resize the DataGridView columns to fit the newly loaded content.
				dataGridView1.AutoResizeColumns(
					DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
			}
			catch (SqlException)
			{
				MessageBox.Show("To run this example, replace the value of the " +
					"connectionString variable with a connection string that is " +
					"valid for your system.");
			}
		}

		private void GetData(string selectCommand)
		{
			try
			{
				// Specify a connection string. Replace the given value with a 
				// valid connection string for a Northwind SQL Server sample
				// database accessible to your system.
				String connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\FS.mdf;Integrated Security=True;User Instance=True";


				// Create a new data adapter based on the specified query.
				dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
				dataAdapter.Fill(ds, "Client");
				// Create a command builder to generate SQL update, insert, and
				// delete commands based on selectCommand. These are used to
				// update the database.
				SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
				
				dataAdapter.Fill(ds);
				// Populate a new data table and bind it to the BindingSource.
				DataTable table = new DataTable();
				table.Locale = System.Globalization.CultureInfo.InvariantCulture;
				dataAdapter.Fill(table);
				bindingSource1.DataSource = table;

				// Resize the DataGridView columns to fit the newly loaded content.
				dataGridView1.AutoResizeColumns(
					DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
			}
			catch (SqlException)
			{
				MessageBox.Show("To run this example, replace the value of the " +
					"connectionString variable with a connection string that is " +
					"valid for your system.");
			}
		}

		private void button3_Click(object sender, EventArgs e)
		{
			GetData(textBox1.Text);
		}

	   




Any help will be appreciated.

Is This A Good Question/Topic? 1
  • +

Replies To: Cant Update SQL from DataGridview

#2 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Cant Update SQL from DataGridview

Posted 25 April 2008 - 02:31 AM

Hmm, I can't understand why that would be happening.
Maybe it's only updating your Dataset table, and not your actual Database table? Try and access your Database independent of Visual Studio and check whether the changes are actually persisting to the DB...

Just taking a stab in the dark here. Not sure how to fix it though.
Was This Post Helpful? 0
  • +
  • -

#3 quentinuys  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 9
  • Joined: 22-April 08

Re: Cant Update SQL from DataGridview

Posted 25 April 2008 - 03:06 PM

View PostFootsie, on 25 Apr, 2008 - 02:31 AM, said:

Hmm, I can't understand why that would be happening.
Maybe it's only updating your Dataset table, and not your actual Database table? Try and access your Database independent of Visual Studio and check whether the changes are actually persisting to the DB...

Just taking a stab in the dark here. Not sure how to fix it though.


Hi Footsie. Thanks alot for the help.
I change the database location from |DataBase| To C:/
The database in the server explorer arnt the same it seems as the one running in my program.
And when i open the one in the server explorer, it updates my program database back to normal. :)

Thanks for the Info.

Do you know where i can get a good example of how to fill a datagridviewcombobox with another data table?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1