Results 1 to 30 of 30

Thread: auto increment number(in a textbox) in vb.net

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    auto increment number(in a textbox) in vb.net

    Hi,
    I want to create auto increment number in vb.net like : -

    01, 02, 03, 04 etc.. so it is in order.

    I am using VB NET 2008 EXPRESS connection OleDb to MS access

    I want to do the auto increment for my textbox MemberID

    I have tried multiple ways and had no luck such as : -
    If txtMemberID.Text = "" Then
    txtMemberID.Text = 1
    Else
    txtMemberID.Text = txtMemberID.Text + 1
    End If



    'Dim include As Integer
    'Integer.TryParse(txtMemberID.Text, include)
    'txtMemberID.Text = (Maxrows + 1)

    Connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\Laura\Desktop\VB NEW BACKUPS\Back Up 19.04.12 1\VB Assignment\Members Database\Members.mdb"
    Connect.Open()
    Try
    cmd = New OleDbCommand("select * FROM tblMembers ", Connect)
    Dim dr As OleDbDataReader = cmd.ExecuteReader
    If dr.Read Then
    txtMemberID.Text = dr.Item(0)
    Else
    txtMemberID.Text = "27"
    End If
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try



    I want to write the code not use the autonumber in Access as my lecturer does not want me to do it this way

    Any suggestions please really am struggling with this one.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: auto increment number(in a textbox) in vb.net

    To increase it by one, declare an integer and any time you wish to increase it by one simply do so and reflect those changes in the textbox. Here is a quick example of what I mean:

    Code:
    Option Strict On
    Option Explicit On
    
    Public Class Form1
        Dim i As Integer = 0
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            TextBox1.Text = CStr(i)
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            i = i + 1
            TextBox1.Text = CStr(i)
        End Sub
    End Class
    Any time I click my button it's adding 1 and the textbox reflects so.

    Edit - Also be sure to wrap your code in code tags.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: auto increment number(in a textbox) in vb.net

    Tell you lecture that not doing that is an issue. If this is a single user system then I guess not a big deal, multiple user systems will cause errors in the data due to duplication of numbers. Why would you not use the tools that are given for the purpose that they are supplied for.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    If you don't want the user to enter the number or change the number once it is in there, then you should use a label rather than a textbox. After all, if you use a textbox, the user might change the value, and then where will you be? Of course, you could set the textbox to ReadOnly, or even disable it, to prevent the user from changing the value, but that will only cause frustration. If people see a textbox, they expect to be able to change the value. Use a label when the user is not allowed to change the value.
    My usual boring signature: Nothing

  5. #5
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: auto increment number(in a textbox) in vb.net

    I think the reason for the textbox in the first place was perhaps he drug the details out from his database rather than using the datagridview, atleast that's what I'm assuming. But I would agree with you a label would be much more user friendly.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    Hi,
    Thank You all for your post this was very helpful and especially thank you too dday9 for the code it works perfectly. I understand my system is for a single user and to be honest this has done the trick to mask my lecturers error. I am going to disable the textbox so they cannot enter information just one last question

    Is there a way to get round this error with maybe a message box

    The OleDbException was unhandled

    The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

    Thank you again much appreciated

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    Also, now it adds it however, once i have added it once say the same number will come up again. And the number goes to the front of my form so its 5,1,2,3,4 instead of 1,2,3,4,5

    For instance if my previous record is 4 and i add a new record that becomes 5. But when i try and add another record the same number comes up again 5

    Should i be entering some sort of record count to the field. Also, i have a problem with deleting the records so if i enter a record count would that update it or should i be using an update code????
    I have tried previously to enter a recordcount field however it failed i am not sure i am writing the code correctly as i have seen on example code such as : -

    Record Count()

    and the code has worked i presuming i am missing a public class and private sub so it recognise the record count

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: auto increment number(in a textbox) in vb.net

    Post your current code, need to see what your doing.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    What do you mean current code for which part I haven't yet incorporated it yet because i keep getting errors for the record count

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    Function getcount() As Integer
    Using cn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\Laura\Desktop\VB NEW BACKUPS\Back Up 19.04.12 1\VB Assignment\Members Database\Members.mdb")
    'provider to be used when working with access database
    cn.Open()
    Dim cmd As New OleDb.OleDbCommand("Select COUNT(*) FROM tblmembers", cn)
    Return cmd.ExecuteScalar()
    End Using
    End Function


    getcount()
    i = Maxrows + 1
    txtMemberID.Text = CStr(i)

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: auto increment number(in a textbox) in vb.net

    What errors and on which line of code?
    Also, getcount is suppose to return integer so you need to do something like this,
    Code:
    Maxrows = getcount()
    that said, this way is still a bad idea, what if you have 10 records, getcount recturns 10 and you make the MemberId equal 11. If you delete memberid 7 and then add a new member, getcount will return 10 again and you will get an error when you try to make the new memberid 11 because there is already a memberid of 11.

    Instead of using Count(*) try Max(memberid), this will return the highest memberid entered.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    In addition to that, the error you are getting is because of what we have all been saying: COUNT is not going to work for you. The error indicates that you are trying to use a number that has already been used. The way to fix it is to get MAX rather than COUNT, but you can't use *. Instead, you have to have something like this:

    "SELECT MAX(<your integer field here>) FROM tblmembers"
    My usual boring signature: Nothing

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    Thank You for that comment ok so i have changed my text and inserted MAX however, the error : -
    Syntax error (missing operator) in query expression 'MAX(*)'.
    However, before this an error was not presented

  14. #14
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    As I just stated:
    but you can't use *
    * means "all fields", but there is no way for that to work. You have to supply the specific field that you need to get the Max value for.
    My usual boring signature: Nothing

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    Ok thank you both sorry being a bit dumb at the minute because its late i now get an error
    No value given for one or more required parameters.

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    But i am unsure what value to enter would that not be record set?

  17. #17
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    That often means that there is a typo, extra character (usually punctuation), or something like that in the SQL string.
    My usual boring signature: Nothing

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    Ok thank you i have fixed the typo but still getting the same problem!!!
    Here is my code : -
    Function getmax() As Integer
    Using cn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\Laura\Desktop\VB NEW BACKUPS\Back Up 19.04.12 1\VB Assignment\Members Database\Members.mdb")
    'provider to be used when working with access database
    cn.Open()
    Dim cmd As New OleDb.OleDbCommand("SELECT MAX(txtMemberID) FROM tblmembers")
    'Return cmd.ExecuteScalar()
    End Using
    End Function

    Basically i now am not getting an error but say i am on 0 the next record is 1 that is added and then when i try to add a new record the next number is still 1!!???

  19. #19
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    Are you saving the 'next record' to the database, or are you just adding it to a datatable? If you are just adding it to a datatable, then the database knows nothing about it, and this whole design won't work. I ask about the datatable because in your other thread it sounded like that is what you were doing.
    My usual boring signature: Nothing

  20. #20
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: auto increment number(in a textbox) in vb.net

    I'm suprised that you aren't getting an error in your connection string with the "Back Up 19.04.12 1", I thought that periods, commas, and other special characters wheren't permitted in connection strings.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    O well I am using a datatable so is this why it dosent work well i have an edit button to make the fields editable and then an update button which should up date it in the database. So this design wont work then? Is there anything that possibly would work im struggling to find one piece of code that will do it. You said the max would but am i writing the code wrong for this as you said this is the other thread to use MAX???
    Nope no error when connecting to it but the connection string is just a back up anyway
    Any more suggestions because im not sure what else it will come under and my deadline is approaching and i am still having problems i only really need to correct this code i think and then it should work :S

  22. #22
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    Yeah, if you are working with a datatable, then you need to look at it this way:

    1) If the datatable has ALL the rows from the database table, then get the max value from the datatable.

    2) If the datatable only has SOME of the rows from the database table, then a mixed strategy will have to be employed.

    In the other thread, I showed a means to get the max from a datatable using LINQ. You stated that it didn't work, but never explained why. Of course, for LINQ to work, you would have to be targeting at least framework 3.5, and I don't know whether you ever identified which version of VB you are using, and which framework you are targeting. If you are targeting at least 3.5, then the LINQ solution would work for situation #1. If not, you'd have to say what was going wrong.

    If the situation is #2, then the strategy would have to be this: When you load the datatable, also run that MAX query to get the max value. That will only be the max value at the time the datatable is loaded, though, so the query can only be run that one time. You would then store that max value in a variable at form or global scope, and whenever you add a row, add to that variable, as well.
    My usual boring signature: Nothing

  23. #23

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    OK so my datatable has all the rows from the database in the table.
    The problem i have is i am unsure which framework i am using.
    But when filling my forms text boxes i have used data set.
    I have tried this code: -
    Dim max = (From m In DataSet.Tables("Members") Select DirectCast(m, DataRow).Item("MemberID")).Max

    However, no error message is produced when i click my new record button. A new record is created but nothing is shown in the Member ID textbox do i need to possibly use a fill or have i got this code on the wrong button should it be in a textbox?

    I have tried the code on the textbox Member ID events such as text changed and validate i am unsure if this is right or wrong and even if my code is correct

    I really do appreciate your help trying to solve my problem

  24. #24
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    Go into Project|Properties, go to the compile tab and way down at the bottom (possibly even off the bottom of the screen, depending on your screen size) is Advanced Compile Options. Click that button and you get a form that has the Target Framework.

    If you are getting no error messages, then the framework isn't the issue. That wouldn't even compile if you weren't targeting at least 3.5.

    The fact that there isn't an error message is kind of interesting. The thing to do is two steps:

    1) While still designing (you don't need to be running the code), move the mouse over max. That should show you what type it is. It should be Integer, unless I screwed something up...which, now that I look at it, I think I did. I believe it should be this:

    Code:
    Dim max = (From m In DataSet.Tables("Members") Select CInt(DirectCast(m, DataRow).Item("MemberID"))).Max
    Without that CInt, max is probably an object. That really shouldn't matter, though, because when you put it in a textbox you will be converting it to a string whatever it is.

    2) Put a breakpoint on the line right after that one. When execution stops on the breakpoint, put the cursor over max. At that point, a tooltip should show you what is in max. Is it right? Sometimes the tooltip doesn't show up for reasons I have never understood, in which case you click on max and press Shift+F9, which will definitely show you what is in max.
    My usual boring signature: Nothing

  25. #25

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    OK thank so
    I am working on NET 3.5
    Still no error message!
    The max is integer with the new code ( btw thanks for that)
    On the max it says : - <Extension> Public Function () MAX As Integer Returns the Maximum value in a sequence of System.Int32 values.
    Still nothing though?
    The field is not filled in automatically and the record still gets moved to the from of the form do i need to use my navigationalrecord to keep it as the last one even the number i entered was higher than all the others?
    Plus am i doing something with this text wrong should i be putting possibly a fill or something it in a different event. I wish it would come up with a error message makes things alot easier.
    Thank You

  26. #26
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: auto increment number(in a textbox) in vb.net

    Yeah, it would make it easier if it was flat out failing, but that's life.

    When I said put the cursor over max, I wasn't sufficiently clear. I meant the variable. You put the cursor over the .Max method for the LINQ statement, so you got the help for the .Max method. I meant the variable called max.

    The second step, where you put the breakpoint on the line after the line that fills the max variable, is kind of critical. If, at that point, you look at the variable called max and it has a value, you would be able to see whether it was the right value. Furthermore, if it is the right value, then you would know that the problem lies with displaying this value.
    My usual boring signature: Nothing

  27. #27

    Thread Starter
    Junior Member
    Join Date
    Apr 2012
    Posts
    24

    Re: auto increment number(in a textbox) in vb.net

    haha thank you all for your help i have sorted it i feel so silly was really easy phaaa
    thanks again

  28. #28
    New Member
    Join Date
    Aug 2014
    Posts
    7

    Re: auto increment number(in a textbox) in vb.net

    This should the function and call it on page load

    sub FillFields()
    dim d as integer
    dim da as new sqldataadapter("select table_id from table_name",con)
    if da.fill(ds, "datasettable") then
    d=ds.tables("datasettable").rows.count+1
    textbox1.text+d
    else
    textbox1.text=1
    end sub

  29. #29
    Registered User
    Join Date
    Aug 2014
    Posts
    1

    Re: auto increment number(in a textbox) in vb.net

    i think this is it

    Function GenerateID() As Long
    Try
    Disconnect()
    Connect()
    Dim result As Integer = 0
    Dim cmd As New MySqlCommand("Select max(SupplierID) as highest from Supplier", con)
    Dim dr As MySqlDataReader = cmd.ExecuteReader
    If dr.Read Then
    result = dr("highest") + 1
    Else
    result = 1
    End If
    dr.Close()
    cmd.Dispose()
    Return result
    Catch ex As InvalidCastException

    Return 1

    Catch ex As Exception

    End Try
    Catch ex As Exception

    End Try
    End Function

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click

    Me.lblSupplierID.Text = GeneratecollectorID()

    End Sub

  30. #30
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: auto increment number(in a textbox) in vb.net

    This thread is over 2 years old. Please do not resurrect old threads!
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width