Good VB.net Database Software, Plz Help, New Added Records are unable to Edit and Del

[digitalsindhx]

Dear Friends I have Developed a Successful Database Software, it include Datagrid, First, Last, Next, Previous, Add, Delete, Edit, Update, Search just Every Thing is Working Nice, But I need help from one of expert from you.
my problem is that every time when i Start program its all functions are workin including edit and delete. (mean data (rows) Added at previous run is able to be Delted at current time) but when i add new record at current run time it adds succesfully, but while at same run time session (current) i tries to Delete or edit new added roecord it is not working, in simple words new added records are not able to delete and edit at same run session.
i m including project's zip file and plz try to run it and add new record, after adding record at same time try to edit or Delete new added record.

Plz Help why it is becouse, can you help me in this record
Please Reffer to Attachment and place AddressBook.mdb at C:\ Root Drive

[kvprajapati]

>i tries to Delete or edit new added roecord it is not working

You need to configure InsertCommand, UpdateCommand, and DeleteCommand properties of dataAdapter.

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Dim Conn As New OleDb.OleDbConnection

    Dim Ds As New DataSet
    Dim Da As OleDb.OleDbDataAdapter
    Dim Cmb As New OleDbCommandBuilder
    Dim Dt As New DataTable
    Dim Sql As String
    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; data source=c:\addressbook.mdb"

        Sql = "Select * from tblContacts"

        Da = New OleDb.OleDbDataAdapter(Sql, Conn)
        Cmb = New OleDbCommandBuilder(Da)
        Da.Fill(Ds, "AddressBook")
        Dt = Ds.Tables("AddressBook")
        DataGridView1.DataSource = Dt

        txtFirstName.DataBindings.Add("Text", Dt, "FirstName")
        txtSurName.DataBindings.Add("Text", Dt, "Surname")
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Me.BindingContext(Dt).Position += 1
    End Sub

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

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        Me.BindingContext(Dt).Position = Me.BindingContext(Dt).Count
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        Me.BindingContext(Dt).Position = 0
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Da.Update(Ds, "AddressBook")
        MsgBox("Data Add/edupdated")
        DataGridView1.Refresh()
    End Sub

    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        Me.BindingContext(Dt).AddNew()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If MessageBox.Show("Do you really want to Delete this Record?", _
"Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = DialogResult.No Then
            MsgBox("Operation Cancelled")
            Exit Sub
        Else
            Me.BindingContext(Dt).RemoveAt(Me.BindingContext(Dt).Position)

            Da.Update(Ds, "AddressBook")
        End If
    End Sub
    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dt.DefaultView.RowFilter = "FirstName like '" & txtSearch.Text & "%'"
    End Sub

    Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
        Dt.DefaultView.RowFilter = "FirstName like '" & txtSearch.Text & "%'"
    End Sub
End Class

[Oxiegen]

On lines 224 and 361:
Add DataGridView1.Update() below them.

------------------------------

Please disregard my post. Totally wrong. :)

[lanitooot]

Imports System.Data.OleDb

Public Class Main
    'Public connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=db.accdb"
    Dim conn As New OleDbConnection
    Dim myqry As String = Nothing
    Dim mycmd As New OleDbCommand
    Dim mydr As OleDbDataReader

    Dim str As String = Nothing

    Private Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call LVsettings()
        Call ConnToDB()
        Call FillListview()
        Call Set1()
    End Sub

