oledb database update problem

[Raged]

Hey all

I'm having loads of problems trying to update my database with a dataset,
I have been looking around forums and posts and trying all sorts of work around's,

as far as I can assume, I will need to manually create (insert, update) function querys (even though as far as I read the commandbuilder is ment to do that for me)

this is the code from the class (- loads of commented out code that don't work)
the variables being passed in are:

table name selected (from a list)

updateset - dataset with new data to replace the original database data (not sure if the dataset holds multiple tables or not)

global var's

Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Hospital.accdb"
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Hospital.accdb"
Dim connection As OleDbConnection = New OleDbConnection
Dim cmd As OleDbCommand
Dim read As OleDbDataReader
Dim sql As String
Dim DAHospital As New OleDbDataAdapter
Dim ds As DataSet = New DataSet
Dim cmdBuild As New OleDbCommandBuilder(DAHospital)

Creating dataadapter and filling dataset

Public Function createDA(ByVal table As String) As DataSet
        connection.ConnectionString = connString
        'Dim tbl As DataTable = cn.GetSchema("Tables")
        sql = "select * from " + table        
        DAHospital = New OleDbDataAdapter(sql, connection.ConnectionString)

        ''''select/inser cmd needed here?
        DAHospital.Fill(ds, table)
        Return ds
End Function

the update function - which returns errors

The DataAdapter.SelectCommand.Connection property needs to be initialized

Public Sub updateDA(ByVal updateset As DataSet, ByVal table As String)

        cn.Open()
        cmdBuild.RefreshSchema()
        cmdBuild.GetUpdateCommand()
        cmdBuild.GetInsertCommand()

        Dim DeletedChildRecords As DataSet = _
        updateset.GetChanges(DataRowState.Deleted)

        Dim NewChildRecords As DataSet = _
            updateset.GetChanges(DataRowState.Added)

        Dim ModifiedChildRecords As DataSet = _
            updateset.GetChanges(DataRowState.Modified)

        Try
            If Not DeletedChildRecords Is Nothing Then
                DAHospital.Update(DeletedChildRecords)
            End If

            DAHospital.Update(updateset, table)

            If Not NewChildRecords Is Nothing Then
                DAHospital.Update(NewChildRecords)
            End If

            If Not ModifiedChildRecords Is Nothing Then
                DAHospital.Update(ModifiedChildRecords)
            End If

            updateset.AcceptChanges()

        Catch ex As Exception
            ' Update error, resolve and try again

        Finally
            If Not DeletedChildRecords Is Nothing Then
                DeletedChildRecords.Dispose()
            End If

            If Not NewChildRecords Is Nothing Then
                NewChildRecords.Dispose()
            End If

            If Not ModifiedChildRecords Is Nothing Then
                ModifiedChildRecords.Dispose()
            End If
        End Try

        cn.Close()
    End Sub

[kvprajapati]

Add following lines into UpdateDa method.

connection.ConnectionString = connString
sql = "select * from " + table        
DAHospital = New OleDbDataAdapter(sql, connection.ConnectionString)

[akamini]

Have a read of this
http://www.homeandlearn.co.uk/NET/nets12p9.html

[Raged]

I have added those 3 lines at the top of my updateDA function
as well as adding this line

Dim cb As New OleDb.OleDbCommandBuilder(DAHospital)

however I am still getting the error

The DataAdapter.SelectCommand property needs to be initialized

I really understand I should not be getting this, as the builder should be creating the select,insert,delete, update commands.

any ideas on what I could do?

[Raged]

Hey all I fixed it,
I just scapped out all my code and started again with a tutorial on doing the cmdBuilder

so my code is now

sql = "select * from " + table
        
        Dim cm = New OleDbCommand(sql, cn)
        DAHospital.SelectCommand = cm
        cmdBuild = New OleDbCommandBuilder(DAHospital)

        cn.Open()

        cmdBuild.RefreshSchema()
        cmdBuild.GetUpdateCommand()
        cmdBuild.GetInsertCommand()

        DAHospital.Update(updateset, table)