VB.NET中ExecuteScalar无法返回Identity值，MessageBox显示0的问题

嘿，我一眼就瞅出问题所在了——你用ExecuteScalar()来获取新员工的ID，但你的INSERT语句根本没返回这个自增ID啊！ExecuteScalar()对于普通的INSERT操作，默认返回的就是0，这就是为啥MessageBox里显示0，但数据库里ID还是正常递增的原因。

下面给你一步步搞定：

核心修改：让INSERT语句返回自增ID #

在SQL Server里，要精准获取刚插入的自增主键，最靠谱的方式是用SCOPE_IDENTITY()，它只会返回当前会话、当前作用域内生成的自增ID，不会被其他并行操作干扰。你得把原来的INSERT语句改成这样：

Dim add As String = String.Empty
add &= "insert into record (firstname, middlename, lastname, birthday, age, department) "
add &= "values (@fn, @mn, @ln, @bd, @age, @dept); "
add &= "SELECT SCOPE_IDENTITY();" ' 新增这行，返回刚插入的ID

其他细节优化（可选但能避坑） #

除了核心问题，还有两个小细节容易踩雷，顺便给你调整了：

• 日期比较的坑：你直接用abday.Text > "2000-01-01"，如果用户输入的日期格式不对，直接就报错了。建议先转成DateTime类型再比较：

  Dim inputBirthday As DateTime
  If Not DateTime.TryParse(abday.Text, inputBirthday) Then
      MsgBox("Please enter a valid birthday format")
      Return
  End If
  If inputBirthday > New DateTime(2000, 1, 1) Then
      MsgBox("Birthday is not appropriate")
  End If
  

• 部门选择的判断：adept.SelectedItem = ""可能会因为SelectedItem是Nothing而报错，改成更稳妥的判断：

  ElseIf adept.SelectedItem Is Nothing OrElse String.IsNullOrEmpty(adept.SelectedItem.ToString()) Then
      MsgBox("Please select a Department")
  

修改后的完整代码片段 #

Dim add As String = String.Empty
add &= "insert into record (firstname, middlename, lastname, birthday, age, department) "
add &= "values (@fn, @mn, @ln, @bd, @age, @dept); "
add &= "SELECT SCOPE_IDENTITY();" ' 新增返回ID的语句

' 先处理日期格式验证
Dim inputBirthday As DateTime
If Not DateTime.TryParse(abday.Text, inputBirthday) Then
    MsgBox("Please enter a valid birthday format")
    Return
End If

If afn.Text.Length < 2 Then
    MsgBox("Please input More value on Firstname")
ElseIf amn.Text.Length < 2 Then
    MsgBox("Please input more value on Middlename")
ElseIf aln.Text.Length < 2 Then
    MsgBox("Please input more value on Lastname")
ElseIf inputBirthday > New DateTime(2000, 1, 1) Then
    MsgBox("Birthday is not appropriate")
ElseIf adept.SelectedItem Is Nothing OrElse String.IsNullOrEmpty(adept.SelectedItem.ToString()) Then
    MsgBox("Please select a Department")
Else
    Using conn As New SqlConnection("server=WIN10;database=hrdept;user=elix;password=blackant;")
        Using cmd As New SqlCommand
            With cmd
                .Connection = conn
                .CommandType = CommandType.Text
                .CommandText = add
                .Parameters.Add("@fn", SqlDbType.VarChar).Value = afn.Text
                .Parameters.Add("@mn", SqlDbType.VarChar).Value = amn.Text
                .Parameters.Add("@ln", SqlDbType.VarChar).Value = aln.Text
                .Parameters.Add("@bd", SqlDbType.Date).Value = inputBirthday ' 用验证后的DateTime
                .Parameters.Add("@age", SqlDbType.Int).Value = aage.Value
                .Parameters.Add("@dept", SqlDbType.VarChar).Value = adept.SelectedItem.ToString()
            End With
            Try
                conn.Open()
                ' 这里ExecuteScalar会返回SCOPE_IDENTITY()的结果，转成Integer
                Dim id As Integer = Convert.ToInt32(cmd.ExecuteScalar())
                If aage.Value < 20 Then
                    If MsgBox("Age must be 20 years old and above is this an INTERN?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                        MsgBox($"NEW EMPLOYEE ADDED{Environment.NewLine}ID NUMBER: {id}{Environment.NewLine}First Name: {afn.Text}{Environment.NewLine}Middle Name: {amn.Text}{Environment.NewLine}Last Name: {aln.Text}{Environment.NewLine}Birthday: {inputBirthday.ToString("yyyy-MM-dd")}{Environment.NewLine}Age: {aage.Value}{Environment.NewLine}Department: {adept.SelectedItem.ToString()}")
                    Else
                        MsgBox("Action cancelled")
                    End If
                Else
                    MsgBox($"NEW EMPLOYEE ADDED{Environment.NewLine}ID NUMBER: {id}{Environment.NewLine}First Name: {afn.Text}{Environment.NewLine}Middle Name: {amn.Text}{Environment.NewLine}Last Name: {aln.Text}{Environment.NewLine}Birthday: {inputBirthday.ToString("yyyy-MM-dd")}{Environment.NewLine}Age: {aage.Value}{Environment.NewLine}Department: {adept.SelectedItem.ToString()}")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                ' 用Finally确保连接关闭，即使报错也能执行
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
            End Try
        End Using
    End Using
End If

这样修改后，ExecuteScalar()就能拿到刚插入的员工ID，MessageBox里显示的就正常啦！

内容的提问来源于stack exchange，提问作者Yema Cake