Update modified Datagrid to Tables

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

Paul
Wed Feb 25 10:17:12 CST 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:A4D40BE4-D8AC-4C22-B1FA-F4CAEC6B94AC@microsoft.com...
> 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.Click
> Dim cn As New SqlCeConnection
> Try
> cn = New SqlCeConnection("data source=" & "\Program
files\Northwindtest\NorthwindDemo.sdf")
> cn.Open()
> Dim cmd As SqlCeCommand = cn.CreateCommand
> cmd.CommandText = "select * from customers"
> Dim da As New SqlCeDataAdapter(cmd)
> Dim ds As New DataSet
> 'Or below as a data table
> 'Dim ds As DataTable = New DataTable("Customers")
> da.Fill(ds)
> DataGrid1.Enabled = True
> DataGrid1.DataSource = ds.Tables(0)
>
> 'Catch database errors
> Catch sqlex As SqlCeException
> Dim sqlerror As SqlCeError
> For Each sqlerror In sqlex.Errors
> MessageBox.Show(sqlerror.Message)
> Next
> Catch ex As Exception
> MessageBox.Show(ex.Message)
> Finally
> DataGrid1.Enabled = True
> 'Dim dt As New DataSet("NorthwindDemo")
> 'Dim db As New SqlCeDataAdapter
> 'db.Update(dt)
> If cn.State <> ConnectionState.Closed Then
> cn.Close()
> End If
> End Try
> End Sub
>
> Private Sub dataGrid1_CurrentCellChanged(ByVal o As Object, ByVal e As
EventArgs) _
> Handles DataGrid1.CurrentCellChanged
>
> Dim currentCell As DataGridCell
> Dim currentCellData As String
>
> ' Get the current cell.
> currentCell = DataGrid1.CurrentCell
> If currentCell.ColumnNumber = 0 Then
> TextBoxCurrentCell.Text = ""
> Exit Sub
> Else
> ' 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 Then
> TextBoxCurrentCell.Text = currentCellData
> End If
> End If
> End Sub
>
> Private Sub buttonSetCurrentCell_Click(ByVal o As Object, ByVal e As
EventArgs) _
> Handles buttonSetCurrentCell.Click
>
> Dim currentCell As DataGridCell
> Dim currentCellData As String
>
> ' Get the text to put into the current cell.
> currentCellData = TextBoxCurrentCell.Text
>
> ' Get the current cell.
> currentCell = DataGrid1.CurrentCell
>
> ' Set the current cell's data.
> DataGrid1(currentCell.RowNumber, currentCell.ColumnNumber) =
currentCellData
> End Sub

anonymous
Wed Feb 25 10:51:08 CST 2004

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.

Paul
Wed Feb 25 12:06:03 CST 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:C1E6F67F-0D53-4CD7-B563-EB36BD3B69A0@microsoft.com...
> 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.

anonymous
Wed Feb 25 17:41:06 CST 2004

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

Paul
Thu Feb 26 09:22:30 CST 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:3389EBB6-DC2A-4716-9E70-2CE233B45947@microsoft.com...
> 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
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.
>
>

anonymous
Thu Feb 26 15:21:05 CST 2004

Paul, I guess what I am trying to say is that I cannot get it working. It is not updating the database.

Paul
Thu Feb 26 15:58:02 CST 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:15A00D13-736F-4A43-91C0-28284205861E@microsoft.com...
> Paul, I guess what I am trying to say is that I cannot get it working. It
is not updating the database.

anonymous
Thu Feb 26 20:01:06 CST 2004

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.

Paul
Fri Feb 27 09:59:43 CST 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:3085888A-6A6B-4C35-A342-A3320F65D2C1@microsoft.com...
> 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.

Paul
Mon Mar 01 10:18:41 CST 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:627E0D30-9FA2-4445-A917-92637EC025EF@microsoft.com...
> Paul, as you can see from the code, I am using a SQLCE database on the
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
framework? On the desktop, no problems with the full framework.

anonymous
Mon Mar 01 18:51:06 CST 2004

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?

Paul
Tue Mar 02 12:30:12 CST 2004

I'll try it, if you want to post it.

Paul T.

"Chewban" <anonymous@discussions.microsoft.com> wrote in message
news:08359AF7-8E23-4DC3-8358-07F99021F59F@microsoft.com...
> 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?