CODEWITHSUNDEEP
.netsqlvb.net
Danjor
Danjor

Reputation: 121

Code always generates the same value

I was using a Btngen to generate an ID to my TextBox and I have 0001 and save it to my DB in SQLSERVER the problem is that after I close the program and re-open it again the value that generate to my TextBox after clicking the Btngen again is still 0001. How can I avoid that? How can I retain the value that already generates and move to the next value? I use increment but it only works while the program is still running but if I close the program and run it again it goes back to 0001 can anyone tell me how to do it?

Here is my code :

Dim p1num As Integer = 0
 p1num += 1
 txtPNumber.Text = p1num.ToString("D4")

Dim SQLcon As New SqlConnection
        Dim SQLdr As SqlDataReader
        Try
            SQLcon.ConnectionString = "Data Source=####;Initial Catalog=####;Persist Security Info=True;User ID=####;Password=####"
            Dim SQLcmd As New SqlCommand("INSERT INTO dbo.Patients" & _
            "(pIDNo)" & _
            "VALUES(@pIDNo",SqlCon)
            SQLcmd.Parameters.AddWithValue("@pIDNo", txtPNumber.Text)
            SQLcon.Open()
            MsgBox("Patient Added!", MsgBoxStyle.Information)
            SQLdr = SQLcmd.ExecuteReader()
        Catch ex As Exception
            MessageBox.Show("Error Occured, Can't Add Patient!" & ex.Message)
        Finally
            SQLcon.Close()
        End Try

Upvotes: 0

Views: 55

Answers (1)

James Osborn
James Osborn

Reputation: 1275

You shouldn't do it this way.

If you need each record to have a unique, generated integer id that increments by 1 each time a record is created, then use an Identity field in the database.

In the Identity Specification of the column properties, set:

  • (IsIdentity) to 'Yes'
  • Identity Increment to '1'
  • Identity Seed to '1'

You should probably also make this value a Primary Key on the table.

Once you save a record, you should then query the database to display the generated ID value in your UI.

Upvotes: 1

Related Questions