Update modified Datagrid to Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I update my datagrid to save the modified values to the database table
I opened up NorthwindDemo.sdf in a datagrid, edit a cell using a textbox (using buttonSetCurrentCell_Click). I just lack the knowledge to update any changes I made to the datagrid back to the database table customers. Please help

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Clic
Dim cn As New SqlCeConnectio
Tr
cn = New SqlCeConnection("data source=" & "\Program files\Northwindtest\NorthwindDemo.sdf"
cn.Open(
Dim cmd As SqlCeCommand = cn.CreateComman
cmd.CommandText = "select * from customers
Dim da As New SqlCeDataAdapter(cmd
Dim ds As New DataSe
'Or below as a data tabl
'Dim ds As DataTable = New DataTable("Customers"
da.Fill(ds
DataGrid1.Enabled = Tru
DataGrid1.DataSource = ds.Tables(0

'Catch database error
Catch sqlex As SqlCeExceptio
Dim sqlerror As SqlCeErro
For Each sqlerror In sqlex.Error
MessageBox.Show(sqlerror.Message
Nex
Catch ex As Exceptio
MessageBox.Show(ex.Message
Finall
DataGrid1.Enabled = Tru
'Dim dt As New DataSet("NorthwindDemo"
'Dim db As New SqlCeDataAdapte
'db.Update(dt
If cn.State <> ConnectionState.Closed The
cn.Close(
End I
End Tr
End Su

Private Sub dataGrid1_CurrentCellChanged(ByVal o As Object, ByVal e As EventArgs)
Handles DataGrid1.CurrentCellChange

Dim currentCell As DataGridCel
Dim currentCellData As Strin

' Get the current cell
currentCell = DataGrid1.CurrentCel
If currentCell.ColumnNumber = 0 The
TextBoxCurrentCell.Text = "
Exit Su
Els
' Get the current cell's data
currentCellData = CStr(DataGrid1(currentCell.RowNumber, currentCell.ColumnNumber)
' Set the TextBox's text to that of the current cell
TextBoxCurrentCell.Focus(

' Set the TextBox's text to that of the current cell
If Not currentCellData.ToString = Nothing The
TextBoxCurrentCell.Text = currentCellDat
End I
End I
End Su

Private Sub buttonSetCurrentCell_Click(ByVal o As Object, ByVal e As EventArgs)
Handles buttonSetCurrentCell.Clic

Dim currentCell As DataGridCel
Dim currentCellData As Strin

' Get the text to put into the current cell
currentCellData = TextBoxCurrentCell.Tex

' Get the current cell
currentCell = DataGrid1.CurrentCel

' Set the current cell's data
DataGrid1(currentCell.RowNumber, currentCell.ColumnNumber) = currentCellDat
End Sub
 
You can do something like this:

-----

SqlCommandBuilder sqlcmdbld = new SqlCommandBuilder( dataadapter );
dataadapter.UpdateCommand = sqlcmdbld.GetUpdateCommand();
dataadapter.DeleteCommand = sqlcmdbld.GetDeleteCommand();
dataadapter.InsertCommand = sqlcmdbld.GetInsertCommand();
dataadapter.Update( dataset ); // Run Update, Delete, and Insert.

dataset.AcceptChanges();

-----

Obviously, if you aren't allowing deletes or adds to the list of rows, you
don't need to do the InsertCommand or DeleteCommand items.

Paul T.

chewban said:
How do I update my datagrid to save the modified values to the database table?
I opened up NorthwindDemo.sdf in a datagrid, edit a cell using a textbox
Click to expand...
(using buttonSetCurrentCell_Click). I just lack the knowledge to update any
changes I made to the datagrid back to the database table customers. Please
help.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
Click to expand...
System.EventArgs) Handles Button1.Click
 
Thanks paul for your prompt reply
I put in a button to run the update code but have problems in articulating the correct syntax to pick up the dataadapter and dataset in a subroutine. Can you please show me how I can use your code in a sub in context of the code I have posted. Thanks in advance.
 
You'll either want to store the dataadapter and dataset as instance
variables of the form or whatever is trying to save the data or you'll have
to extract them from the data table itself. I used the dataadapter to get
the data from the source table (or other query).

dataset = new DataSet();
dataadapter = new SqlDataAdapter();
dataadapter.SelectCommand = cmd; // Which might be 'SELECT * FROM
mytable WHERE whatever;' or something
dataadapter.Fill(dataset);

ResultGrid.DataSource = dataset.Tables[ 0 ]; // Setting the data
grid's data source.

Paul T.

Chewban said:
Thanks paul for your prompt reply.
I put in a button to run the update code but have problems in articulating
Click to expand...
the correct syntax to pick up the dataadapter and dataset in a subroutine.
Can you please show me how I can use your code in a sub in context of the
code I have posted. Thanks in advance.
 
Paul

Thanks for your reply again
The code you gave me is what I used to populate the dataset
dataset = new DataSet()
dataadapter = new SqlDataAdapter()
dataadapter.SelectCommand = cmd
dataadapter.Fill(dataset)
Datagrid1.DataSource = dataset.Tables[ 0 ];

I then modify a cell (city) in the datagri
datagrid1(0,7)="Melbourne

and the earlier code is to update the dataset
SqlCommandBuilder sqlcmdbld = new SqlCommandBuilder( dataadapter )
dataadapter.UpdateCommand = sqlcmdbld.GetUpdateCommand()
dataadapter.DeleteCommand = sqlcmdbld.GetDeleteCommand()
dataadapter.InsertCommand = sqlcmdbld.GetInsertCommand()
dataadapter.Update( dataset ); // Run Update, Delete, and Insert
dataset.AcceptChanges()

However, is there a step missing where I need to update the dataset with my modified datagrid
I intend to use a button to update the record set to the database but still do not have a clue how to do it. Is there an example out there? Alex Feinman has a datagridediting example at OpenCFForum but the data is hard coded and does not show the full scope of building recordset from database, bind to a grid, edit the grid and then save back to database
 
If you already attached the datagrid to the dataset to get the grid
populated, no, all you have to do is run the dataadapter's Update(dataset)
operation. I use the prevously-posted update code when the OK button in my
'dialog' is clicked. This seems very simple, though, so I suspect that I'm
not understanding what exactly it is that you're asking...

Paul T.

Chewban said:
Paul,

Thanks for your reply again.
The code you gave me is what I used to populate the dataset.
dataset = new DataSet();
dataadapter = new SqlDataAdapter();
dataadapter.SelectCommand = cmd;
dataadapter.Fill(dataset);
Datagrid1.DataSource = dataset.Tables[ 0 ];

I then modify a cell (city) in the datagrid
datagrid1(0,7)="Melbourne"

and the earlier code is to update the dataset.
SqlCommandBuilder sqlcmdbld = new SqlCommandBuilder( dataadapter );
dataadapter.UpdateCommand = sqlcmdbld.GetUpdateCommand();
dataadapter.DeleteCommand = sqlcmdbld.GetDeleteCommand();
dataadapter.InsertCommand = sqlcmdbld.GetInsertCommand();
dataadapter.Update( dataset ); // Run Update, Delete, and Insert.
dataset.AcceptChanges();

However, is there a step missing where I need to update the dataset with my modified datagrid?
I intend to use a button to update the record set to the database but
Click to expand...
still do not have a clue how to do it. Is there an example out there? Alex
Feinman has a datagridediting example at OpenCFForum but the data is hard
coded and does not show the full scope of building recordset from database,
bind to a grid, edit the grid and then save back to database.
 
Paul, I guess what I am trying to say is that I cannot get it working. It is not updating the database.
 
Is the user that you logged in as allowed to update the database? Are you
updating it in a way that is reasonable (you can read the SQL that's
generated by the SqlCommandBuilder?

Paul T.

Chewban said:
Paul, I guess what I am trying to say is that I cannot get it working. It
Click to expand...
is not updating the database.
 
Paul, Thanks for your help to date. It is just frustrating trying and not getting anywhere. I just think that if there is a working example it will make life easier. An example using a SQLCE database like NorthwindDemo.sdf.
 
That is *precisely* where I've been sending the pieces that I have. I can't
post the whole program, but it works perfectly with Northwind in SQL Server
7 and SQL Server 2000. That's why I'm suggesting possible causes, at this
point...

How do you know it's not updating?

Paul T.

Chewban said:
Paul, Thanks for your help to date. It is just frustrating trying and
Click to expand...
not getting anywhere. I just think that if there is a working example it
will make life easier. An example using a SQLCE database like
NorthwindDemo.sdf.
 
You should probably not open the database a second time to try to verify the
results without shutting down the first connection. I don't know when the
transaction that updates the data will be completed and you may be getting
the data from the second connection before the first has completed the
update. How about this sequence:

1. Connect to the database and populate the grid.
2. Modify the cell contents.
3. Update the database.
4. Disconnect from the database and exit your program.

Then,

1. Connect to the database and populate the grid. At *this* point, is the
change present?

I'm not doing anything with the full framework, so I was assuming that all
of your stuff was .NET CF, yes.

Paul T.

Chewban said:
Paul, as you can see from the code, I am using a SQLCE database on the
Click to expand...
device. I have one routine that loads the data into a datagrid, modify one
of the cells, use your code to update the dataset and "accept changes", and
then one of two things to check. 1. Open up the database on a second
routine to siply bind the database table to the grid, 2. Synchronise the
data back to the server where I use sql server 2000 to view the results.
You are aware that I am trying to get the code to work on the compact
Click to expand...
framework? On the desktop, no problems with the full framework.
 
Paul, Thanks for your kind preseverence. I have been doing it in the sequence you stated. I will normally exit the application to check if the database has been updated and it is not. Would you like me to post you the code? I will rig up the code based on the NorthwindDemo.sdf. I assume that posting the source files to you is out of the question?
 
is there a way to extract data from the database, select a specific
entry and load those results into a table that can be maintained outside
the original database?
 
I'll try it, if you want to post it.

Paul T.

Chewban said:
Paul, Thanks for your kind preseverence. I have been doing it in the
Click to expand...
sequence you stated. I will normally exit the application to check if the
database has been updated and it is not. Would you like me to post you the
code? I will rig up the code based on the NorthwindDemo.sdf. I assume that
posting the source files to you is out of the question?
 
You can get the results of a query and just iterate through the data set,
yes. What you do with the data is up to you. It might look something like
this example which puts the list of tables found in an SQL Server database
into a .NET CF combobox:

-----

String query = String.Format( "SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';" );
SqlCommand cmd = new SqlCommand( query,
sqlconnect );
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);

// Populate the combo box with the names of
// the available tables.
DataTable dt = ds.Tables[ 0 ];

// Get list of column names. I hope that
// at least one is something like "TableName".
// Well, close, "TABLE_NAME".
int table_name_index = -1;
int i = 0;
foreach ( DataColumn dc in dt.Columns )
{
if ( dc.ColumnName == "TABLE_NAME" )
{
table_name_index = i;
break;
}
i++;
}

if ( table_name_index != -1 )
{
foreach ( DataRow dr in dt.Rows )
{
// Get the table's name.
Object o = dr.ItemArray[ table_name_index ];
TablesCombo.Items.Add( o.ToString() );
}
}

// Clean up the dataset, since we're done with
// it.
ds.Clear();

// Shut it down.
cmd = null;
 
Back
Top