Autogenerate ID

[dineshkumaar]

I am developing an application for Co-operative Society Receipt printing Software. I have Members Table with a primary Key MemberID. It is an access database. MemberID as a Long Integer Column. If the user opens Members Entry Form, a new Record is already added with the new autogenerated MemberID. This MemberID is OK. After Filing in the Details when I click save, it Updates the Table and immediately starts new record again with the new MemberID code. This time the new MemberID code is the same as the previous one, it is not incremented. I checked if the record has been updated in the table manually and found it is there.

Most surprisingly.. when I try to debug the code, It is working properly giving proper MemberID. But in run mode, it doesnot increment.


The code for autogeneration is as under:

    Dim ConStr As String = My.Settings.SocieRecConnectionString
    Dim ActiveCon As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection(ConStr)

   Public Function GetNewMemberID() As Integer
        Dim qryStr As String = String.Format("Select Max(MemberID) from Members")
        Dim obj = ExScalar(qryStr)
        If Not obj Is DBNull.Value Then
            GetNewMemberID = DirectCast(obj, Integer)
            GetNewMemberID = GetNewMemberID + 1
        Else
            GetNewMemberID = 1
        End If
    End Function

    'Function to run Execute query and return value
    Private Function ExScalar(ByVal cmdText As String) As Object
        If ActiveCon.State = ConnectionState.Closed Then
            ActiveCon.Open() 
        End If
        cmd = New OleDbCommand(cmdText, ActiveCon)
        ExScalar = cmd.ExecuteScalar()

    End Function

    Private Sub MembersBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles MembersBindingSource.AddingNew

        NewMemberID = GetNewMemberID()
        Dim chk = CheckMemberID(NewMemberID)

        Console.WriteLine("New Member ID : " & CStr(NewMemberID))
        Console.WriteLine("chk: " & chk.ToString)

        Do Until CheckMemberID(NewMemberID) > 0
            NewMemberID = NewMemberID + 1
        Loop

        Dim bs As BindingSource = CType(sender, BindingSource)
        Dim vw As DataView = CType(bs.List, DataView)
        Dim drv As DataRowView = vw.AddNew

        drv("SocietyID") = OpenSocietyID
        drv("MemberID") = NewMemberID
        bs.Position = bs.Count - 1
        e.NewObject = drv
        TitleComboBox.Text = "Shri"
    End Sub

    Private Sub MembersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Me.Validate()
        Me.MembersBindingSource.EndEdit()
        'Me.TableAdapterManager.UpdateAll(Me.SocieRecDataSet)
        Me.MembersTableAdapter.Update(Me.SocieRecDataSet)

        'OpenSocietyID = SocietiesBindingSource.Current("SocietyID")
        ShowMsg(String.Format("Record Saved Successfully"))
        'wait(600)

        Dim TotRows As Integer = dgvMembers.Rows.Count
        dgvMembers.CurrentCell = dgvMembers.Rows(TotRows - 1).Cells(0)

        MembersBindingSource.AddNew()
        MemberNameTextBox.Focus()

    End Sub

Kindly help

Regards
Thanks