Home All Groups Group Topic Archive Search About

DataGrid / Dataset / DataAdaptor / DataBase updat problem - please

Author
15 May 2006 12:21 PM
marcmc
My dataset is not updating my database after the user modifies the datagrid.
I populate my data with the load sub below.
In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
they all seem to be connected correctly. But my data does not update.
the dataAdaptor is configured for Insert/Update/delete and the datagrid
datasource is DataSet11.TableName. Any ideas?

Private Sub Thresholds_Load(ByVal sender As.....
Try
  cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
& Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
  cn.Open()
  cmdSelect.Connection = cn

  Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
*  from MISRE_Threshold", cn)
  Dim dsThreshold As DataSet = New DataSet

            ' fill dataset
            da.Fill(dsThreshold, "MISRE_Threshold")

            'Attach DataSet to DataGrid
            dgThreshold.DataSource = dsThreshold.DefaultViewManager

        Catch ex As Exception
            MessageBox.Show("Error: Could not establish database connection")
        End Try


Private Sub btnSave_Click(ByVal sender.....

  SqlDataAdapter1.Update(DataSet11)

EndSub

Author
15 May 2006 12:34 PM
spamsickle
If you're using the VB IDE to create the code that connects to your
database, you may be running into the "local copy" issue.  The "Create
New Connection" wizard pops up a messagebox that says something like
"There is no local copy of the database you're connecting to, do you
want to create one and copy the data to it?"  The default is "Yes".

Taking the default will mean that the updates your program makes to the
database only update the local copy.  While you're executing the
program, you'll see the changes, but if you do a "Save", those changes
are only saved to the local copy.  In addition, the next time your
program executes, a fresh copy of the database overlays your local
copy, wiping out any changes you may have made previously.

If this is what's causing your problem, you could either specify "no
local copy" when the wizard creates the connection code, or copy your
local copy of the database (which will be in your Projects folder) over
the original database data after your program executes.