Updating a access database from a dataset
This application is a windows form
I have two datasets. One from a web service that I call and one from a local access database. My problem is that I am not able to update the local database with the information from the web service dataset. And help would be great.
here is my code:
Dim d As New DataSet() ' dataset from web services
'For local access database
Dim oConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\memberarea.mdb"
Dim oConn As New OleDb.OleDbConnection(oConnString)
Dim oDS As New DataSet()
Dim oAdapt As New OleDb.OleDbDataAdapter("Select firstname,lastname from staff", oConn)
Sub get_ds_web()
Dim o As New djwebminweb.sync()
d.Merge(o.getstaff("Select firstname,LastName from staff where acctnum='10001'"))
o = Nothing
End Sub
Sub get_ds_local()
oDS.Clear()
oAdapt.Fill(oDS, d.Tables(0).TableName)
oConn.Close()
End Sub
Private Sub updatelocalbd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updatelocalbd.Click
Cursor.Current = Cursors.WaitCursor
updatelocalbd.Enabled = False ' disable button once clicked
Call update_ds_local() ' update local database
Call get_ds_local() ' get new local dataset from db
' Fill New local db combobox
ComboBox3.DataSource = oDS.Tables("staff")
ComboBox3.DisplayMember = "firstname"
' Fill Merged dataset combobox
ComboBox4.DataSource = d.Tables("staff")
ComboBox4.DisplayMember = "firstname"
Cursor.Current = Cursors.Default
End Sub
Sub update_ds_local()
' set the olecommand select command
oAdapt.SelectCommand = New OleDb.OleDbCommand("Select firstname,LastName from staff", oConn)
' declare a ole command builder
Dim custCB As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(oAdapt)
'Open database
oConn.Open()
'merge the two record sets together
d.Merge(oDS)
'fill the ole dataadapter
oAdapt.Fill(d)
'update the dataset
oAdapt.Update(d, "staff")
'Close the db connection
oConn.Close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Cursor.Current = Cursors.WaitCursor
Call get_ds_web() ' get remote dataset
Call get_ds_local() ' get local dataset
'Fill local db combbox
ComboBox1.DataSource = oDS.Tables("staff")
ComboBox1.DisplayMember = "firstname"
'Fill website db combbox
ComboBox2.DataSource = d.Tables("staff")
ComboBox2.DisplayMember = "firstname"
Cursor.Current = Cursors.Default
End Sub
Thank
Mike