vb.net | Update DB with OleDB

Question

i wrote a module of a connection to DB with OleDB and the 'sub UpdateClients' doesn't work, the DB don't update.

what's missing or wrong?

this line -> "daClient.Update(dsClient, "CLUB_CLIENT")" -> dosen't work (sorry about my english, i'm not so good)

the database doesn't update after this line (like i expected)

what's missing in my code? i want that my DB will Update. "txtid" will be in "ClntId" from my tables.

Module mdlDB
    Const CONNECTION_STRING As String = _
          "provider= Microsoft.Jet.OleDB.4.0;Data Source=DbHalf.mdb;mode= Share Deny None"
    Dim daClient As New OleDb.OleDbDataAdapter
    Dim dsClient As New DataSet
    Dim cmClient As CurrencyManager

    Public Sub OpenClients(ByVal txtId, ByVal txtName, ByVal BindingContext)

        Dim Con As New OleDb.OleDbConnection(CONNECTION_STRING)
        Dim sqlClient As New OleDb.OleDbCommand

        Con.Open()
        sqlClient.CommandText = "SELECT*"
        sqlClient.CommandText += "FROM tblClubClient"
        sqlClient.Connection = Con
        daClient.SelectCommand = sqlClient

        dsClient.Clear()
        daClient.Fill(dsClient, "CLUB_CLIENT")

        cmClient = BindingContext(dsClient, "CLUB_CLIENT")
        cmClient.Position = 0

        txtId.DataBindings.Add("text", dsClient, "CLUB_CLIENT.ClntId")
        txtName.DataBindings.Add("text", dsClient, "CLUB_CLIENT.ClntName")

        Con.Close()

    End Sub

    Public Sub UpdateClients(ByVal txtId, ByVal txtName, ByVal BindingContext)

        Dim cb As New OleDb.OleDbCommandBuilder(daClient)

        cmClient = BindingContext(dsClient, "CLUB_CLIENT")

        dsClient.Tables("CLUB_CLIENT").Rows(cmClient.Position).Item("ClntId") = txtId.Text
        dsClient.Tables("CLUB_CLIENT").Rows(cmClient.Position).Item("ClntName") = txtName.Text

        daClient.Update(dsClient, "CLUB_CLIENT")

    End Sub

End Module

Answer 1

Hi,

Sometimes the people in these forums get hung up on "forum protocol" or "forum etiquette" (If you want to see this in spades go take a peek at the apple developers forums.)

The answer was buried up there is the comments--- specifically:

Since you close the connection in the OpenClients() ---> Con.Close(), it is not viable in the UpdateClients() routine.

I will also emphasize the commented idea about using TRY/CATCH and also add that since database connections can mysteriously "go away" you can also add a check for Con.State (i.e. Open or Closed database connection) in any subroutines that expect a database connection to be open. if the check fails, you can re-call the OpenClients() subroutine before continuing on.