locked
Auto Increment ID number in TextBox control from MS Access table

    Question

  • I am working on an application that needs to have a new ID number every time the user presses the ADD button to add a new record into the database. The data comes from an MS Access 2007 database. I need to display this ID number in a TextBox control on the form. I am using Visual Studio 2008 Express Edition Design Tools to build the application. 

    How can I achieve this in the TextBox on the form ? What happens if the user deletes a record from the database using the Delete button on the form ??? 

    Can someone please help me with the code on how to do this in the TextBox on the form ???

    Any help will be greatly appreciated.

    Thanks a lot!

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 4:44 AM

Answers

  • So you are storing it as an Integer??

    Presuming you do have an Integer column in the database then add a function like this to the partial class you should have for the table adapter.

          Public Function GetMaximumIndex() As Integer
             Dim command As New SqlCeCommand("SELECT MAX(PastorID) FROM PastorINFO", Connection)
             Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
             If (command.Connection.State And ConnectionState.Open) <> ConnectionState.Open Then
                command.Connection.Open()
             End If
             Dim returnValue As Object
             Try
                returnValue = command.ExecuteScalar
             Finally
                If previousConnectionState = ConnectionState.Closed Then
                   command.Connection.Close()
                End If
             End Try
             If returnValue Is Nothing OrElse returnValue.GetType Is GetType(DBNull) Then
                Return -1
             Else
                Return CInt(returnValue)
             End If
          End Function
    
    

     If you delete a record then nothing happens to the numbering, unless it was the highest number you deleted in which case it will be reused.

    To display the number with leading zeroes you can use the PadLeft function.


    • Edited by Dave299 Wednesday, October 19, 2011 11:12 AM
    • Marked as answer by maneeshmassey Wednesday, October 19, 2011 4:20 PM
    Wednesday, October 19, 2011 11:11 AM
  • Well you started the thread by saying you wanted it in a textbox so how about:

     

    PastorIDTextBox.Text = (GetMaximumIndex + 1).ToString
    

     


    • Edited by Dave299 Wednesday, October 19, 2011 2:25 PM
    • Marked as answer by maneeshmassey Wednesday, October 19, 2011 4:20 PM
    Wednesday, October 19, 2011 2:16 PM

