Member Avatar for digitalsindhx

I have created simple database project, using vb.net, ado.net, oledb, datatable, datagridview.

My program is working nice, I just need Help with how to add, delete, update and edit records. Can any one please and please help me in this case, or can provide any good tutorial or source code for it please.

I used following code

‘Namespaces
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Common


 ‘other declearations in Public class form

Public Class Form1


    Inherits System.Windows.Forms.Form


    Dim dtBook As DataTable
    Dim daBook As New OleDb.OleDbDataAdapter


    Dim Srch As String
    Dim MaxRowsBook As Integer

    Private DataTable As DataTable
    Private CurrRec As Integer = 0



‘ a function declaration for Connecting textboxes and datagridview to datatable

Public Function ClearBookTable()

        txtBookID.DataBindings.Clear()
        txtNameOfBook.DataBindings.Clear()
        txtCompanyID.DataBindings.Clear()
        txtAuthorName.DataBindings.Clear()

        txtBookID.DataBindings.Add("text", dtBook, "BookID")
        txtNameOfBook.DataBindings.Add("text", dtBook, "NameOfBook")
        txtCompanyID.DataBindings.Add("text", dtBook, "CompanyID")
        txtAuthorName.DataBindings.Add("text", dtBook, "AuthorName")
End Function



‘code for form load event

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim daBook As OleDb.OleDbDataAdapter



        dtBook = New DataTable(1)
        daBook = New OleDbDataAdapter("Select * from Books", "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Display Center Software\DisplayCenterData.mdb")
        daBook.Fill(dtBook)

        DataGridBook.DataSource = dtBook

        txtBookID.DataBindings.Add("text", dtBook, "BookID")
        txtNameOfBook.DataBindings.Add("text", dtBook, "NameOfBook")
        txtCompanyID.DataBindings.Add("text", dtBook, "CompanyID")
        txtAuthorName.DataBindings.Add("text", dtBook, "AuthorName")
End Sub


‘code for previous Button

  Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        Me.BindingContext(dtBook).Position -= 1
        CurrRec -= 1
    End Sub

‘code for next button
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Me.BindingContext(dtBook).Position += 1
        CurrRec += 1

    End Sub

‘code for first Button
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        MaxRowsBook = dtBook.Rows.Count
        Me.BindingContext(dtBook).Position = 0

    End Sub

‘code for last button
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        MaxRowsBook = dtBook.Rows.Count
        Me.BindingContext(dtBook).Position = MaxRowsBook

    End Sub

‘code for search button
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Srch = txtSearch.Text
        daBook = New OleDbDataAdapter("Select * From Books where NameOfBook like'" & Srch & "%'", "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Display Center Software\DisplayCenterData.mdb")

        dtBook = New DataTable(2)

        daBook.Fill(dtBook)
        DataGridBook.DataSource = dtBook

        ClearBookTable()

    End Sub
Edited by Nick Evan because: Fixed formatting
  • 2 Contributors
  • 2 Replies
  • 538 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by digitalsindhx

Dani AI

Generated

Quick diagnosis and the simplest fix: your insert code is calling the DataAdapter.Update immediately, so the new row is written to the MDB as soon as you add it. To get the behavior you want — click Add, enter values, then click Save/Update to persist — keep the new row in memory and call Update only from the Save handler. your posted Add routine performs the commit right away; move that commit into a separate Save button. 's CodeProject link is C#, but the ADO.NET pattern is the same in VB.NET.

Example pattern (VB.NET) — keep objects at form scope, bind via a BindingSource, fill once on load:

' class-level
Private connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourDatabasePath.mdb"
Private da As OleDb.OleDbDataAdapter
Private dt As DataTable
Private bs As BindingSource

' Form_Load (fill and bind)
da = New OleDb.OleDbDataAdapter("SELECT BookID, NameOfBook, CompanyID, AuthorName FROM Books", connString)
Dim cb As New OleDb.OleDbCommandBuilder(da)
dt = New DataTable()
da.Fill(dt)
If dt.PrimaryKey.Length = 0 AndAlso dt.Columns.Contains("BookID") Then dt.PrimaryKey = New DataColumn() {dt.Columns("BookID")}
bs = New BindingSource(dt, Nothing)
DataGridBook.DataSource = bs
txtNameOfBook.DataBindings.Add("Text", bs, "NameOfBook")
txtCompanyID.DataBindings.Add("Text", bs, "CompanyID")
txtAuthorName.DataBindings.Add("Text", bs, "AuthorName")

Button workflow (keep add/save/delete separate):

' Add button
bs.AddNew()    ' starts a new in-memory record; user edits the bound textboxes

' Save/Update button
bs.EndEdit()
Try
  da.Update(dt)   ' persist all pending changes (inserts/updates/deletes)
Catch ex As Exception
  MessageBox.Show(ex.Message)
End Try

' Delete button
If MessageBox.Show("Delete current record?", "Confirm", MessageBoxButtons.YesNo) = DialogResult.Yes Then
  bs.RemoveCurrent()
  da.Update(dt)
End If

Key troubleshooting notes: do not call AcceptChanges before Update (that will prevent DB write). CommandBuilder needs a proper primary key column in the DataTable (or supply Insert/Update/Delete commands manually with parameters). Always call EndEdit on the BindingSource or BindingContext before Update. Use parameterized commands if you build SQL by hand to avoid injection and type problems. If problems persist, post the exact Add/Save handlers and any exceptions; that will make it easy to pinpoint the issue.

Recommended Answers

All 2 Replies

Member Avatar for digitalsindhx

Check this,

http://www.codeproject.com/KB/database/simpledbreadwrite.aspx

But Dear Renukavani, it seems it is C# code, as it Starts with these words .... "Simple ADO.NET Database Read, Insert, Update and Delete using C#.."

Please Help with Vb.net ado.net Oledb Coding Concepts.. i have founded following Code but facing problem that it Adds and update database at once, when i want that first i should Click <add> button, Put Values in text Boxes, then when i Click <update> the database should be updated permenantly

my code is as follows

Dim conn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Display Center Software\DisplayCenterData.mdb")



        conn.Open()



        Dim da As New OleDb.OleDbDataAdapter("Select * FROM Books", conn)


        Dim ds As New DataSet

        Dim dt As DataTable, row As DataRow

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        da.Fill(ds)

        dt = ds.Tables(0)

        row = dt.NewRow



        row("BookID") = txtBookID.Text
        row("NameOfBook") = txtNameOfBook.Text
        row("CompanyID") = txtCompanyID.Text
        row("AuthorName") = txtAuthorName.Text



        dt.Rows.Add(row)

        da.Update(ds)



        MsgBox("1 Record Inserted")

        DataGridBook.DataSource = Nothing

        'dView()



        conn.Close()

please help also with Delete record

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.