-
Apr 27th, 2012, 02:10 PM
#1
Thread Starter
Junior Member
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.
-
Apr 27th, 2012, 02:19 PM
#2
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.
-
Apr 27th, 2012, 02:33 PM
#3
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
-
Apr 27th, 2012, 02:59 PM
#4
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
-
Apr 27th, 2012, 03:03 PM
#5
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.
-
Apr 28th, 2012, 11:49 AM
#6
Thread Starter
Junior Member
-
Apr 28th, 2012, 12:02 PM
#7
Thread Starter
Junior Member
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
-
Apr 28th, 2012, 12:33 PM
#8
Re: auto increment number(in a textbox) in vb.net
Post your current code, need to see what your doing.
-
Apr 28th, 2012, 01:05 PM
#9
Thread Starter
Junior Member
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
-
Apr 28th, 2012, 01:53 PM
#10
Thread Starter
Junior Member
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)
-
Apr 28th, 2012, 03:50 PM
#11
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.
-
Apr 28th, 2012, 05:04 PM
#12
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
-
Apr 28th, 2012, 05:06 PM
#13
Thread Starter
Junior Member
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
-
Apr 28th, 2012, 05:07 PM
#14
Re: auto increment number(in a textbox) in vb.net
As I just stated: * 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
-
Apr 28th, 2012, 05:09 PM
#15
Thread Starter
Junior Member
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.
-
Apr 28th, 2012, 05:17 PM
#16
Thread Starter
Junior Member
Re: auto increment number(in a textbox) in vb.net
But i am unsure what value to enter would that not be record set?
-
Apr 28th, 2012, 05:24 PM
#17
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
-
Apr 28th, 2012, 06:07 PM
#18
Thread Starter
Junior Member
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!!???
-
Apr 28th, 2012, 07:01 PM
#19
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
-
Apr 28th, 2012, 07:45 PM
#20
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.
-
Apr 29th, 2012, 11:51 AM
#21
Thread Starter
Junior Member
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
-
Apr 29th, 2012, 12:56 PM
#22
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
-
Apr 29th, 2012, 01:34 PM
#23
Thread Starter
Junior Member
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
-
Apr 29th, 2012, 08:58 PM
#24
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
-
Apr 30th, 2012, 04:06 AM
#25
Thread Starter
Junior Member
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
-
Apr 30th, 2012, 11:47 AM
#26
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
-
Apr 30th, 2012, 02:19 PM
#27
Thread Starter
Junior Member
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
-
Aug 26th, 2014, 12:41 AM
#28
New Member
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
-
Aug 26th, 2014, 08:36 AM
#29
Registered User
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
-
Aug 26th, 2014, 08:59 AM
#30
Re: auto increment number(in a textbox) in vb.net
This thread is over 2 years old. Please do not resurrect old threads!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|