Edit datagrid and make changes to dataset

[Ken]

Hello Everyone,

I am now convinced that half a solution is still a lie!
I have seen countless examples of making changes to the
datagrid -- this I am able to that without a problem.
However, I have seen few (less than none ) examples that
show a "working" (actually works when you use the code)
example of a System.Windows.Forms.DataGrid where you
update the grid and the supporting dataset.
Here is what I have:

private void loadgrid()
{
myconn3.ConnectionString = connstr3;
mysqlcom3.Connection = myconn3;
mysqlcom3.Connection.Open();
SqlDataAdapter myad3 = new SqlDataAdapter
("SHOWPrinterList",myconn3);
myad3.Fill(mydataset3,"PRINTERLIST");
dataGrid1.DataSource = mydataset3;
dataGrid1.DataMember = "PRINTERLIST";
mycmdbuilder = new SqlCommandBuilder(myad3); ---Here is
where I am getting green lined
(incorrect syntax near "ShowPrinterList")
dataGrid1.ColumnHeadersVisible = true;
//dataGrid1.SetDataBinding(mydataset3,"PRINTERLIST");
}

I understand that you should be updating the underlying
dataset, however , I have not been able to see an example
that actually works! Nor have I been successful trying to
do the update myself. I have both 1.0 and 1.1 frameworks
to work with. Can anybody send me a simple example in C#
of a Datagrid populated by one table using a stored
procedure and that can allow an update of both the
datagrid and the dataset. I would really appreciated your
help on this

Thank You in Advance for Your Help,
Ken

 

[Scot Rose [MSFT]]

What happens if you open the connection and pass it to the command rather than doing the Command.connection.open?

Here are some steps I have laying around the desktop that might be of use...

1. Open Visual Studio 7 and create a new Visual C# Windows Application.

2. Use the Toolbox to add two Buttons and a DataGrid to the default form.

3. Use the Properties window to change the Text property of Button1 to "Load" and Button2 to "Save".

4. Double-click Button1 to add an event handler.

5. Add the following code at the very top of the code window, following the other "using" statements:

using System.Data.OleDb;

6. Add the following member variable declarations to the Form1 class definition, after the button and grid declarations:

private OleDbConnection con = new OleDbConnection("Provider=sqloledb;Data Source=YourServer;User ID=sa;Password=YourPassword;Initial Catalog=Northwind");
private OleDbDataAdapter da;
private DataSet ds = new DataSet();

7. Add the following code to the Button1_Click event handler:

da = new OleDbDataAdapter("Select * From Customers", con);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, "Cust");
dataGrid1.DataSource = ds.Tables[0];

8. Double-click Button2 to add an event handler:

OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.Update(ds, "Cust");

8. Press F5 to compile and run the application.

9. The grid is initially empty. Click the "Load" button to populate the grid.

10. Make a change to several records and click the Load button again. Note what happens to the changes. Do not change the CustomerID field as you will get an exception for
violating referential Integrity on the back-end.

11. Make a change to several records and click the Save button. Close and re-run the application. Re-populate the grid. Are the changes kept?




Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------

 

[Ken]

Thank You Scot,

That works! Now I know that OleDbDataAdapter is better and
more flexable than SqlDataAdapter. They are not
interchangeable!!

Thanks again for Your Help,
Ken

-----Original Message-----
What happens if you open the connection and pass it to

the command rather than doing the Command.connection.open?

Here are some steps I have laying around the desktop that might be of use...

1. Open Visual Studio 7 and create a new Visual C# Windows Application.

2. Use the Toolbox to add two Buttons and a DataGrid to the default form.

3. Use the Properties window to change the Text property

of Button1 to "Load" and Button2 to "Save".

4. Double-click Button1 to add an event handler.

5. Add the following code at the very top of the code

window, following the other "using" statements:

using System.Data.OleDb;

6. Add the following member variable declarations to the

Form1 class definition, after the button and grid
declarations:

private OleDbConnection con = new OleDbConnection

("Provider=sqloledb;Data Source=YourServer;User
ID=sa;Password=YourPassword;Initial Catalog=Northwind");

private OleDbDataAdapter da;
private DataSet ds = new DataSet();

7. Add the following code to the Button1_Click event handler:

da = new OleDbDataAdapter("Select * From Customers", con);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, "Cust");
dataGrid1.DataSource = ds.Tables[0];

8. Double-click Button2 to add an event handler:

OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.Update(ds, "Cust");

8. Press F5 to compile and run the application.

9. The grid is initially empty. Click the "Load" button to populate the grid.

10. Make a change to several records and click the Load

button again. Note what happens to the changes. Do not
change the CustomerID field as you will get an exception
for

violating referential Integrity on the back-end.

11. Make a change to several records and click the Save

button. Close and re-run the application. Re-populate the
grid. Are the changes kept?

Want to know more? Check out the MSDN Library at

http://msdn.microsoft.com or the Microsoft Knowledge Base
at http://support.microsoft.com