Member Avatar for Oblivi8or

Hi.

I'm fairly new to VB.NET programming, and i'm currently developing an application for the sake of experience.

What i know about Visual Basic programming might be considered dangerous, but none the less, i'm trying.

I'm having difficulty getting past the idea of how to update a record in a Microsoft Access 2000 database file. I have no problems inserting, deleting and creating new records in the database, but somehow it seems that there's more to updating a record than what i had expected.

The following code is what i use to collect the data of a specific row in my mdb file, and then displaying the data in the corresponding textbox which i have on my windows form.

Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String


        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\bin\MY_DATABASE.mdb"
        con.Open()

        sql = "SELECT * FROM client WHERE ID = 1"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "ClientList")

        con.Close()
'filling the corresponding textbox named txtName which I have on my windows form
        txtName.Text = ds.Tables("ClientList").Rows(0).Item("Name")

I managed to get past that and work myself around in order to fill the respective text boxes on my windows form.

Now the problem i'm facing, is that i have 3 buttons on this form, which should enable me to ADD, DELETE, or UPDATE the record by filling in or editing the text boxes on the form.

So many times have I tried, and so many times have i searched accross the Internet, and I cant seem to resolve this problem.

I want the update method to work like that of the code snippet I provided above, or atleast something similar to that.

Could anyone please help me with this?

Thank you in advance. Really appreciate it.

  • 4 Contributors
  • 8 Replies
  • 2K Views
  • 5 Years Discussion Span
  • Latest Post Latest Post by Novagrail
arjunsasidharan commented: Welcome to daniweb :) +3

Dani AI

Generated

If you want to stick with DataSets like suggested, the key is that your OleDbDataAdapter must know how to generate UPDATE statements. That only happens when your SELECT includes the table’s primary key, and you keep the adapter around long enough to call Update. If the key is missing or you call AcceptChanges too early, Update will throw an exception (often surfacing as the generic vshost crash you saw, ).

Here is a small, resilient pattern that updates a single row by ID. Note the brackets around [Name] (it is a reserved word), the primary key in the SELECT, and MissingSchemaAction.AddWithKey so the key comes back into the DataTable.

Imports System.Data
Imports System.Data.OleDb

Dim connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\bin\MY_DATABASE.mdb"

Using con As New OleDbConnection(connStr)
    Using da As New OleDbDataAdapter("SELECT ID, [Name] FROM Client WHERE ID = ?", con)
        da.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da.SelectCommand.Parameters.Add("?", OleDbType.Integer).Value = CInt(txtId.Text)

        Dim dt As New DataTable("Client")
        da.Fill(dt)
        If dt.Rows.Count = 0 Then Throw New ApplicationException("Record not found.")

        dt.Rows(0)("Name") = txtName.Text   'apply edits from your form

        Using cb As New OleDbCommandBuilder(da)
            da.Update(dt)                  'persists INSERT/UPDATE/DELETE as needed
        End Using
    End Using
End Using

Troubleshooting tips:

  • Ensure the table has a primary key and it is included in your SELECT; otherwise CommandBuilder cannot create UPDATE/DELETE.
  • Do not call AcceptChanges before da.Update(...); it resets row state and nothing gets saved.
  • Quote any Access keywords/odd column names with [Brackets] (e.g., [Name], [Date]).
  • Target x86 when using Jet 4.0 on 64-bit Windows, or move to the ACE provider for newer Office installs.
  • For multiple fields or rows, bind your controls to a BindingSource over the DataTable; call BindingSource.EndEdit() then da.Update(dt).

: for MySQL, the flow is the same, but use the MySql.Data provider and parameterized SQL with @param names.

Recommended Answers

All 8 Replies

Member Avatar for vamas

to update Database (vb.net Code)

Dim cb as new oledb.oledbcommandbuilder(da)
ds.tables("yourtablename").rows(rowno).item(columnno)="Value"
ds.tables("yourtablename").rows(rowno).item(columnno)="value"
da.update(ds, "Yourtablename")

'da = dataadapter object
'ds = dataset object

Member Avatar for Oblivi8or

to update Database (vb.net Code)

Dim cb as new oledb.oledbcommandbuilder(da)
ds.tables("yourtablename").rows(rowno).item(columnno)="Value"
ds.tables("yourtablename").rows(rowno).item(columnno)="value"
da.update(ds, "Yourtablename")

'da = dataadapter object
'ds = dataset object

Thank you Vamas.

I tried your code, but i keep getting a Microsoft Error report whenever I click the Update button on my windows form.

The error report which I get, is as follows:

Project1.vshost.exe has encountered a problem and needs to close. We are sorry for the inconvenience.

Do you perphaps know what might cause this error report whenever I click the update button?

Member Avatar for Oblivi8or

Could someone please help me? :icon_eek:

Member Avatar for arjunsasidharan

Could someone please help me? :icon_eek:

Try

Member Avatar for Oblivi8or

Thanx buddy, you're a life saver.

Edited by happygeek because: fixed formatting
Member Avatar for arjunsasidharan

Your most welcome Oblivi.. if you got it working then you can mark the thread as SOLVED

Member Avatar for Novagrail

i need a code using VB.NET and MysQL server Database... Non-modular... code for add, edit/update, delete in a single form....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.