how to prevent bug/error when saving data with the same primary key on visual basic 2012?

Question

i'm using visual studio 2012 and microsoft SQL server 2012 to make a save function.

my coding is like this:

form:

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If Len(Trim(txt_nis.Text)) = 0 Or Len(Trim(txt_nisn.Text)) = 0 Or Len(Trim(txt_namasiswa.Text)) = 0 Or Len(Trim(cmb_kelaminsiswa.Text)) = 0 Or Len(Trim(txt_kotalahir.Text)) = 0 Or Len(Trim(DTP_siswa.Value)) = 0 Or Len(Trim(cmb_agamasiswa.Text)) = 0 Or Len(Trim(txt_beratsiswa.Text)) = 0 Or Len(Trim(txt_tinggisiswa.Text)) = 0 Then
            MsgBox("Data Belum Lengkap")
            Exit Sub
        End If
        ClassSiswa.Nis = txt_nis.Text
        ClassSiswa.Nisn = txt_nisn.Text
        ClassSiswa.Nama_Siswa = txt_namasiswa.Text
        ClassSiswa.Jenis_Kelamin = cmb_kelaminsiswa.Text
        ClassSiswa.Kota_Lahir = txt_kotalahir.Text
        ClassSiswa.Tanggal_Lahir = DTP_siswa.Value
        ClassSiswa.Agama = cmb_agamasiswa.Text
        ClassSiswa.Berat_Badan = txt_beratsiswa.Text
        ClassSiswa.Tinggi_Badan = txt_tinggisiswa.Text
        ClassSiswa.RekamData(ClassSiswa.opencon)
        MsgBox("Data siswa Berhasil Disimpan")
        datagridview()
    End Sub

class

Public Shared Sub RekamData(ByVal _Cn As SqlClient.SqlConnection)
        Dim sql As SqlClient.SqlCommand
        sql = New SqlClient.SqlCommand
        sql.Connection = _Cn
        sql.CommandType = CommandType.Text
        sql.CommandText = "Insert into siswa values('" & Nis & "','" & Nisn & "','" & Nama_Siswa & "','" & Jenis_Kelamin & "','" & Kota_Lahir & "','" & Tanggal_Lahir & "','" & Agama & "','" & Berat_Badan & "','" & Tinggi_Badan & "')"
        sql.ExecuteNonQuery()
    End Sub

SQL query:

Create Table siswa 
(
Nis varchar (40) primary key,
Nisn varchar (40),
Nama_Siswa varchar(40),
Jenis_Kelamin varchar (10),
Kota_Lahir varchar (10),
Tanggal_Lahir date,
Agama varchar (10),
Berat_Badan varchar (10),
Tinggi_Badan varchar (10)
)

Nis is primary key.

in the windows form datagridview there is a data that i already saved with Nis : 123, if i saved another data with the same Nis : 123 the program will stop and give me a error because there is already Nis with the value 123.

what i wanted to do is:

instead of show me the error, i want to make a coding that show me a messagebox "data already saved" so that the program won't stopped beacuse of error.

how do i make a coding to do that?

Are you sure you don't want to update the record when it already exists? — Tim3880, May 31 '15 at 04:37

Ed Gibbs · Accepted Answer · 2015-05-31T17:36:24.483

