Question How can i make auto increment in my textbox

Prince29

Member
Joined
Sep 15, 2015
Messages
10
Programming Experience
Beginner
All i want is every time i finish the registration and I click ADD button I want my ID increase one . Like 1 , 2 , 3, ... Here's my code

Try
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\HMSdb.accdb")
cn.Open()
cmd = New OleDbCommand("select * from PatientRegistration ", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
If dr.Read Then
txtPatientID.Text = dr.Item(0) + 1
Else
txtPatientID.Text = "1"
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
If you're using an Access database then that is not something that you should be doing yourself. You should be defining your ID column as type AutoNumber in the database and then, when you insert the records, the database will automatically generate sequential values.
 

Prince29

Member
Joined
Sep 15, 2015
Messages
10
Programming Experience
Beginner



  • I already try that but i don't know how i will display the ID column in my textbox , because im new in vb.net so its hard . can you give me some example of what your're saying ?


 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Please don't post twice in 21 minutes. If you have nothing new to add, don't post again at all.

As for the question, the value will be generated when you save the record so you won't be able to display it until the record is saved. Are you clear on that?

Once the record is saved, you can then query the database for the last value generated. To do that, you would use the following SQL:
VB.NET:
SELECT @@IDENTITY
You execute that by creating an OleDbCommand and calling ExecuteScalar.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Sorry . i don't know how to use ExecuteScalar .
What did you find when you searched the web for information on that?
and what is @@IDENTITY ? is that column name ?
What did you find when you searched the web for information on that?

I'm more than happy to help with stuff that you can't do but I'm not here as a substitute for Google. You try first and then you ask if you can't find what you need.
 

Prince29

Member
Joined
Sep 15, 2015
Messages
10
Programming Experience
Beginner
i know now what is executescalar . but what if i don't use the autonumber in database ? i want to generate autonumber in my textbox ? you think it is possible ?
because in my code that i post ., when i run the program and register i got the patient id = 1 and register again patient id = 2 but when i register again it remain 2 . what wrong with my code ?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
what wrong with my code ?
You're retrieving every record in the table but then you only read the first one, so you're always going to get the very first ID. The first ID is 1 so you always see 2 in your TextBox because that's (1 + 1).

What's the point of retrieving every record when you are only interested in the last one? What's the point in getting the whole record when you're only interested in one column? You should be querying to get just the largest existing ID from the table. That's retrieving a single value and that's what you use ExecuteScalar for.
i know now what is executescalar .
So now you can put that knowledge to the test.
 

Prince29

Member
Joined
Sep 15, 2015
Messages
10
Programming Experience
Beginner
Ok . i'll try to recode my code , even though it's hard for me .
by the way this is my thesis project and i'm only a 3rd college student .
can you give me sample of how should i start to recode my code ?
 

Prince29

Member
Joined
Sep 15, 2015
Messages
10
Programming Experience
Beginner
Sir this is my new code but i i got error " Datatype mismatch in criteria expression. " in my database i already set the PatientID as primary and autonumber . but why i got error like this ? " Datatype mismatch in criteria expression. "



Try


Dim query As String = "Insert Into PatientRegistration (PatientID) Values ('" & txtPatientID.Text & "')"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\HMSdb.accdb"
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand(query, conn)
cmd.Parameters.AddWithValue("", txtPatientID.Text)
conn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = query2
ID = cmd.ExecuteScalar()
End Using
End Using


Catch ex As Exception
MsgBox(ex.Message)
End Try
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Just change your SQL code as directed, call ExecuteScalar instead of ExecuteReader and then use the value it returns. Note that that value will be NULL if there's no record, so keep that in mind. You can't just use the value as a number because it may not be.
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

To add to what has already been said, and assuming that you now have the PatientID Column set as an AutoNumber column in your table then this PatientID Column is of type Long Integer and yet you are trying to add a String Value to that column by saying ‘PatientID.Text’.

You should NOT be trying to add anything to this column! You should be adding things like user names and user details etc from the Registration information using your Insert command and then RETURN the PatientID Column using the @@Identity Property.

Hope that helps.

Cheers,

Ian

BTW, Use the link again that you used to get your initial code example to work out how to use Parameters correctly. It shows you and yet you did not follow along.
 

Prince29

Member
Joined
Sep 15, 2015
Messages
10
Programming Experience
Beginner
Sir Ian what if my database patientregistration table is blank or no data . then i run the program then i want to display 1 in my textbox PatientID . It is possible or not ?
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

Well in that case it’s not a REAL Table and you are trying to do something which goes against everything which is good. So, do yourself a BIG FAVOUR and ADD a second column to your table, call it whatever you want, let’s say PatientName, and then add a name string to that column using the Insert Statement. Then you get the ID from the PatientID Column using @@Identity.

That’s it, really simple!

Cheers,

Ian
 
Top Bottom