Hi,
Is it possible to perminently update a dataset without an sql command. I am trying to edit a data table programmatically, and write my changes to my dataset. Something like this:
dim dt as new datatable
dt = me.dataset1.table1
dt.rows(1).item(1) = 5
dt.AcceptChanges()
me.dataset1.table1DataAdapter.Update(dt)
But, after i run this, the table in my dataset does not seem to be updated with the new values. What do i need to do differently
Thanks in advance.
Pete M
Perminently update a dataset without SQL command?
mrotoloni
Pete_M,
DataSet table is stored in the memory and is different from the real table in the database. The following two methods shows the difference between update the dataset and the database tables:
Public Sub RefreshData()
Dim connection As New SqlConnection(ConnectionString)
connection.Open()
Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)
Dim dataset As New DataSet
adapter.Fill(dataset)
adapter.Dispose()
connection.Close()
Dim table As DataTable = dataset.Tables(0)
AddHandler table.ColumnChanged, New DataColumnChangeEventHandler(AddressOf ColumnChanged)
datagridAuthors.DataSource = table
End Sub
Public Sub SaveChanges()
Dim table As DataTable = CType(datagridAuthors.DataSource, DataTable)
Dim changedRows As New ArrayList
For Each row As DataRow In table.Rows
If row.RowState <> DataRowState.Unchanged Then
changedRows.Add(row)
End If
Next
If changedRows.Count = 0 Then
Return
End If
Dim connection As New SqlConnection(ConnectionString)
connection.Open()
Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)
Dim builder As New SqlCommandBuilder(adapter)
Dim rows() As DataRow = CType(changedRows.ToArray(GetType(DataRow)), DataRow())
adapter.Update(rows)
adapter.Dispose()
connection.Close()
menuSaveChanges.Enabled = False
End Sub
The SqlCommandBuilder change the real table in database when you modify the data on the form shown in a DataGrid.
PitcherJ
The simple answer to your question is NO....ADO.NET uses sql to communicate data changes to the database.(The wizards will generate simple sql statements for you) Also you would not call accept changes on the datatable prior to calling the update command...I would take a look at the ADO.NET/VB data samples:
http://msdn2.microsoft.com/en-us/vbasic/ms789075.aspx#data
reichard
That's weak, but oh well...i'll figure it out. I have a related question: When i add a datasource, is that data stored within my VB application or does my applicaion only connect to the database that's sored on my computer I'm trying to figure out how my program interacts with MS Access,which is the source of all my datasets.
Pete M
Tryst