Home > Firebird, MySQL, SQL Server 2000, Visual Basic .NET > SELECT SCOPE_IDENTITY(), SELECT LAST_INSERT_ID() dan RETURNING PK

SELECT SCOPE_IDENTITY(), SELECT LAST_INSERT_ID() dan RETURNING PK

February 27, 2011

Yuph kali ini kita akan membahas fungsi menarik (SELECT SCOPE_IDENTITY(), SELECT LAST_INSERT_ID() dan RETURNING PK) yang dimiliki oleh database2x terkenal seperti SQL Server, MySQL dan Firebird.

Dari nama fungsinya kita bisa menebak kegunaan dari fungsi diatas yaitu untuk mendapatkan nilai ID terakhir (biasanya bertipe angka) yang diinputkan.

Saya dan mungkin Anda biasanya dalam merancang tabel untuk selalu menambahkan field ID dengan attribut IDENTITY/AUTONUMBER dan sekaligus dijadikan sebagai PRIMARY KEY.

Tentunya hal ini kita lakukan untuk mempermudah dalam proses manipulasi data (edit dan delete).

Contoh beberapa tabel yang biasanya menggunakan field ID untuk PRIMARY KEY dan sekaligus diset dengan attribut IDENTITY/AUTONUMBER

Bagi yang males membaca referensi (termasuk sy :D) biasa menuliskan kode berikut untuk mengINSERTkan dan mendapatkan nilai ID yang terakhir, kita ambil contoh tabel provinsi.

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    ' langkah 1 - menambahkan data
    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1)"
    Using cmd As New SqlClient.SqlCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            .ExecuteNonQuery()
        End With
    End Using

    ' langkah 2 - mengambil id terakhir dg memanfaatkan fungsi MAX
    strSql = "SELECT MAX(id) FROM provinsi"
    Using cmd As New SqlClient.SqlCommand(strSql, conn)
        lastID = Convert.ToInt32(cmd.ExecuteScalar())
    End Using

    ' tampilkan ID terakhir
    MessageBox.Show("Last ID : " & lastID.ToString())
End Sub

Sekarang kita lihat bedanya jika memanfaatkan fungsi SELECT SCOPE_IDENTITY().

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1) SELECT SCOPE_IDENTITY()" ' ada tambahan SELECT SCOPE_IDENTITY()
    Using cmd As New SqlClient.SqlCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            lastID = Convert.ToInt32(.ExecuteScalar())
        End With
    End Using

    MessageBox.Show ("Last ID : " & lastID.ToString())
End Sub

Gimana lebih simple kan ?🙂

Oke sekarang kita lihat kode untuk MySQL(SELECT LAST_INSERT_ID) dan Firebird (RETURN KOLOM_PK).

' MySQL
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1); SELECT LAST_INSERT_ID()"
    Using cmd As New MySqlClient.MySqlCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            lastID = Convert.ToInt32(.ExecuteScalar())
        End With
    End Using

    MessageBox.Show ("Last ID : " & lastID.ToString())
End Sub

' Firebird
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim lastID As Long = 0

    strSql = "INSERT INTO provinsi (keterangan) VALUES (@1) RETURNING ID" ' ID -> kolom primary key
    Using cmd As New FirebirdClient.FbCommand(strSql, conn)
        With cmd
            .Parameters.Clear()
            .Parameters.AddWithValue("@1", "Riau")

            lastID = Convert.ToInt32(.ExecuteScalar())
        End With
    End Using

    MessageBox.Show ("Last ID : " & lastID.ToString())
End Sub

Untuk bisa mengakses database MySQL dan Firebird di Visual Basic .NET kita harus menginstall MySQL Connector/Net dan Firebird ADO.NET Data Provider.