All replies

  • Hello maneeshmassey,

    I am working on an application that needs to have a new ID number every time the user presses the ADD button to add a new record into the database. The data comes from an MS Access 2007 database. I need to display this ID number in a TextBox control on the form. I am using Visual Studio 2008 Express Edition Design Tools to build the application. 

    How can I achieve this in the TextBox on the form ? What happens if the user deletes a record from the database using the Delete button on the form ??? 

    Can someone please help me with the code on how to do this in the TextBox on the form ???

    Any help will be greatly appreciated.

    Thanks a lot!

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!


    to do what you ask and assign sufficient to bind the text property of the TextBox the value of the id field of your table.

    Select the text box.
    Find textbox in the properties of the DataBindings property.
    Expand the node until you find the Text property.

    Perform a search of the area affected by your BindingSource, see the following image.

    Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, October 19, 2011 5:57 AM
  • Hi Carmelo,

    I have attached a photo of my application. As you can see from the photo, the TextBox is already bound to the PastorINFOBindingSource. There is an ADD button on the form too, which the user presses to Auto Increment the PastorID field.

    You have not mentioned anything on how to increment the ID in this TextBox in code. 

    What needs to be done in code so I could achieve the above functionality ???

    Thanks.

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 6:10 AM
  • Hello maneeshmassey,

    Hi Carmelo,

    I have attached a photo of my application. As you can see from the photo, the TextBox is already bound to the PastorINFOBindingSource. There is an ADD button on the form too, which the user presses to Auto Increment the PastorID field.

    You have not mentioned anything on how to increment the ID in this TextBox in code. 

    What needs to be done in code so I could achieve the above functionality ???

    Thanks.

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!

    all I did was add a text box from the toolbox and give it as explained in the text property bindings and everything works, the only thing you need to verify at this point and have been assigned to the Id column of your table, the identity of true, otherwise you will not see the increase.
    This poui verify the re-opening the table and see proptietà in the ID field.

    However, I am attaching a sample file.

    https://skydrive.live.com/?cid=da622d2ca9c0a629#!/?cid=da622d2ca9c0a629&sc=documents&uc=1&id=DA622D2CA9C0A629%21286

    Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, October 19, 2011 7:24 AM
  • Hi 
    I really do not understand what you mean by the following:

    "...all I did was add a text box from  the toolbox and give it as explained in the text property bindings and everything works, the only thing you need to verify at this point and have been assigned to the Id column of your table, the identity oftrue, otherwise you will not see the increase.
    This poui verify the re-opening the table and see proptietà in the ID field.

    However, I am attaching a sample file.

    https://skydrive.live.com/?cid=da622d2ca9c0a629#!/?cid=da622d2ca9c0a629&sc=documents&uc=1&id=DA622D2CA9C0A629%21286 ...."

    Could you please explain in a little bit more detail ???

    By the way, I was not able to open the link you gave as Skydrive server is down at the moment.

    Thanks a lot!

    Best Regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 7:52 AM
  • Hello maneeshmassey,

    Hi 
    I really do not understand what you mean by the following:

    "...all I did was add a text box from  the toolbox and give it as explained in the text property bindings and everything works, the only thing you need to verify at this point and have been assigned to the Id column of your table, the identity oftrue, otherwise you will not see the increase.
    This poui verify the re-opening the table and see proptietà in the ID field.

    However, I am attaching a sample file.

    https://skydrive.live.com/?cid=da622d2ca9c0a629#!/?cid=da622d2ca9c0a629&sc=documents&uc=1&id=DA622D2CA9C0A629%21286 ...."

    Could you please explain in a little bit more detail ???

    By the way, I was not able to open the link you gave as Skydrive server is down at the moment.

    Thanks a lot!

    Best Regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!

    standing has what I understand, forgive me but communication penalizes us, you have a text box where you want to display the field Id profitable deals time you add a record in the textBox value must increase by one.

    As you've set the binding and correct, but if you do not see anything, you should re-open the table in your database, find the field ID, and verify that the property is set to true identity, and identity increment is set to one.

    For example, if you can try now to download it.

    https://skydrive.live.com/?cid=da622d2ca9c0a629&sc=documents&uc=1&id=DA622D2CA9C0A629%21286#!/?cid=da622d2ca9c0a629&permissionsChanged=1&id=DA622D2CA9C0A629%21286!cid=DA622D2CA9C0A629&id=DA622D2CA9C0A629%21286&sc=documents


    If you still have questions ask quietly and sorry for my English is not good.

    Best Regars.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, October 19, 2011 8:32 AM
  • I'm really sorry Carmelo, but I am not able to follow your instructions. I thought you would explain the CODE to me on how to auto increment the ID field in the text box. I forgot to tell you that the ID field is NOT an AUTO NUMBER field, it is an ID created by me of the type "0000001", "0000002", "0000003" ... and so on. I need the number padded with 6 zeros before the number.

    What you are requesting me to do is something from the MS Access database side which is not what I want. I rather want to CODE it from Visual Basic.NET 

    The file you sent me on Skydrvie does not open on my Visual Studio 2008 edition. 

    Please explain me the code on how to get this functionality.

    I appreciate your concern.

    regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 9:08 AM
  • Hello maneeshmassey,

    I'm really sorry Carmelo, but I am not able to follow your instructions. I thought you would explain the CODE to me on how to auto increment the ID field in the text box. I forgot to tell you that the ID field is NOT an AUTO NUMBER field, it is an ID created by me of the type "0000001", "0000002", "0000003" ... and so on. I need the number padded with 6 zeros before the number.

    What you are requesting me to do is something from the MS Access database side which is not what I want. I rather want to CODE it from Visual Basic.NET 

    The file you sent me on Skydrvie does not open on my Visual Studio 2008 edition. 

    Please explain me the code on how to get this functionality.

    I appreciate your concern.

    regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!


    I understand now, sorry for misunderstanding you, I thought you referring to the ID field of the table.

    Well if it is not combined with the visual database, but only one thing you can do something like this, retrieve the current value of the text box BindingNavigatorPositionItem.

    Dim index As Integer = 0
    
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         index = Integer.Parse(BindingNavigatorPositionItem.Text)
            If index <= 10 Then
                index += 1
                Me.TextBox1.Text = "00000" & index.ToString
            Else
                Me.TextBox1.Text = "000000" & index.ToString
            End If
        End Sub
    
    And in the Clcik event Button
    
       Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click
            index = Integer.Parse(BindingNavigatorPositionItem.Text)
            If index <= 10 Then
                index += 1
                Me.TextBox1.Text = "00000" & index.ToString
            Else
                Me.TextBox1.Text = "000000" & index.ToString
            End If
        End Sub
    


    Keep in mind that this code in the case of many id be reviewed, and only an idea, as you see I put the change if the number of zeros, and greater than or equal to 10.
    I hope you understand, and I've given you an idea.

    Best Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, October 19, 2011 9:34 AM
  • Thanks so much for the code Carmelo. I forgot to tell you, I am not using the BindingNavigator given at the top of the window. I will remove this along with the BindingNavigator Object given at the bottom of the component tray. 

    In this case, when I do not want to use the BindingNavigator in my application, how should I modify the code given by you so this functionality is achieved WITHOUT the BindingNavigator ???

    Waiting for your reply.

    Thanks a lot!

    Best Regards,

    Maneesh

     

     


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 9:56 AM
  • Maneesh

    You would complete your database far quicker if you were to follow some of the suggestions you have received.

    In previous threads I explained why it is usually a good idea to have a numeric ID field.  But you insist on using a string.

    Carmelo assumed your ID field was an identity field because that would be the sensible way to do it - but you want to do it differently.

    You say you need the ID to have 6 zeroes in front of the number.  Does that mean 123 should be entered as 000000123.  Or do you mean the overall length of the field should always be 6, i.e. 123 should appear as 000123.

    If the latter then you can still use an Integer field in the database.  Padding with leading zeroes is just a formatting issue, there is no need to store them in the database.

    To get the highest number currently used in the database you could use the SQL MAX function (provide of course that you used an Integer and not a string).

     

     

    Wednesday, October 19, 2011 10:04 AM
  • Thanks for jumping right in Dave !

    You missed me by miles Dave ! In my previous posts to you, I had told you that the PastorID field was already made into a NUMERIC field by me and I deleted all the STRING stuff !!!

    Now my PastorID is PURELY a numeric field. 

    The ID field is a '6-DIGIT' number ID. Meaning to say that this a NUMBER and not that I want 6 zeros always before the number.

    Hope this clears some of the doubts you were having.

    Regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 10:16 AM
  • Now my PastorID is PURELY a numeric field.

    The ID field is a '6-DIGIT' number ID. Meaning to say that this a NUMBER and not that I want 6 zeros always before the number

    Are you talking about the same field in those two sentences?

    Earlier you said:

    the ID field is NOT an AUTO NUMBER field, it is an ID created by me of the type "0000001", "0000002", "0000003" ... and so on. I need the number padded with 6 zeros before the number

     

    Wednesday, October 19, 2011 10:25 AM
  • Hello maneeshmassey,

    Thanks so much for the code Carmelo. I forgot to tell you, I am not using the BindingNavigator given at the top of the window. I will remove this along with the BindingNavigator Object given at the bottom of the component tray. 

    In this case, when I do not want to use the BindingNavigator in my application, how should I modify the code given by you so this functionality is achieved WITHOUT the BindingNavigator ???

    Waiting for your reply.

    Thanks a lot!

    Best Regards,

    Maneesh

     

     


    The problem with being punctual is, there's nobody there to appreciate it !!!


    In this case, if the textbox is not tied to anything, it retrieves the number of rows from the table.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Me.TextBox1.Text = Me.yourdatasetname.yourtablename.Rows.Count.ToString End Sub

    It is always the talk of the formatting if you always want six figures even as explained by Dave.
    Regards.

     


    Carmelo La Monica

     

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica


    Wednesday, October 19, 2011 10:29 AM
  • Firstly, yes Dave I am talking the same thing in those two sentences above.

    I had written above:

    "0000001", "0000002", "0000003" ... etc. Sorry the extra zero was a typo. And sorry also I could'nt explain that well. This is my mistake.

    But what I really meant was that this number is a 6-digit number. For example: This would increment as follows: 000001, .......000090 ... 000099 .....000100 ....001000....001001 ......001020... 011000 .... 100000 .....etc.

    Obviously when its a 6-digit number, the zeros towards the left hand side would decrease with the increase of the number.

    Secondly, what code should be used in this scenario so as every time the user presses the ADD button to add a new record, the application checks to see MAX ID in the database and increments it by a value of 1.

    Thirdly, what would happen in a situation in which the user deletes a record from the database ? Will the application again assign the deleted number after checking the MAX ID ??? Just wanted to know how this works.

    Regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 10:50 AM
  • I did try the code you suggested above and ran the application but nothing showed up in the ID TextBox. 

    I will try to explain a bit more what I have done. 

    I dragged the TextBox control from the DataSource window and dropped in onto the form. Then I set the TextBox.Text property of the TextBox to "Clear" so that when the user presses the ADD button, the TextBox may show the Auto Incremented ID. Is this the correct way of doing it ???

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 11:04 AM
  • So you are storing it as an Integer??

    Presuming you do have an Integer column in the database then add a function like this to the partial class you should have for the table adapter.

          Public Function GetMaximumIndex() As Integer
             Dim command As New SqlCeCommand("SELECT MAX(PastorID) FROM PastorINFO", Connection)
             Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
             If (command.Connection.State And ConnectionState.Open) <> ConnectionState.Open Then
                command.Connection.Open()
             End If
             Dim returnValue As Object
             Try
                returnValue = command.ExecuteScalar
             Finally
                If previousConnectionState = ConnectionState.Closed Then
                   command.Connection.Close()
                End If
             End Try
             If returnValue Is Nothing OrElse returnValue.GetType Is GetType(DBNull) Then
                Return -1
             Else
                Return CInt(returnValue)
             End If
          End Function
    
    

     If you delete a record then nothing happens to the numbering, unless it was the highest number you deleted in which case it will be reused.

    To display the number with leading zeroes you can use the PadLeft function.


    • Edited by Dave299 Wednesday, October 19, 2011 11:12 AM
    • Marked as answer by maneeshmassey Wednesday, October 19, 2011 4:20 PM
    Wednesday, October 19, 2011 11:11 AM
  • Okay, I copied the code given by you over to the Partial Class of the PastorINFOTableAdapter.

    And now how should I call this function ??? Should I call the function as such:

    Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
            Me.Validate()
            Me.PastorINFOBindingSource.AddNew()
            PastorINFOTableAdapter.GetMaximumIndex()
        End Sub
    



    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 11:48 AM
  • Well you started the thread by saying you wanted it in a textbox so how about:

     

    PastorIDTextBox.Text = (GetMaximumIndex + 1).ToString
    

     


    • Edited by Dave299 Wednesday, October 19, 2011 2:25 PM
    • Marked as answer by maneeshmassey Wednesday, October 19, 2011 4:20 PM
    Wednesday, October 19, 2011 2:16 PM
  • Sorry Dave, I never got this code to work. You have to help me with the next steps.

    Besides this, I have been looking at Beth Massi's Blog and read this article there. Now I am really confused with MS Access database by making the Primary Key an Auto Number field in order to use Identity Scope.

    http://blogs.msdn.com/b/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

    And here's another one:

    http://support.microsoft.com/kb/815629

    Do you think it would be right to use an Auto Number for the Primary Key ?? I thought we should never use the Primary Key as an Auto Number ????


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 2:29 PM
  • Sorry Dave, I never got this code to work.

    Not knowing what you've done I can't suggest why but the code is lifted straight from one of my projects and works fine for me.

    Do you think it would be right to use an Auto Number for the Primary Key

    YES!!!!!!!!!!!!! 

    I thought we should never use the Primary Key as an Auto Number ???

     Where did you get that from?

     

    Wednesday, October 19, 2011 2:43 PM
  • Please don't get excited Dave. Your code works perfectly !!! This post was uploaded before I got your above code:

    PastorIDTextBox.Text = (GetMaximumIndex + 1).ToString

    I was just making sure as I read somewhere that we should not use the Primary Key as the Auto Number. Hence I created a Numeric Field assigned that as my Primary Key. Is this perfectly ok to do ????


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 2:51 PM
  • Yes - it's perfectly ok.  If you look at Beth's code her function to retrieve the identity value is called SetPrimaryKey.

    It's quite common not to display the ID to the user - why would they need to know what unrelated number has been assigned - so it may not be necessary to retrieve the identity for every record you add.

    Wednesday, October 19, 2011 3:01 PM
  • Minor issue here. 

    I'm using this code for padding the ID's with zeros:

    Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
            Me.Validate()
            cmdAdd.Enabled = False
            ComboDistrict.Enabled = True
            Me.PastorINFOBindingSource.AddNew()
            txtPastorID.Text = (PastorINFOTableAdapter1.GetMaximumIndex() + 1).ToString.PadLeft(6, "0"c)
        End Sub
    

    But as soon as the Tab key is pressed to move to the next field, the zeros are removed and I get only the number without any zeros to the left. So what's the use of this code anyway ???


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 3:11 PM
  • Isn't there a way to stop the zeros from 'falling off' when the tab key is pressed ???
    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 3:35 PM
  • That will happen with a textbox bound to an Integer column in the dataset.  You can get round it in SQLServer with something like this in your select statement.  I don't know whether it will work with Access.

    Replace PastorID in the select statement with:

    RIGHT('00000'+ CONVERT(VARCHAR,PastorID),6) AS PastorID

    Wednesday, October 19, 2011 4:08 PM
  • No that didn't work in Access. But anyway at least I got the Auto Increment to work.

    Thank you once again Dave!

    Best Wishes,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 4:19 PM
  • Just came back to say, its been a pleasure working with you on the project thus far.

    I hope to continue to receive your help, suggestions and advice as I work towards finishing this application soon.

    Thanks once again!

    Regards,

    Maneesh


    The problem with being punctual is, there's nobody there to appreciate it !!!
    Wednesday, October 19, 2011 4:36 PM