Question How can i make auto increment in my textbox

[Prince29]

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]

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]

​

• 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]

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:

SELECT @@IDENTITY

You execute that by creating an OleDbCommand and calling ExecuteScalar.

 

[Prince29]

Sorry . i don't know how to use ExecuteScalar .

and what is @@IDENTITY ? is that column name ?

 

[jmcilhinney]

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]

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]

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]

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]

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]

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.

 

[Prince29]

i can't understand . can you show me what your're saying ?

 

[IanRyder]

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]

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]

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