如何从sql数据库中检索max id并自动增加它并在表单上显示（vb.net） [英] How to retrieve the max id from sql database and autoincrement it and display on the form(vb.net)

问题描述

您好，



任何人都可以帮助我找到EmpId col的最大ID并增加它并在用户添加新员工时显示在另一个表单上... （vb.net和sql 2005）



代码：

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:

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

推荐答案

这是一个非常非常糟糕的主意。



为什么？简单：这样做的唯一合乎逻辑的原因是你正在创建一个新员工，并希望按顺序给他下一个员工ID。但是 - SQL Server是一个多用户数据库。如果两个人决定在相似的时间创建一个新的Employee会发生什么？数据库中的最大值没有改变，所以他们都会假设读取的值加上一个......

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...

嗨。我不是说你的方法很糟糕。这完全取决于你的应用程序的范围和用法。让我们进入你的代码。



我在你的代码中发现了一些问题。



1.如果你使用dataadapter来打开数据库连接的原因et dataset？ DataAdapter的Fill方法负责连接打开/关闭头痛。



DataAdapters填充方法



2.根据您的要求，SqlCommand对象的ExecuteScalar方法非常适合。

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.

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.

这篇关于如何从sql数据库中检索max id并自动增加它并在表单上显示（vb.net）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！