none
A good way to get auto number from database in VB.net RRS feed

  • Question

  • Hi everybody,

    I have this code to get auto number from access database 

        Function GetTable(SelectCommand As String) As DataTable
            Trace.WriteLine("GetTable STARTED @ " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
    
            Dim cmd As New OleDbCommand("", conn)
            Try
                Dim Data_table1 As New DataTable
                If conn.State = ConnectionState.Closed Then conn.Open()
                cmd.CommandText = SelectCommand
                Data_table1.Load(cmd.ExecuteReader())
                Return Data_table1
            Catch ex As Exception
                MsgBox(ex.Message)
                Return New DataTable
            Finally
                If conn.State = ConnectionState.Open Then conn.Close()
            End Try
            Trace.WriteLine("GetTable FINISHED @ " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
    
        End Function
    
        Function GetAutonumber(TableName As String, ColumnName As String) As String
            Trace.WriteLine("GetAutonumber STARTED @ " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
    
            Dim Str As String
            Str = "select max ( " & ColumnName & " ) + 1 from " & TableName
            Dim Data_table2 As New DataTable
            Data_table2 = GetTable(Str)
            Dim AutoNum As String
            If Data_table2.Rows(0)(0) Is DBNull.Value Then
                AutoNum = "1"
            Else
                AutoNum = CType(Data_table2.Rows(0)(0), String)
            End If
            Return AutoNum
            Trace.WriteLine("GetAutonumber FINISHED @ " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
    
        End Function

    Is there a simple way to get an auto number? if this snippet code is good ,No problem i'll use it .

    But i have a sense that there is a simple way to get an auto number

     Thanks in advance...........

    Regards From Amr_Aly

    Friday, May 4, 2018 7:58 PM

Answers

  • Yeah, if you're using OLEDB (ADO/ADO.NET) then another query is always required, such as the SELECT @@Identity statement suggested by Karen. It's also relatively easy with DAO.

            Dim Recordset As dao.Recordset
            Dim Database As dao.Database
            Dim DAODBEngine As New dao.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("C:\Users\...\Documents\My Database\Access\Northwind.accdb")
            Recordset = Database.OpenRecordset("AuditTrail")
    
            Recordset.AddNew()
            Recordset("Col1").Value = "SomeVal"
            Recordset("Col2").Value = Now()
            Recordset("Col3").Value = "SomeVal2"
            Recordset("Col4").Value = "SomeVal3"
            Recordset.Update()
    
            Recordset.Bookmark = Recordset.LastModified
            Dim autoNumberValue As Integer = Recordset("AutoNumberColumn").Value
    
            Recordset.Close()
            Database.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Amr_Aly Thursday, May 10, 2018 4:25 PM
    Friday, May 4, 2018 9:59 PM

All replies

  • As shown below, two executions are needed on the command.

    Public Function AddNewRow(
    ByVal pName As String,
    ByVal pContact As String,
    ByVal pContactTitle As String,
    ByVal pJoinDate As Date,
    ByVal pData1 As DateTime,
    ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
        Try
            Using cn As New OleDbConnection("TODO")
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                    <SQL>
                        INSERT INTO Customer 
                            (
                                CompanyName,
                                ContactName,
                                ContactTitle,
                                JoinDate,
                                Data1
                            ) 
                        Values
                            (
                                @CompanyName,
                                @ContactName,
                                @ContactTitle,
                                @JoinDate,
                                @Data1
                            )
                    </SQL>.Value
    
                    cmd.Parameters.AddWithValue("@CompanyName", pName)
                    cmd.Parameters.AddWithValue("@ContactName", pContact)
                    cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                    cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
                    cmd.Parameters.AddWithValue("@Data1", pData1)
                    cn.Open()
    
                    cmd.ExecuteNonQuery()
    
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
    
                End Using
            End Using
    
        Catch ex As Exception
            Success = False
        End Try
    
        Return Success
    
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Paul P Clement IV Friday, May 4, 2018 9:54 PM
    • Unproposed as answer by Amr_Aly Wednesday, May 9, 2018 7:40 AM
    • Marked as answer by Amr_Aly Thursday, May 10, 2018 4:26 PM
    • Unmarked as answer by Amr_Aly Thursday, May 10, 2018 4:26 PM
    Friday, May 4, 2018 8:48 PM
    Moderator
  • Yeah, if you're using OLEDB (ADO/ADO.NET) then another query is always required, such as the SELECT @@Identity statement suggested by Karen. It's also relatively easy with DAO.

            Dim Recordset As dao.Recordset
            Dim Database As dao.Database
            Dim DAODBEngine As New dao.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("C:\Users\...\Documents\My Database\Access\Northwind.accdb")
            Recordset = Database.OpenRecordset("AuditTrail")
    
            Recordset.AddNew()
            Recordset("Col1").Value = "SomeVal"
            Recordset("Col2").Value = Now()
            Recordset("Col3").Value = "SomeVal2"
            Recordset("Col4").Value = "SomeVal3"
            Recordset.Update()
    
            Recordset.Bookmark = Recordset.LastModified
            Dim autoNumberValue As Integer = Recordset("AutoNumberColumn").Value
    
            Recordset.Close()
            Database.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Amr_Aly Thursday, May 10, 2018 4:25 PM
    Friday, May 4, 2018 9:59 PM
  • Sorry i didn't understand Karen, I'm using this method to save and determine if the "ID" is new or already exist in the database in order to make save or update

     Sub SavePatient()
            Try
                If txtNo.Text = GetAutonumber("Pat", "Patient_no") Then
    
                    cmd = New OleDbCommand("INSERT INTO Pat(Patient_no, Name, Address, Birthdate, Age, Phone, Mob," &
                                           "HusName, Job, Male, Female, Married, Divorced, Single_, Widowed)" &
                                           "VALUES(@Patient_no, @Name, @Address, @Birthdate, @Age, @Phone, @Mob," &
                                           "@HusName, @Job, @Male, @Female, @Married, @Divorced, @Single_, @Widowed)", conn)
    
                    With cmd.Parameters
                        .Add("@Patient_no", OleDbType.Integer).Value = CInt(Val(txtNo.Text))
                        .Add("@Name", OleDbType.VarChar).Value = cbxPatName.Text
                        .Add("@Address", OleDbType.VarChar).Value = cbxAddress.Text
                        .Add("@Birthdate", OleDbType.DBDate).Value = DTPicker.Value
                        .Add("@Age", OleDbType.VarChar).Value = txtAge.Text
                        .Add("@Phone", OleDbType.VarChar).Value = txtPhone.Text
                        .Add("@Mob", OleDbType.VarChar).Value = txtMob.Text
                        .Add("@HusName", OleDbType.VarChar).Value = cbxHusband.Text
                        .Add("@Job", OleDbType.VarChar).Value = cbxJob.Text
                        .Add("@Male", OleDbType.Boolean).Value = chbxMale.Checked
                        .Add("@Female", OleDbType.Boolean).Value = chbxFem.Checked
                        .Add("@Married", OleDbType.Boolean).Value = rdoMarr.Checked
                        .Add("@Divorced", OleDbType.Boolean).Value = rdoDiv.Checked
                        .Add("@Single_", OleDbType.Boolean).Value = rdoSin.Checked
                        .Add("@Widowed", OleDbType.Boolean).Value = rdoWid.Checked
                    End With
    
                    If conn.State = ConnectionState.Open Then
                        conn.Close()
                    End If
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                End If
    
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub

    and this one to update

    Sub UpdatePatient()
            Try
                If cbxPatName.Text <> "" And txtNo.Text <> GetAutonumber("Pat", "Patient_no") Then
                    
                    cmd = New OleDbCommand("UPDATE Pat SET Name=@Name, Address=@Address, Birthdate=@Birthdate, 
                                          Age=@Age, Phone=@Phone, Mob=@Mob, HusName=@HusName, Job=@Job, Male=@Male, 
                                          Female=@Female, Married=@Married, Divorced=@Divorced, Single_=@Single_,
                                          Widowed=@Widowed WHERE Patient_no=@Patient_no", conn)
    
                    With cmd.Parameters
                        .Add("@Name", OleDbType.VarChar).Value = cbxPatName.Text
                        .Add("@Address", OleDbType.VarChar).Value = cbxAddress.Text
                        .Add("@Birthdate", OleDbType.DBDate).Value = CDate(DTPicker.Value)
                        .Add("@Age", OleDbType.VarChar).Value = txtAge.Text
                        .Add("@Phone", OleDbType.VarChar).Value = txtPhone.Text
                        .Add("@Mob", OleDbType.VarChar).Value = txtMob.Text
                        .Add("@HusName", OleDbType.VarChar).Value = cbxHusband.Text
                        .Add("@Job", OleDbType.VarChar).Value = cbxJob.Text
                        .Add("@Male", OleDbType.Boolean).Value = chbxMale.Checked
                        .Add("@Female", OleDbType.Boolean).Value = chbxFem.Checked
                        .Add("@Married", OleDbType.Boolean).Value = rdoMarr.Checked
                        .Add("@Divorced", OleDbType.Boolean).Value = rdoDiv.Checked
                        .Add("@Single_", OleDbType.Boolean).Value = rdoSin.Checked
                        .Add("@Widowed", OleDbType.Boolean).Value = rdoWid.Checked
                        .Add("@Patient_no", OleDbType.Integer).Value = CInt(Val(txtNo.Text))
                    End With
    
                    If conn.State = ConnectionState.Open Then
                        conn.Close()
                    End If
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                End If
    
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub

    I'm so sorry that i couldn't absorb your code.....forgive me


    Regards From Amr_Aly


    • Edited by Amr_Aly Friday, May 4, 2018 11:57 PM
    Friday, May 4, 2018 11:55 PM
  • Yeah, if you're using OLEDB (ADO/ADO.NET) then another query is always required, such as the SELECT @@Identity statement suggested by Karen. It's also relatively easy with DAO.

            Dim Recordset As dao.Recordset
            Dim Database As dao.Database
            Dim DAODBEngine As New dao.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("C:\Users\...\Documents\My Database\Access\Northwind.accdb")
            Recordset = Database.OpenRecordset("AuditTrail")
    
            Recordset.AddNew()
            Recordset("Col1").Value = "SomeVal"
            Recordset("Col2").Value = Now()
            Recordset("Col3").Value = "SomeVal2"
            Recordset("Col4").Value = "SomeVal3"
            Recordset.Update()
    
            Recordset.Bookmark = Recordset.LastModified
            Dim autoNumberValue As Integer = Recordset("AutoNumberColumn").Value
    
            Recordset.Close()
            Database.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Hi Paul, Did you mean something like that

     Public Sub AutoNum()
            Dim Recordset As DAO.Recordset
            Dim Database As DAO.Database
            Dim DAODBEngine As New DAO.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("TestDB.accdb")
            Recordset = Database.OpenRecordset("Pat")
    
            Recordset.AddNew()
            Recordset("Patient_no").Value = txtNo.Text
            Recordset("Name").Value = cbxPatName.Text
            Recordset("Address").Value = cbxAddress.Text
            Recordset("Birthdate").Value = DTPicker.Value
            Recordset("Age").Value = txtAge.Text
            Recordset("Phone").Value = txtPhone.Text
            Recordset("Mob").Value = txtMob.Text
            Recordset("HusName").Value = cbxHusband.Text
            Recordset("Job").Value = cbxJob.Text
            Recordset("Male").Value = chbxMale.Checked
            Recordset("Female").Value = chbxFem.Checked
            Recordset("Married").Value = rdoMarr.Checked
            Recordset("Divorced").Value = rdoDiv.Checked
            Recordset("Single_").Value = rdoSin.Checked
            Recordset("Widowed").Value = rdoWid.Checked
            Recordset.Update()
    
            Recordset.Bookmark = Recordset.LastModified
            Dim autoNumberValue As Integer = CInt(Recordset("Patient_no").Value)
    
            Recordset.Close()
            Database.Close()
        End Sub

    I'm sorry ,i'm so confused my code above in the question works well but i want to find more fast and good codde to get auto number from the database....just develop myself.....

    thanks


    Regards From Amr_Aly

    Saturday, May 5, 2018 12:21 AM
  • I will warn you up front that the following information is easy once you understand it but may take some time to thus I've included code samples for you to try out.

    Let's see if this makes sense to you. When loading a DataTable such as in your GetTable method you could check the state of each row which in this case they would be "unchanged", if you add a row the state is "added", if updated the state is "Modified". Who do we know this? From DataRowState

    I have a MSDN code sample that shows how to get all states or one state. For "know" of a change immediately there are DataTable events, see my other MSDN code sample to learn about this.

    To put things into perspective, you want to insert or update a DataRow once the user is done editing, you would use DataTable events as per my second example. Once the backend data operation has competed mark that DataRow's AcceptChanges property to True, not the DataTable but the DataRow.

    If you want to handle changes all at once you my first code sample. Once done handling all changes set AcceptChanges to True for the DataTable as the last step.

    Is there an easier concrete way to do what I provided above, no, otherwise I would have provided an easier method.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, May 5, 2018 12:52 AM
    Moderator
  • It looks like you have the basic idea using DAO. I can't test your code since I don't have your database but that looks to be correct. Keep in mind that DAO will be faster than ADO or ADO.NET, since it's designed specifically for Access, but there will be less integration with ADO.NET components (such as a DataGridView).

    Just one thing I noticed, if Patient_no is the Autonumber column then it would not be included in the AddNew...Update block, since it would be auto generated.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Saturday, May 5, 2018 3:49 AM
  • The best way is to use the autonumber as a technical ID and a real ID to get the data. 


    Success
    Cor

    Saturday, May 5, 2018 7:41 AM
  • It looks like you have the basic idea using DAO. I can't test your code since I don't have your database but that looks to be correct. Keep in mind that DAO will be faster than ADO or ADO.NET, since it's designed specifically for Access, but there will be less integration with ADO.NET components (such as a DataGridView).

    Just one thing I noticed, if Patient_no is the Autonumber column then it would not be included in the AddNew...Update block, since it would be auto generated.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    OK Paul, It makes error ( Unrecognized database format 'E:\KMAClinicpro\New_\Pediatric12-04-2018Modified\test\bin\Release\TestDB.accdb'.)

    Regards From Amr_Aly

    Saturday, May 5, 2018 10:09 AM
  • Sounds to me like you have the old version of the DAO reference in your project. Should be one of the following for .accdb format:


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, May 5, 2018 11:37 AM
  • Sounds to me like you have the old version of the DAO reference in your project. Should be one of the following for .accdb format:


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul i'm searching to have a new version of Microsoft.office.interop.access.dao but i failed beacause i've already have it in my project

    take a look plaese 


    Regards From Amr_Aly

    Saturday, May 5, 2018 4:11 PM
  • You have multiple references to DAO. Remove the old Jet version above.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, May 5, 2018 5:41 PM
  • You have multiple references to DAO. Remove the old Jet version

    A new error message (Not a valid password)

    Recordset = Database.OpenRecordset("AuditTrail") instead of AuditTrail("My_Table") and ("database Password") and ("connection string")

    And I've another inquiry how to this snippet know the text box of the "Patient_no"

    Dim autoNumberValue As Integer = CInt(Recordset("Patient_no").Value)


    Or i do something like this

    autoNumberValue = CInt(Val(txtNo.Text))


    Regards From Amr_Aly

    Saturday, May 5, 2018 7:46 PM
  • Putting the database in a project or app folder is a bad idea. Plus you need to make sure you are not overwriting the copy you are using each time you run project in VS by setting the "Copy to Output Directory" property of the database (in Solution Explorer) to "Do not copy". I suspect the database file the app is trying to open either does not have a password or has a different password. That would be only explanation for the error.

    The snippet you refer to is setting the current record to the last row that was updated so you can then retrieve the autonumber value for that record. I assumed from your code that the autonumber column was Patient_no. If that is not the name of your autonumber column then you need to replace it with the correct name. Like I said, the autonumber column should not be in the AddNew...Update block since the value of that column will be automatically generated when the row is inserted. You only reference it after the Update by setting the bookmark and retrieving the value. Hope that makes sense. 


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Saturday, May 5, 2018 9:10 PM
  • Putting the database in a project or app folder is a bad idea. Plus you need to make sure you are not overwriting the copy you are using each time you run project in VS by setting the "Copy to Output Directory" property of the database (in Solution Explorer) to "Do not copy". I suspect the database file the app is trying to open either does not have a password or has a different password. That would be only explanation for the error.

    The snippet you refer to is setting the current record to the last row that was updated so you can then retrieve the autonumber value for that record. I assumed from your code that the autonumber column was Patient_no. If that is not the name of your autonumber column then you need to replace it with the correct name. Like I said, the autonumber column should not be in the AddNew...Update block since the value of that column will be automatically generated when the row is inserted. You only reference it after the Update by setting the bookmark and retrieving the value. Hope that makes sense. 


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Hi Paul,

    I spend a long time testing this problem the error is (Not A Valid Password) ,I changed the directory of my database and changed the setting of "Copy to Output Directory" property of my database to "Do not copy".It was really "Copy Always" and my database password is fixed in all the code .My code works well without your code and the (txtNo.text) that have to have an auto Number generated  when my App. open is empty has no any number . I think that your code for saving items to database but i want a simple and fast way to generate auto number in my App. 

    So i think that you want to tell me that when i use Dao method to save in the database i will can get an auto number faster than  my two functions snippets  above , I tried your code like this in the validating event of the textbox that i use to make saving process     

    Private Sub cbxPatName_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles cbxPatName.Validating
            Dim ds As DataSet = New DataSet
            Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT Name FROM Pat WHERE Name='" & cbxPatName.Text & "'", conn)
            da.Fill(ds, "Pat")
            Dim dv As DataView = New DataView(ds.Tables("Pat"))
            Dim cur As CurrencyManager
            cur = CType(Me.BindingContext(dv), CurrencyManager)

            If cur.Count <> 0 And txtNo.Text = GetAutonumber("Pat", "Patient_no") Then
                MsgBox("The is already exists", MsgBoxStyle.OkOnly, "Change Name")
                cbxPatName.ResetText()
                Exit Sub
            End If
            If cbxPatName.Text <> "" Then
                AutoNum()
                'SavePatient()
                'UpdatePatient()
            End If
        End Sub

    Public Sub AutoNum() Dim Recordset As Microsoft.Office.Interop.Access.Dao.Recordset Dim Database As Microsoft.Office.Interop.Access.Dao.Database Dim DAODBEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine() Database = DAODBEngine.OpenDatabase("E:\TestDB.accdb") Recordset = Database.OpenRecordset("Pat", Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset) 'Recordset = Database.OpenRecordset("provider=microsoft.ace.oledb.12.0; data source=TestDB.accdb;jet oledb:database password=mypassword") Recordset.AddNew() 'Recordset("Patient_no").Value = txtNo.Text 'Recordset.FindFirst("Patient_no = 1") Recordset("Name").Value = cbxPatName.Text Recordset("Address").Value = cbxAddress.Text Recordset("Birthdate").Value = DTPicker.Value Recordset("Age").Value = txtAge.Text Recordset("Phone").Value = txtPhone.Text Recordset("Mob").Value = txtMob.Text Recordset("HusName").Value = cbxHusband.Text Recordset("Job").Value = cbxJob.Text Recordset("Male").Value = chbxMale.Checked Recordset("Female").Value = chbxFem.Checked Recordset("Married").Value = rdoMarr.Checked Recordset("Divorced").Value = rdoDiv.Checked Recordset("Single_").Value = rdoSin.Checked Recordset("Widowed").Value = rdoWid.Checked Recordset("Patient_no").Value = txtNo.Text Recordset.Update() Recordset.Bookmark = Recordset.LastModified Dim autoNumberValue As Integer = CInt(Recordset("Patient_no").Value) 'autoNumberValue = CInt(Val(txtNo.Text)) Recordset.Close() Database.Close()

    But the same message appears (Not A Valid Password)
     

    Regards From Amr_Aly



    • Edited by Amr_Aly Sunday, May 6, 2018 9:28 AM
    Sunday, May 6, 2018 8:44 AM
  • If your database has a password the OpenDatabase statement should look like the following:

           Database = DAODBEngine.OpenDatabase("E:\TestDB.accdb", False, False, ";PWD=password")


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 11:31 AM
  • If your database has a password the OpenDatabase statement should look like the following:

           Database = DAODBEngine.OpenDatabase("E:\TestDB.accdb", False, False, ";PWD=password")

    Not a valid password - has been solved thanks Paul........But the problem is the (txtNo.text) still empty when i open my app. hence i can't save or update this message appears

    and another problem will be occur how can i distinguish between a new number and old number (i.e the number that has not entered to database in order to make saving process and the number already exists in the database in order to update) i handled this situation by my two functions above as follow 

    if txtNo.text = GetAutoNumber("Pat", "Patient_no") Then
    
    SavePatient()
    
    End if
    
    if txtNo.text <> GetAutoNumber("Pat", "Patient_no") Then
    
    UpdatePatient()
    
    End if
     

    finally Is there a special  method to retrieve data by Dao or i can retrieve by my old method ?

    thanks in advance..........



    Regards From Amr_Aly

    Sunday, May 6, 2018 1:46 PM
  • If your database has a password the OpenDatabase statement should look like the following:

           Database = DAODBEngine.OpenDatabase("E:\TestDB.accdb", False, False, ";PWD=password")

    Yea Paul has something with Dao. He is one of the longest time MVP (23 year), and AFAIK that started with DAO. 

    I think it is old sentiment he did not tell you not to use it anymore. It is the fastest running with a Jet 4.0 database. However, 12.0 access does not run even anymore well in a program when the client has office 64 bit installed. And there is nothing to do to solve that.

    https://support.office.com/en-us/article/choose-between-the-64-bit-or-32-bit-version-of-office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261 


    Success
    Cor


    Sunday, May 6, 2018 4:55 PM
  • Not sure that I understand your question about "new" and "old" autonumber. An autonumber value is created automatically, hence the name autonumber. You should never change the value of a column in a record that is autonumber when updating (it's static) and you always start with a new autonumber when inserting. There is no "new" and "old" autonumber value.

    Yes, you can also use DAO to retrieve data using the OpenRecordset method for either a Database or QueryDef object. You can also use your ADO.NET code if you prefer.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 6:01 PM
  • Not sure what you mean Cor. Microsoft Access (DAO) does support 64-bit.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 6:05 PM
  • Not sure that I understand your question about "new" and "old" autonumber. An autonumber value is created automatically, hence the name autonumber. You should never change the value of a column in a record that is autonumber when updating (it's static) and you always start with a new autonumber when inserting. There is no "new" and "old" autonumber value.

    Yes, you can also use DAO to retrieve data using the OpenRecordset method for either a Database or QueryDef object. You can also use your ADO.NET code if you prefer.


    Paul ~~~~ Microsoft MVP (Visual Basic)

     

    OK Paul ,I understand you that i was wrong about new and old, But I'm still have no auto number created yet when i open my App. in order to save or update .I couldn't Save or Update in database "Patient ID" must be created automatically (as my picture indicates above) where is the auto number generated in order to write a patient name and save into database? ..... My problem was in creating auto number by a fast way than my code (Two functions above in my question - GetTable and GetAutoNumber) Or i have to use it with your code..... If i use it the auto number will be created normally ,And  i'm still couldn't update the database with your code (Saving process done but Update process fail) 


    Regards From Amr_Aly


    • Edited by Amr_Aly Sunday, May 6, 2018 7:49 PM
    Sunday, May 6, 2018 7:31 PM
  • You have to define the Patient ID column in the table as AutoNumber. Did you do that when you created the table in the database?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 8:51 PM
  • You have to define the Patient ID column in the table as AutoNumber. Did you do that when you created the table in the database?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    It's already auto number


    Regards From Amr_Aly

    Sunday, May 6, 2018 9:53 PM
  • It's already auto number

    OK, so when you insert a new row is this column automatically populated?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 10:00 PM

  • OK, so when you insert a new row is this column automatically populated?
    Yes if i do this in my database but in my app. NO

    Regards From Amr_Aly

    Sunday, May 6, 2018 10:14 PM

  • OK, so when you insert a new row is this column automatically populated?

    Yes if i do this in my database but in my app. NO

    Regards From Amr_Aly

    So after you insert a new row in the DAO code and execute the below statement, no value is returned in the autoNumberValue variable?

    Dim autoNumberValue As Integer = Recordset("Patient_no").Value
    
    FYI, CInt would not be necessary.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 10:35 PM
  • Not sure what you mean Cor. Microsoft Access (DAO) does support 64-bit.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    If I read how much you seems to make propaganda for the DAO Recordset in this thread, people can get idea's like it is something new and better. 

    I don't think that it is not what you want to say. 

    :-)


    Success
    Cor

    Sunday, May 6, 2018 10:37 PM

  • So after you insert a new row in the DAO code and execute the below statement, no value is returned in the autoNumberValue variable?

    Dim autoNumberValue As Integer = Recordset("Patient_no").Value
    FYI, CInt would not be necessary.
    Option strict on disallow implicit conversion ,So i need it

    Regards From Amr_Aly

    Sunday, May 6, 2018 10:56 PM

  • So after you insert a new row in the DAO code and execute the below statement, no value is returned in the autoNumberValue variable?

    Dim autoNumberValue As Integer = Recordset("Patient_no").Value
    FYI, CInt would not be necessary.

    Option strict on disallow implicit conversion ,So i need it

    Regards From Amr_Aly


    OK, so if you add CInt do you get your autonumber value?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 10:59 PM
  • No I have  no  any auto number appears , My text box in my App. is empty when i open it 

    Regards From Amr_Aly

    Sunday, May 6, 2018 11:07 PM
  • Not sure what you mean Cor. Microsoft Access (DAO) does support 64-bit.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    If I read how much you seems to make propaganda for the DAO Recordset in this thread, people can get idea's like it is something new and better. 

    I don't think that it is not what you want to say. 

    :-)


    Success
    Cor

    Cor, never implied or said that. But apparently you were thinking that. ;-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 11:07 PM
  • No I have  no  any auto number appears , My text box in my App. is empty when i open it 

    Regards From Amr_Aly

    Not what I asked you. I asked you if the value of Patient_no was returned to autoNumberValue, but you have not answered that question yet.

    If you are displaying this number on a Form after the insert then you need to assign it to the TextBox (readonly) or a Label on your Form.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, May 6, 2018 11:14 PM
  • Hi Amr_Aly,

    According to your requirement, you want to get auto number from database to let you insert or update data in database. But the Access database can set auto numbers, so you just check if the ID is exist in Access table, if yes, you just can update this record by this ID, if not, you can insert new record by this ID.

    About checking this ID is exist or not, you can take a look. I think you don't need to get Auto number from Access database.

      Dim isinsert As Boolean
     Private Function fun(txtno As Integer) As Boolean
            isinsert = True
            Dim sql As String = "Select count(*) from test1 where Id=@Id "
            Using conn As New OleDbConnection(str)
                conn.Open()
                Using cmd As New OleDbCommand(sql, conn)
                    cmd.Parameters.AddWithValue("@Id", txtno)
                    If cmd.ExecuteScalar() Then
                        isinsert = False
                        Return isinsert
                    Else
                        isinsert = True
                        Return isinsert
                    End If
                End Using
                conn.Close()
            End Using
            Return isinsert
        End Function

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 7, 2018 3:06 AM
    Moderator
  • Hi Amr_Aly,

    According to your requirement, you want to get auto number from database to let you insert or update data in database. But the Access database can set auto numbers, so you just check if the ID is exist in Access table, if yes, you just can update this record by this ID, if not, you can insert new record by this ID.

    About checking this ID is exist or not, you can take a look. I think you don't need to get Auto number from Access database.

      Dim isinsert As Boolean
     Private Function fun(txtno As Integer) As Boolean
            isinsert = True
            Dim sql As String = "Select count(*) from test1 where Id=@Id "
            Using conn As New OleDbConnection(str)
                conn.Open()
                Using cmd As New OleDbCommand(sql, conn)
                    cmd.Parameters.AddWithValue("@Id", txtno)
                    If cmd.ExecuteScalar() Then
                        isinsert = False
                        Return isinsert
                    Else
                        isinsert = True
                        Return isinsert
                    End If
                End Using
                conn.Close()
            End Using
            Return isinsert
        End Function


    It didn't work Cherry Bu, here is  my modifications

    Dim isinsert As Boolean
        Private Function fun(txtno As Integer) As Boolean
            isinsert = True
            Dim sql As String = "Select count(*) from Pat where Patient_no=@Patient_no"
            Using conn As New OleDbConnection("provider=microsoft.ace.oledb.12.0; data source=TestDB.accdb;jet oledb:database password=pass")
                conn.Open()
                Using cmd As New OleDbCommand(sql, conn)
                    cmd.Parameters.AddWithValue("@Patient_no", txtno)
                    If CBool(cmd.ExecuteScalar()) Then
                        isinsert = False
                        Return isinsert
                    Else
                        isinsert = True
                        Return isinsert
                    End If
                End Using
                conn.Close()
            End Using
            Return isinsert
        End Function

    after that i put this function in public sub new like this

    If cbxPatName.Text = "" Then
                fun(CInt(Val(txtNo.Text)))

    End if

    when i open my App. txtNo.text is empty without any number ...... but by old method it enable me to get auto number by put 

    txtNo.text = GetAutoNumber("Pat", "Patient_no") ..... I'm searching for simple and fast way to get an auto number . just develop my skills

    your code is so nice and simple ,i like it but it didn't work... 


    Regards From Amr_Aly

    Monday, May 7, 2018 7:51 AM
  • Not what I asked you. I asked you if the value of Patient_no was returned to autoNumberValue, but you have not answered that question yet.

    If you are displaying this number on a Form after the insert then you need to assign it to the TextBox (readonly) or a Label on your Form.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Your first line ("Not what I asked you. I asked you if the value of Patient_no was returned to autoNumberValue, but you have not answered that question yet") i can't understand it and i don't know how to do this. how the Value of Patient_no return to autoNumberValue i use your code like this 

     Public Sub AutoNumSAve()
            Dim Recordset As Microsoft.Office.Interop.Access.Dao.Recordset
            Dim Database As Microsoft.Office.Interop.Access.Dao.Database
            Dim DAODBEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("TestDB.accdb", False, False, ";PWD=pass")
            Recordset = Database.OpenRecordset("Pat") ', Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset
            'Recordset = Database.OpenRecordset("provider=microsoft.ace.oledb.12.0; data source=TestDB.accdb;jet oledb:database password=pass")
    
            Recordset.AddNew()
            'Recordset("Patient_no").Value = CInt(Val(txtNo.Text))
            'Recordset.FindFirst("Patient_no = 1")
            Recordset("Name").Value = cbxPatName.Text
            Recordset("Address").Value = cbxAddress.Text
            Recordset("Birthdate").Value = DTPicker.Value
            Recordset("Age").Value = txtAge.Text
            Recordset("Phone").Value = txtPhone.Text
            Recordset("Mob").Value = txtMob.Text
            Recordset("HusName").Value = cbxHusband.Text
            Recordset("Job").Value = cbxJob.Text
            Recordset("Male").Value = chbxMale.Checked
            Recordset("Female").Value = chbxFem.Checked
            Recordset("Married").Value = rdoMarr.Checked
            Recordset("Divorced").Value = rdoDiv.Checked
            Recordset("Single_").Value = rdoSin.Checked
            Recordset("Widowed").Value = rdoWid.Checked
            Recordset("Patient_no").Value = CInt(Val(txtNo.Text))
            Recordset.Update()
    
            Recordset.Bookmark = Recordset.LastModified
    
            Dim autoNumberValue As Integer = CInt(Val(Recordset("Patient_no").Value))
            'autoNumberValue = CInt(Val(txtNo.Text))
            Recordset.Close()
            Database.Close()
        End Sub
    The second line ("If you are displaying this number on a Form after the insert then you need to assign it to the TextBox (readonly) or a Label on your Form.") i want to display auto number when i open my app. in the (text box "txtNo.text" ) before saving or updating in order to know if this is a new patient to make save or already exists in my database to update it. I hope it is obvious now

     

    Regards From Amr_Aly


    • Edited by Amr_Aly Monday, May 7, 2018 8:06 AM
    Monday, May 7, 2018 8:04 AM
  • I'm searching for simple and fast way to get an auto number . just develop my skills


    Amr,

    What skills? Why do you want to get your autonumber while other people don't need that or cannot use it because it gives false information.

    In this thread you started with a datatable (a collection of rows), nowhere you showed how you updated that; was busy with a recordset (a real complete different collection) and now you are busy with datareader rows which seems to be new inserted (not updated).

    You can not develop your skills if you have not a purpose to do something. 

    You do like you have a hammer and knock with that against the wall and tell you're developing your skills. 

    The first thing you have to know is if you need that autonumber and then wherefore. 

    If you use a datatable, you have to use the adapter, that has an update and in that are used temporally autonumbers. 

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.autoincrementseed(v=vs.110).aspx



    Success
    Cor


    Monday, May 7, 2018 8:15 AM
  • The second line ("If you are displaying this number on a Form after the insert then you need to assign it to the TextBox (readonly) or a Label on your Form.") i want to display auto number when i open my app. in the (text box "txtNo.text" ) before saving or updating in order to know if this is a new patient to make save or already exists in my database to update it. I hope it is obvious now

     

    Regards From Amr_Aly


    An AutoNumber value is generated at the time of insert, not before. The reason for this is simple. In a multi-user environment someone else could try to add a record to a table with the same number, which would create a duplicate or generate an error if it's a primary key. Does this make sense?

    So in your case, there is no Patient_id until you have added the patient information to the database table. After the patient has been added you can display it on the Form in a Label so it cannot be changed.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 7, 2018 11:55 AM
  • Could you not just refer to the DA.FillSchema method?

    Paul, are you pulling our leg with using DAO? You're making me feel really old.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, May 7, 2018 5:08 PM

  • An AutoNumber value is generated at the time of insert, not before. The reason for this is simple. In a multi-user environment someone else could try to add a record to a table with the same number, which would create a duplicate or generate an error if it's a primary key. Does this make sense?

    So in your case, there is no Patient_id until you have added the patient information to the database table. After the patient has been added you can display it on the Form in a Label so it cannot be changed.

    Not exactly Paul but it's helpful to know another method of saving in the database (your code and Karen's code), I used my two functions with your code, I'm stucking with update .So Can you tell me how to update by DAO ?.......thanks Paul

    Regards From Amr_Aly




    • Edited by Amr_Aly Wednesday, May 9, 2018 7:39 AM
    Wednesday, May 9, 2018 7:34 AM

  • The second line ("If you are displaying this number on a Form after the insert then you need to assign it to the TextBox (readonly) or a Label on your Form.") i want to display auto number when i open my app. in the (text box "txtNo.text" ) before saving or updating in order to know if this is a new patient to make save or already exists in my database to update it. I hope it is obvious now

     

    Regards From Amr_Aly


    @Amr_Aly

    I know it it senseless to write in this thread, but for those seeing this every time appear. I assume that Karen has given a solutions with System.Data. Paul has given you especial solutions with ADODB.

    There is a very important difference which makes them so different that it is comparing planes with ships (both used for transport)

    1. System.Data is disconnected (which means it needs updates of the data).
    2. OleDB is connected (which means every change in the program result in an automatic update).  

    So as written more times in this thread, you started with a plane for your transport but are now busy with a ship. What is your goal. Trolling or with a problem?


    Success
    Cor



    Wednesday, May 9, 2018 9:22 AM

  • An AutoNumber value is generated at the time of insert, not before. The reason for this is simple. In a multi-user environment someone else could try to add a record to a table with the same number, which would create a duplicate or generate an error if it's a primary key. Does this make sense?

    So in your case, there is no Patient_id until you have added the patient information to the database table. After the patient has been added you can display it on the Form in a Label so it cannot be changed.

    Not exactly Paul but it's helpful to know another method of saving in the database (your code and Karen's code), I used my two functions with your code, I'm stucking with update .So Can you tell me how to update by DAO ?.......thanks Paul

    Regards From Amr_Aly




    Well I'm not going to argue with you about how an AutoNumber works. That is defined by Microsoft Access and other DBMS which use them (in SQL Server it's referred to as an Identity column).

    Are you asking about Update now or are you still stuck on Insert. It sounded to me like the Insert was working. The only difference was that you wanted to see the AutoNumber value *before* the Insert, which unfortunately is not going to work with an AutoNumber column. If you want to see the value of Patient_id before the Insert then you will need to use a different method.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 9, 2018 12:27 PM
  • You could insert a row with a GUID then create a function to get the auto incremented # using the GUID in a where clause.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, May 9, 2018 1:06 PM


  • Well I'm not going to argue with you about how an AutoNumber works. That is defined by Microsoft Access and other DBMS which use them (in SQL Server it's referred to as an Identity column).

    Are you asking about Update now or are you still stuck on Insert. It sounded to me like the Insert was working. The only difference was that you wanted to see the AutoNumber value *before* the Insert, which unfortunately is not going to work with an AutoNumber column. If you want to see the value of Patient_id before the Insert then you will need to use a different method

    OK Paul i knew that i made a disturbance but i want to learn , Insertion works well till now but i don't understand your words ....

     how to update by DAO ? i tried your code(insertion code) with my update method but it failed so i'm searching for a way to update by DAO 

    By the way , I appreciated everyone has participated in this thread , and i'm sorry that i misunderstood or didn't absorbed your codes because i'm still new to VB.net....Really this thread made me confused and felt that the way is so long but i'll be never surrendered ,So please forgave me everybody 

      

    Regards From Amr_Aly

    Wednesday, May 9, 2018 1:24 PM
  • You could insert a row with a GUID then create a function to get the auto incremented # using the GUID in a where clause.


     

    Sorry Gtripodi

    I didn't understand .......... Did you mean that i should use something like this ?

    Private Sub NewID()
            Try
                Dim sqlString As String = "SELECT MAX(Patient_no) +1 FROM Pat"
                Dim dbConnection As New OleDbConnection("provider=microsoft.ace.oledb.12.0; data source=TestDB.accdb;jet oledb:database password=pass")
                Dim command As New OleDb.OleDbCommand(sqlString, dbConnection)
                dbConnection.Open()
                txtNo.Text = CType(command.ExecuteScalar(), String)
                dbConnection.Close()
            Catch sqlex As Exception
                MessageBox.Show(sqlex.Message)
            End Try
        End Sub


    Regards From Amr_Aly

    Wednesday, May 9, 2018 1:35 PM
  • Going with this overly long thread which Paul has provided you excellent guidance I would like to make a recommendation that hopefully does not duplicate other replies.

    Perhaps create a unique value to present pre-insert of a record which if the record is inserted use this value else discard the value. There are two parts to this method, a raw value which works yet hard to read by humans while the second part has dashes which makes it easy for a user to read.

    The following class permits returning both parts of the generated value.

    Public Class UniquePatientNumber
        Public Property Identifier As String
        Public Property DisplayValue As String
        Public Overrides Function ToString() As String
            Return DisplayValue
        End Function
    End Class

    Simple usage

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TextBox1.Text = GenerateId().ToString
    End Sub
    ''' <summary>
    ''' Generate a unique string where the raw value has no dashes
    ''' and the formatted value has dashes and upper cased which
    ''' is easier for humans to read.
    ''' </summary>
    ''' <returns></returns>
    Private Function GenerateId() As UniquePatientNumber
        Dim result = New UniquePatientNumber
    
        Dim x As Long = 1
        For Each b As Byte In Guid.NewGuid().ToByteArray()
            x *= (CLng(b) + 1)
        Next
    
        Dim rawValue = String.Format("{0:x}", x - Date.Now.Ticks)
        result.Identifier = rawValue
    
        Dim sb As New Text.StringBuilder()
    
        For i As Integer = 0 To rawValue.Length - 1
            sb.Append(rawValue(i))
            If i Mod 4 = 3 AndAlso i > 0 AndAlso i < rawValue.Length - 1 Then
                sb.Append("-")
            End If
        Next
    
        result.DisplayValue = sb.ToString.ToUpper
    
        Return result
    
    End Function

    EDIT

    Under project properties, Compile, Advance compile options set "remove integer overflow checks"

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, May 9, 2018 2:16 PM
    Moderator
  • >>

     how to update by DAO ? i tried your code(insertion code) with my update method but it failed so i'm searching for a way to update by DAO 

     

    Regards From Amr_Aly

    <<

    If you are performing an UPDATE (not INSERT) then the code is virtually the same for DAO, but with the Edit method instead of AddNew. You would specify the Patient_no of the record you want to edit in the query to return the row to be updated. Here is an example of how an UPDATE would work with DAO:

            Dim Recordset As Microsoft.Office.Interop.Access.Dao.Recordset
            Dim Database As Microsoft.Office.Interop.Access.Dao.Database
            Dim DAODBEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("TestDB.accdb", False, False, ";PWD=pass")
            Recordset = Database.OpenRecordset("SELECT * FROM TableName WHERE Patient_no = " & PatientNumber, Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset)
            
    		If Not Recordset.EOF Then
    			'Update columns where necessary
    			Recordset.Edit()
    			Recordset("Name").Value = cbxPatName.Text
    			Recordset("Address").Value = cbxAddress.Text
    			Recordset("Birthdate").Value = DTPicker.Value
    			Recordset("Age").Value = txtAge.Text
    			Recordset("Phone").Value = txtPhone.Text
    			Recordset("Mob").Value = txtMob.Text
    			Recordset("HusName").Value = cbxHusband.Text
    			Recordset("Job").Value = cbxJob.Text
    			Recordset("Male").Value = chbxMale.Checked
    			Recordset("Female").Value = chbxFem.Checked
    			Recordset("Married").Value = rdoMarr.Checked
    			Recordset("Divorced").Value = rdoDiv.Checked
    			Recordset("Single").Value = rdoSin.Checked
    			Recordset("Widowed").Value = rdoWid.Checked
    			Recordset.Update()  
    		Else
    		    MsgBox("Patient number not found!")
    	        End If		
    		
    		Recordset.Close()
    		Database.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, May 10, 2018 1:00 PM
  • Thanks Karen ,

    I appreciated your efforts ... your first code is so helpful and i want to know , How to make an update to this code ? or i have to open a new question in order to answer ..I know that i made a big disturbance ........ i pleased to get help from you karen 

    Public Function AddNewRow(
    ByVal pName As String,
    ByVal pContact As String,
    ByVal pContactTitle As String,
    ByVal pJoinDate As Date,
    ByVal pData1 As DateTime,
    ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
        Try
            Using cn As New OleDbConnection("TODO")
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                    <SQL>
                        INSERT INTO Customer 
                            (
                                CompanyName,
                                ContactName,
                                ContactTitle,
                                JoinDate,
                                Data1
                            ) 
                        Values
                            (
                                @CompanyName,
                                @ContactName,
                                @ContactTitle,
                                @JoinDate,
                                @Data1
                            )
                    </SQL>.Value
    
                    cmd.Parameters.AddWithValue("@CompanyName", pName)
                    cmd.Parameters.AddWithValue("@ContactName", pContact)
                    cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                    cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
                    cmd.Parameters.AddWithValue("@Data1", pData1)
                    cn.Open()
    
                    cmd.ExecuteNonQuery()
    
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
    
                End Using
            End Using
    
        Catch ex As Exception
            Success = False
        End Try
    
        Return Success
    
    End Function


    Regards From Amr_Aly


    • Edited by Amr_Aly Thursday, May 10, 2018 4:33 PM
    Thursday, May 10, 2018 4:19 PM

  • If you are performing an UPDATE (not INSERT) then the code is virtually the same for DAO, but with the Edit method instead of AddNew. You would specify the Patient_no of the record you want to edit in the query to return the row to be updated. Here is an example of how an UPDATE would work with DAO:

            Dim Recordset As Microsoft.Office.Interop.Access.Dao.Recordset
            Dim Database As Microsoft.Office.Interop.Access.Dao.Database
            Dim DAODBEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine()
    
            Database = DAODBEngine.OpenDatabase("TestDB.accdb", False, False, ";PWD=pass")
            Recordset = Database.OpenRecordset("SELECT * FROM TableName WHERE Patient_no = " & PatientNumber, Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset)
            
    		If Not Recordset.EOF Then
    			'Update columns where necessary
    			Recordset.Edit()
    			Recordset("Name").Value = cbxPatName.Text
    			Recordset("Address").Value = cbxAddress.Text
    			Recordset("Birthdate").Value = DTPicker.Value
    			Recordset("Age").Value = txtAge.Text
    			Recordset("Phone").Value = txtPhone.Text
    			Recordset("Mob").Value = txtMob.Text
    			Recordset("HusName").Value = cbxHusband.Text
    			Recordset("Job").Value = cbxJob.Text
    			Recordset("Male").Value = chbxMale.Checked
    			Recordset("Female").Value = chbxFem.Checked
    			Recordset("Married").Value = rdoMarr.Checked
    			Recordset("Divorced").Value = rdoDiv.Checked
    			Recordset("Single").Value = rdoSin.Checked
    			Recordset("Widowed").Value = rdoWid.Checked
    			Recordset.Update()  
    		Else
    		    MsgBox("Patient number not found!")
    	        End If		
    		
    		Recordset.Close()
    		Database.Close()



    Many thanks Paul ,

    That's it Now I can Insert and Update

    Please forgave me about the shortage of knowledge in VB.net ..........thanks for all  

    • Edited by Amr_Aly Thursday, May 10, 2018 4:25 PM
    Thursday, May 10, 2018 4:20 PM


  • @Amr_Aly

    I know it it senseless to write in this thread, but for those seeing this every time appear. I assume that Karen has given a solutions with System.Data. Paul has given you especial solutions with ADODB.

    There is a very important difference which makes them so different that it is comparing planes with ships (both used for transport)

    1. System.Data is disconnected (which means it needs updates of the data).
    2. OleDB is connected (which means every change in the program result in an automatic update).  

    So as written more times in this thread, you started with a plane for your transport but are now busy with a ship. What is your goal. Trolling or with a problem?




    I know that this is a valuable words but i'm not understand it till now ........So please forgave me sir , I appreciated your efforts 

    Thanks Cor .


    Regards From Amr_Aly




    • Edited by Amr_Aly Thursday, May 10, 2018 4:35 PM
    Thursday, May 10, 2018 4:31 PM

  • Many thanks Paul ,

    That's it Now I can Insert and Update

    Please forgave me about the shortage of knowledge in VB.net ..........thanks for all  

    No problem. I'm glad we were able to resolve the communication issues. :-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 10, 2018 4:33 PM
  • You are so cool and a good man ,I'm Pleased to know someone like you Paul Many thanks

    Regards From Amr_Aly

    Thursday, May 10, 2018 4:38 PM