#Region "FillListView"

    Sub FillListview()
        LV.Items.Clear()
        myqry = "SELECT * from tblInformation ORDER BY id ASC"
        mycmd = New OleDbCommand(myqry, conn)
        mydr = mycmd.ExecuteReader

        While mydr.Read
            With LV
                .Items.Add(mydr("id"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(mydr("Firstname"))
                    .Add(mydr("Lastname"))
                    .Add(mydr("Address"))
                End With
            End With
        End While
    End Sub

#End Region

#Region "Connection"
    Sub ConnToDB()
        Try
            With conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=db.accdb"
                .Open()
            End With
        Catch ex As Exception
            MessageBox.Show("Unable to connect", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Application.Exit()
        End Try
    End Sub
#End Region

#Region "LVSettings"
    Sub LVsettings()
        With LV.Columns
            .Add("ID", 50, HorizontalAlignment.Left)
            .Add("First Name", 100, HorizontalAlignment.Left)
            .Add("Last Name", 100, HorizontalAlignment.Left)
            .Add("Address", 200, HorizontalAlignment.Left)
        End With
    End Sub
#End Region

#Region "object Settings "

    Sub Set1()
        btnAdd.Enabled = True
        btnEdit.Enabled = True
        btnDelete.Enabled = True
        btnExit.Enabled = True

        btnSave.Enabled = False
        btnCancel.Enabled = False
        Dim txt As Control
        For Each txt In Me.Controls
            If TypeOf txt Is TextBox Then
                txt.Enabled = False
            End If
        Next

        LV.Enabled = True
    End Sub

    Sub Set2()
        btnAdd.Enabled = False
        btnEdit.Enabled = False
        btnDelete.Enabled = False
        btnExit.Enabled = False

        btnSave.Enabled = True
        btnCancel.Enabled = True

        Dim txt As Control
        For Each txt In Me.Controls
            If TypeOf txt Is TextBox Then
                txt.Enabled = True
            End If
        Next

        LV.Enabled = False
    End Sub
#End Region

#Region "CLEAR TEXTBOX"
    Sub ClearAlltextBox()
        Dim a As Control
        For Each a In Me.Controls
            If TypeOf a Is TextBox Then
                a.Text = Nothing
            End If
        Next
    End Sub
#End Region


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Application.Exit()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        str = "add"
        Call Set2()
        Call ClearAlltextBox()
        txtID.Text = "Computer Generated"
        txtID.Enabled = False

    End Sub

    Private Sub btnEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If txtID.Text = "" Then
            MessageBox.Show("Please Select Record to Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            str = "edit"
            Call Set2()
            txtID.Enabled = False
        End If

    End Sub

    Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Call Set1()
        Call ClearAlltextBox()
    End Sub

    Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If str = "add" Then
            ''''''ADD NEW RECORD'''''''
            If txtFirst.Text = "" Or txtLast.Text = "" Or txtAddress.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                myqry = "INSERT INTO tblInformation(Firstname,LastName,Address) "
                myqry = myqry + "VALUES('" & txtFirst.Text & "','" & txtLast.Text & "','" & txtAddress.Text & "')"
                mycmd = New OleDbCommand
                With mycmd
                    .CommandText = myqry
                    .Connection = conn
                    .ExecuteNonQuery()
                End With
                Call Set1()
            End If

        Else
            ''''''''''UPDATE RECORD'''''''
            If txtFirst.Text = "" Or txtLast.Text = "" Or txtAddress.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                myqry = "UPDATE tblInformation SET "
                myqry = myqry + " Firstname = '" & txtFirst.Text & "',"
                myqry = myqry + " LastName = '" & txtLast.Text & "',"
                myqry = myqry + " Address = '" & txtAddress.Text & "'"
                myqry = myqry + " WHERE "
                myqry = myqry + " ID = " & txtID.Text

                'myqry = "UPDATE tblInformation "
                'myqry = myqry + "SET Firstname = '" & txtFirst.Text & "',"
                'myqry = myqry + "LastName = '" & txtLast.Text & "',"
                'myqry = myqry + "Address = '" & txtAddress.Text & "'"
                'myqry = myqry + "WHERE id = '" & txtID.Text & "'"

                mycmd = New OleDbCommand(myqry, conn)
                mycmd.ExecuteNonQuery()
                Call Set1()
            End If
        End If

        Call FillListview()
        Call ClearAlltextBox()

    End Sub

    Private Sub LV_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LV.SelectedIndexChanged
        If LV.SelectedItems.Count > 0 Then
            With LV.SelectedItems(0)
                txtID.Text = .Text
                txtFirst.Text = .SubItems(1).Text
                txtLast.Text = .SubItems(2).Text
                txtAddress.Text = .SubItems(3).Text
            End With
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            myqry = "DELETE FROM tblInformation WHERE ID = " & txtID.Text
            mycmd = New OleDbCommand(myqry, conn)
            mycmd.ExecuteNonQuery()
        End If
        Call ClearAlltextBox()
        Call FillListview()
    End Sub
End Class