How to retrieve the max id from sql database and autoincrement it and display on the form(vb.net)

Question

0.00/5 (No votes)

See more:

Hello,

Can anyone please help me in finding max id for EmpId col and incremnet it and display onthe other form when user adds the new employee...(vb.net and sql 2005)

Code:

VB

Private Sub txtadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtadd.Click
       Dim da As SqlDataAdapter
       Dim ds As New DataSet
       Dim sql As String
       Dim a As Integer

       Dim connectionString As String = "Data Source=Localhost;" + "integrated security=yes;" + "database=Employee"
       Dim myconnection As SqlConnection = New SqlConnection(connectionString)
       myconnection.Open()
       sql = "select max(EmpID) from Employee_details"
       da = New SqlDataAdapter(sql, myconnection)
       da.Fill(ds, "Employee_Details")
       Dim command As SqlCommand
       command = New SqlCommand(sql)

       Try


           txtid.Text = a + 1
       Catch
           MsgBox("Can not open connection ! ")
       Finally
           myconnection.Close()
       End Try

Posted 7-Oct-11 3:50am

Nisharani

Add a Solution

Comments

Bala Selvanayagam 7-Oct-11 10:02am

This is not a good practise Nisharani as high lighted by
OriginalGriff.

Why do not you set the EmployeeID field as identity field (auto number) and to retrieve and show the EmployeeID once the new Employee is saved into the database.

Always go by the best practise

Nisharani 7-Oct-11 10:10am

Ok..thanks anyways..

Kschuler 7-Oct-11 10:13am

Can you Improve the question and add what specifically you are having trouble with? Is that code causing an error? Where are you stuck?

Jignesh Khant 1-Jul-14 7:25am

Is your EmpID column auto increment in db?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-10-07T03:53:00

Solution 1

That is a very, very bad idea.

Why? Simple: the only logical reason for doing this is that you are creating a new employee, and want to give him the next Employee ID in sequence. But - SQL Server is a multiuser database. What happens if two people decide to create a new Employee at similar times? The maximum value from the database has not changed, so they both will assume that the value read plus one is available...

Posted 7-Oct-11 3:53am

OriginalGriff

Comments

Nisharani 7-Oct-11 9:57am

I am just assuming for time being a single user enter an employee one at a time..can you please
help in this?

OriginalGriff 7-Oct-11 10:16am

You cannot assume that: it leaves holes for really nasty intermittent bugs in the future. If you use a multiuser database you must write your code assuming multiuser access. Otherwise you are just playing about.

damodara naidu betha 1-Jul-14 7:32am

Yes. You are right. 5+

damodara naidu betha · Answer 2 · 2014-07-01T01:58:00

Hi. I am not saying that your approach is bad. It is totally up to your application's scope and usage. Lets go into your code.

I found few issues in your code.

1. Why are you opening database connection, if you use dataadapter to get dataset? DataAdapter's Fill method takes care of connection open/close headache.

DataAdapters Fill Method

2. For your requirement SqlCommand object's ExecuteScalar method suits well.

VB

Dim sql As String = "select max(EmpID) As NewEmp from Employee_details"
Dim connectionString As String = "Data Source=Localhost;integrated  
security=yes;database=Employee"
Dim newEmpId As Int32 = 0
Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        Try
            conn.Open()
            newEmpId = Convert.ToInt32(cmd.ExecuteScalar())+1
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try 
    End Using 

//Here add your code to display the newEmpId in your form.