VB.NET 連結Access下達Insert指令並取回自動編號Id

參考資料：Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT.


此篇單存只是做個紀錄，目前測試是有效的，可以在下達Insert指令後，透過SELECT @@IDENTITY去取得Access資料庫中的自動編號。

    '|DataDirectory|意思是您的Access資料庫必須將它放在專案中的App_Data資料夾底下
    Public strsqlCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|" & WebConfigurationManager.AppSettings("acc_dbname").ToString & ";Jet OLEDB:DataBase Password=" & WebConfigurationManager.AppSettings("acc_dbpwd").ToString & ";Persist Security Info=False"
    Public sqlCon As OleDbConnection 'Connection物件建立ASP.NET程式與資料庫間的連結
    Public E_SQL As Boolean 'SQL指令執行成功或失敗
    Public sql_enq As String = Nothing
    Public Sub ODB_Open()
        '假如資料庫目前以關閉,就開啟資料庫連結
        sqlCon = New OleDbConnection(strsqlCon)
        sqlCon.Open() '開啟資料庫連結
    End Sub

    Public Function ODB_E_insert_getID1(ByVal sqlstr As String, ByVal pv As String, ByVal sqlvalue_p() As String) As String
        Dim insert_cmd As OleDbCommand = Nothing, insert_return As String = "NULL"

        Dim sql_p() As String = Nothing
        sql_p = pv.Split(",") 'sql字串中的參數
        sql_enq = Nothing

        If (sql_p.Length - 1) <> (sqlvalue_p.Length - 1) Then
            E_SQL = False
            insert_return = "err_Lv1"
        Else
            Try
                insert_cmd = sqlCon.CreateCommand()
                Dim x_end As Integer = sql_p.Length - 1
                For x As Integer = 0 To x_end
                    If insert_cmd.Parameters.Contains(sql_p(x)) Then
                        If sqlvalue_p(x) = String.Empty Then
                            insert_cmd.Parameters(sql_p(x)).Value = DBNull.Value
                        Else
                            insert_cmd.Parameters(sql_p(x)).Value = sqlvalue_p(x)
                        End If

                    Else
                        If sqlvalue_p(x) = String.Empty Then
                            insert_cmd.Parameters.AddWithValue(sql_p(x), DBNull.Value) '讓ADO.NET自行判斷型別轉換
                        Else
                            insert_cmd.Parameters.AddWithValue(sql_p(x), sqlvalue_p(x)) '讓ADO.NET自行判斷型別轉換
                        End If
                    End If
                Next

                insert_cmd.CommandText = sqlstr

                Dim insert_enq As Integer = 0
                insert_enq = insert_cmd.ExecuteNonQuery()

                If insert_enq = 1 Then
                    insert_cmd.CommandText = "SELECT @@IDENTITY"
                    insert_return = CStr(insert_cmd.ExecuteScalar())
                    E_SQL = True
                Else
                    insert_return = "err_Lv3" '新增失敗
                End If
            Catch ex As Exception
                insert_return = "err_Lv2"
            End Try
        End If

        If Not insert_cmd Is Nothing Then
            insert_cmd.Dispose()
        End If

        Return insert_return
    End Function

    Public Sub ODB_Close()

        If sqlCon.State = ConnectionState.Open Then
            sqlCon.Close() '關閉資料庫連結
            sqlCon.Dispose()
        End If
    End Sub