Your RekamData method will throw a SqlException with exception number 2627 if the primary key is duplicated. Catch it in your btnSave_Click method:

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    If Len(Trim(txt_nis.Text)) = 0 Or Len(Trim(txt_nisn.Text)) = 0 Or Len(Trim(txt_namasiswa.Text)) = 0 Or Len(Trim(cmb_kelaminsiswa.Text)) = 0 Or Len(Trim(txt_kotalahir.Text)) = 0 Or Len(Trim(DTP_siswa.Value)) = 0 Or Len(Trim(cmb_agamasiswa.Text)) = 0 Or Len(Trim(txt_beratsiswa.Text)) = 0 Or Len(Trim(txt_tinggisiswa.Text)) = 0 Then
        MsgBox("Data Belum Lengkap")
        Exit Sub
    End If
    ClassSiswa.Nis = txt_nis.Text
    ClassSiswa.Nisn = txt_nisn.Text
    ClassSiswa.Nama_Siswa = txt_namasiswa.Text
    ClassSiswa.Jenis_Kelamin = cmb_kelaminsiswa.Text
    ClassSiswa.Kota_Lahir = txt_kotalahir.Text
    ClassSiswa.Tanggal_Lahir = DTP_siswa.Value
    ClassSiswa.Agama = cmb_agamasiswa.Text
    ClassSiswa.Berat_Badan = txt_beratsiswa.Text
    ClassSiswa.Tinggi_Badan = txt_tinggisiswa.Text
    Try
        ClassSiswa.RekamData(ClassSiswa.opencon)
        datagridview()
    Catch ex As SqlException
        If ex.Number = 2627 Then
            MsgBox("Duplicate key")
        Else
            MsgBox("SQL exception " & ex.Message)
        End If
        Exit Sub
    End Try
    ' Sorry - I don't know what this means :)
    MsgBox("Data siswa Berhasil Disimpan")
End Sub

the `MsgBox("Data siswa Berhasil Disimpan")` is to confirm that all data has been saved. — Riki Salim, May 31 '15 at 16:18
i tried the coding and it worked. the only problem is when i finished saving the data, the messagebox `MsgBox("Data siswa Berhasil Disimpan")` doesn't show up to notice me that all the data has been saved. — Riki Salim, May 31 '15 at 16:57
Sorry - I made a mistake and commented out the `MsgBox` in my code. I've fixed it. I also made a change where the duplicate key is caught: if you have some other kind of SQL exception that should also be reported, and after the exception reporting I added an `Exit Sub`. — Ed Gibbs, May 31 '15 at 17:38

score 0 · Answer 2 · edited May 23 '17 at 12:30

You can use query which insert rows only if new Primary Key value doesn't exist in the table

INSERT INTO siswa VALUES 
(SELECT @Nis
, @Nisn
, @Nama_Siswa
, @Jenis_Kelamin
, @Kota_Lahir
, @Tanggal_Lahir
, @Agama
, @Berat_Badan
, @Tinggi_Badan 
WHERE NOT EXISTS (SELECT 1 FROM siswa WHERE Nis = @Nis))

I think checking for existed primary keys is job for the function which insert new row.
Based on method SqlCommand.ExecuteNonQuery which return number of the affected rows
You can change Sub RekamData to Function that return true of row inserted or false if not.

Public Shared Function RekamData(ByVal _Cn As SqlClient.SqlConnection) As Boolean
    Dim query As String = "here copy query above"
    Dim insertedQnt As Int32
    Using sqlcom As New SqlClient.SqlCommand(query, _Cn)
        sqlcom.CommandType = CommandType.Text
        With Sql.Parameters
            .AddWithValue("@Nis", Nis)
            .AddWithValue("@Nisn", Nisn)
            .AddWithValue("@Nama_Siswa", Nama_Siswa)
            .AddWithValue("@Jenis_Kelamin", Jenis_Kelamin)
            .AddWithValue("@Kota_Lahir", Kota_Lahir)
            .AddWithValue("@Tanggal_Lahir", Tanggal_Lahir)
            .AddWithValue("@Agama", Agama)
            .AddWithValue("@Berat_Badan", Berat_Badan)
            .AddWithValue("@Tinggi_Badan", Tinggi_Badan)
        End With
        insertedQnt = sqlcom.ExecuteNonQuery()
    End Using
    Return (insertedQnt > 0)
End Function

Then use it

'your code
If ClassSiswa.RekamData(ClassSiswa.opencon) = True Then
    MsgBox("Data siswa Berhasil Disimpan")
Else
    MsgBox("Value of Nis already exist")
End If

In the example I used SqlParameter for passing values to the database query.
I think this is good practice to use parameters.
For example parameters will help with problem of Sql Injection
How-does-sqlparameter-prevent-sql-injection

how to prevent bug/error when saving data with the same primary key on visual basic 2012?

2 Answers2