Selamat mencoba🙂

  1. Hanif
    April 7, 2011 at 2:12 am

    Assalamu’alikum. Mas, saya mau tanya, selama ini saya buat aplikasi tapi password di database tidak pernah saya enskrip. hasilnya ketika di buka dengan mysql Yog atau di mysql front atau di gui tools mysql langsung bisa ketahuan user dan passwordnya. saya mencoba menggunakan MD5 untuk enskrip, tapi ketika login tidak bisa pake passwordnya ya?. misal password saya hanif dan enskripnya adalah 72e74f574535bdc82cf4b99f8fc064e1. ketika saya masukin hanif ga mau masuk, tapi ketika saya masukin 72e74f574535bdc82cf4b99f8fc064e1 hasil enskripannya bisa masuk.
    untuk contoh kode programnya ada di
    http://www.4shared.com/file/2OWBKa_a/pasword.html. kecil kok cm 5KB
    sebelumnya terima kasih.

  2. Hanif
    April 7, 2011 at 3:16 am

    Maaf mas, ternyata udah ketemu caranya. tadi bingung, ga ktm ktm caranya. saya utak atik udah bisa

    • April 7, 2011 at 4:37 am

      Iya om itu menggunakan fungsi PASSWORD yang sudah disediakan MySQL kan?

  3. yulie
    August 26, 2011 at 8:47 am

    maaf mas bisa share tentang cara mengoptimalkan database MySQL ga? kebetulan saya sedang bikin aplikasi dengan vb6 dan databasenya php myadmin, kemudian di databasenya banyak muncul overhead, padahal baru mencoba menginputkan beberapa data. kalo dari phpnya kan tinggal klik optimize database, bagaimana coding untuk optimize database kalau kita buat dari vb6? mohon pencerahannya.. terima kasih sebelumnya…..

    • August 30, 2011 at 12:25 pm

      Coba link ini om, untuk table diagnotistics.

      Contoh penggunaan :

      mysql.exe -u<USER_NAME> -p<USER_PASSWORD> -e "OPTIMIZE TABLE NAMA_TABLE"
      
  4. madhe
    November 3, 2011 at 2:02 am

    mas kalau ingin membuat format no urut dan saya mau ambil dari autonumber dan kita modif menjadi mis no “SL0001” dan seterusnya bagaimana yah, saya gunakan dengan sql server. thq mas

    • November 3, 2011 at 11:55 am

      Coba seperti ini om :

      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
          Dim noUrut As Integer = 5
          MessageBox.Show(String.Format("{0:SL000#}", noUrut))
      End Sub
      
      • madhe
        November 4, 2011 at 12:30 am

        Terima kasih mas, tapi kalau saya mau pakai dari no autonumber, jadi di tablenya ada no autonumber dan nourut (diambil dr autonumber dan sudah di modif), saya masih belum jelas, mohon pencerahannya. maaf nih sudah merepotkan. terima kasih sebelumnya

        • November 4, 2011 at 5:55 am

          Loh bukannya tinggal query biasa om

          SELECT MAX(field_autonumber) FROM tabel
          

          Hasil selectnya kan tinggal ditampung ke variabel noUrutnya.

          • madhe
            November 4, 2011 at 7:35 am

            terima kasih mas, kalau begitu saya dptkan nilai max lalu saya update lagi nourut (dr autonumber) ke tabelnya (CMIIW), atau ada cara lain mas, misalnya kalau saya update langsung di database dengan SP atau dengan trigger intinya saya ingin perubahannya dilakukan di databasenya mas, bukan dari coding vbnya.

            • November 4, 2011 at 8:01 am

              Emg struktur tabelnya gimana sih om ?

              • madhe
                November 5, 2011 at 4:17 am

                begini mas strukturnya

                CREATE TABLE [dbo].[MasterCustomer](
                	[IDCust] [int] IDENTITY(1,1) NOT NULL,
                	[Nama] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[NamaSI] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[TglLahir] [datetime] NOT NULL,
                	[TempatLahir] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[JenisKel] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[Alamat1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[Kota1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[Telp1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                	[Fax1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                	[Email1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                	[Alamat2] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                	[Kota2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                	[Telp2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                	[Fax2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
                ) ON [PRIMARY]
                

                nah sebenarnya idcust saya maunya formatnya seperti C0001, C0002 …. dst nah no ini di dapat dari IDCust yang autonumber cuman saya tambahkan kode C dan formatnya seperti itu .

                terima kasih mas.

                • November 5, 2011 at 6:19 am

                  Satu hal yang perlu diperhatikan om, field yg diset identity nilainya enggak bisa diedit.
                  Jadi klo om mau menformat cust idnya menjadi C0001 dst otomatis harus ditambahkan lagi field baru misal dg nama KodeCust, nah hasil format nilai dari field IDCust diupdate ke field KodeCust. Contoh :

                  Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
                      Dim custID As Integer = 0
                  
                      strSql = "INSERT INTO MasterCustomer (Nama, Alamat1) VALUES (@1, @2) SELECT SCOPE_IDENTITY()"
                      Using cmd As SqlCommand = conn.CreateCommand()
                          With cmd
                              .CommandText = strSql
                              .Parameters.AddWithValue("@1", "Joko")
                              .Parameters.AddWithValue("@2", "Jogja")
                              custID = Convert.ToInt32(.ExecuteScalar())
                  
                              strSql = "UPDATE MasterCustomer SET KodeCust = @1 WHERE IDCust = @2"
                              .CommandText = strSql
                              .Parameters.Clear()
                              .Parameters.AddWithValue("@1", String.Format("{0:C000#}", custID))
                              .Parameters.AddWithValue("@2", custID)
                              .ExecuteNonQuery()
                          End With
                      End Using
                  End Sub
                  
                  • madhe
                    November 7, 2011 at 12:20 am

                    Wah terima kasih banget mas, ini yang saya inginkan, mas satu lagi, kalau seandainya saya gunakan trigger apakah di perbolehkan, sekali lagi terima kasih mas, smoga allah membalas kebaikan mas dengan yang lain.

                    • November 8, 2011 at 8:38 am

                      Yg jelas bisa om, coba aja googling dg keyword “trigger+SELECT SCOPE_IDENTITY()”

  5. madhe
    November 12, 2011 at 1:46 am

    terima kasih banyak mas …

    • November 12, 2011 at 2:41 am

      Sama2x om

  6. ovy
    September 9, 2012 at 5:26 pm

    Ass, Om mau nanya ni om, Gimana cara Menyimpan Teks Pada RichTexBox ke dlm database mysql Agar Format Tulisannya Gak Berubah….???

  7. Hardy
    October 2, 2012 at 6:50 pm

    Malam Om Kamarudin… sy mau tanya bbrpa hal :
    1. Saya coba buat 5 event, yang dijalankan setiap hari (tengah malam)sekitar jam 01.00-01.30. Dan sy kasih selisih dengan tenggat waktu 3-4 menit per event tsb. Tapi kenapa yang mysql meng-eksekusi nya setiap pukul 04.30 ?? itu apa ya sebabnya?

    2. sy punya View dengan nama A, dan table B. Table B mempunyai field ‘tanggal’ dan ‘jumlah’. Pertanyaannya-> sy ingin membuat event yg bertujuan INSERT ke table B tsb. Field tanggal=NOW(), dan field jumlah=COUNT data dari View A. Sy dah coba tapi gagal terus. bagaimana caranya ya om.
    mohon pencerahan. Terima Kasih sebelumnya.

  8. February 19, 2013 at 12:50 am

    om saya ingin mengahasilakan tampilan seperti http://prntscr.com/t8lg6
    yang di kasi tanda merah (terlamat=pinjam-kembali), ada permasalahan di kolom terlambat isinya tidak seperti di gambar melainkan seperti ini 8929
    mohon pencerahanya.

    • February 19, 2013 at 9:09 am

      Klo di VB 6 sih tinggal gini om :

      Private Sub Command1_Click()
          Dim tglPinjam   As String
          Dim tglKembali  As String
          Dim terlambat As Integer
          
          tglPinjam = "2012-05-25"
          tglKembali = "2012-05-31"
          
          terlambat = DateDiff("d", tglPinjam, tglKembali)
          MsgBox "Terlambat : " & terlambat
      End Sub
      
      • February 19, 2013 at 6:06 pm

        kalau menggunakan Query gimana om,

        • February 20, 2013 at 4:37 pm

          Databasenya pake MySQL kan ?
          Nah MySQL punya fungsi DATEDIFF, coba di googling dulu.

  9. February 22, 2013 at 12:14 pm

    mas admin
    rikues leh ga ?
    saya udah puter puter di google bagaimana menghubungkan SQL server yg ada di Laptop A ke Laptop B yang dijadikan client melalui Wifi (adhoc) + ODBC
    susah juga
    udah utak atik Client network utility tetep aja connection failed
    saya menggunakan vb.net + SQL server 2000 developer edition di laptop A
    lalu sudah di BUILD menjadi EXE di Folder user> Public>Projectvbnet>bin>release>admin.exe

    yg ingin saya tanyakan kenapa jika Laptop B mengakses Admin.exe pada Laptop A
    muncul pesan error

    yg pertama kadang seperti ini ” ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ”
    yang kedua seperti pada pic ini

    lalu membuat ODBC di Laptop B tetep ga bisa terhubung // connection failed melulu

    pada vb.net nya saya sertakan modul global
    Module ModuleGlobal
    Public AppName As String = ” Secure Parking ”

    Public Connection As New Data.Odbc.OdbcConnection
    Public TryConnection As Data.Odbc.OdbcConnection
    Public Connect As Boolean

    Public Command As New Data.Odbc.OdbcCommand
    Public Adapter As New Data.Odbc.OdbcDataAdapter

    Public SQLStatement As String
    Public Const StrConnection As String = “dsn=parkir”

    Public DsnName As String

    Public Log As Boolean
    Public LogID As String
    Public LogName As String
    Public LogLevel As String
    Public Function ValidateString(ByVal Str As String) As String
    ValidateString = Replace(Str.ToUpper, “‘”, “`”)
    End Function
    Public Function ValidatePass(ByVal Str As String) As String
    ValidatePass = Replace(Str, “‘”, “`”)
    End Function
    End Module

    jika di laptop A sudah bisa terkoneksi dengan baik namun Laptop B sebagai user ke dua tidak bisa mengeksekusi Admin.exe di Folder yg telah disebutkan diatas
    mohon pencerahannya mas admin
    terimakasih

    • February 22, 2013 at 2:11 pm

      Klo memang seperti itu om masalahnya mendingan di test dulu koneksi jaringannya.

      1. Gunakan perintah ping untuk cek koneksi dari komputer B ke komputer A klo hasilnya Replay berarti lakukan pengecekan yg nomor 2
      2. Pada saat membuat Data Source Name pastikan bisa terkoneksi ke server yang dimaksud, untuk contoh hasil testingnya bisa om lihat disini.

      Klo dua poin di atas tidak ada yg bermasalah baru masuk ke kode program, selain itu kenapa harus via DSN segala kan bisa lasung via kode.

  10. Choirul Akmal
    August 29, 2013 at 9:49 am

    Ada masukkan nih untuk firebird 2.0 keatas, kalau ada yang perlu select urutan row tertentu misal menampilkan row ke 3 saja dari total keseluruhan 200 row pada tabel pegawai bisa gunakan fungsi “rows”
    contoh :
    select p.idpegawai, p.NIK, p.Nama, P.jabatan from pegawai p order by p.id rows 3 to 3
    atau bisa juga select p.id, p.NIK, p.Nama, P.jabatan from pegawai p order by p.id rows 3 to 10 jika mau menampilkan urutan ke 3 sampai 10 dari ID pegawai

    Sebelumnya di Firebird 1.5 tidak tersedia fungsi ini

    • September 3, 2013 at 9:59 am

      Oke om tq infonya, dan sebagai gantinya di FB 1.5 menggunakan perintah :

      SELECT FIRST m .. SKIP n
      
  1. No trackbacks yet.
Comments are closed.
%d bloggers like this: