Click here to Skip to main content
15,848,451 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm using visual studio 2012 and SQL server 2012 to make form to register data in windows form.

the primary key for my form called NIS.

the function i want is:

- after i insert the data in the database, when i tried to insert another data with the same primary key value then a messagebox will appear to tell me that "there is already a data with the same value in database" (to avoid/evade bug).

how do i create a coding to do that?

the coding for save function:

form:

save button:
VB
Private Sub btnSimpan_Click(sender As Object, e As EventArgs) Handles btnSimpan.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_tinggi_badan.Text)) = 0 Or Len(Trim(cmb_ket_ayah.Text)) = 0 Or Len(Trim(txt_namaayah.Text)) = 0 Or Len(Trim(DTP_ayah.Text)) = 0 Or Len(Trim(txt_kotalahirayah.Text)) = 0 Or Len(Trim(cmb_agamaayah.Text)) = 0 Or Len(Trim(txt_ijazahayah.Text)) = 0 Or Len(Trim(txt_pekerjaanayah.Text)) = 0 Or Len(Trim(txt_pendapatanayah.Text)) = 0 Or Len(Trim(txt_alamatayah.Text)) = 0 Or Len(Trim(cmb_ket_ibu.Text)) = 0 Or Len(Trim(txt_namaibu.Text)) = 0 Or Len(Trim(DTP_ibu.Text)) = 0 Or Len(Trim(txt_kotalahiribu.Text)) = 0 Or Len(Trim(cmb_agamaibu.Text)) = 0 Or Len(Trim(txt_ijazahibu.Text)) = 0 Or Len(Trim(txt_pekerjaanibu.Text)) = 0 Or Len(Trim(txt_pendapatanibu.Text)) = 0 Or Len(Trim(txt_alamatibu.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_tinggi_badan.Text

       ClassSiswa.Ket_Ayah = cmb_ket_ayah.Text
       ClassSiswa.Nama_Ayah = txt_namaayah.Text
       ClassSiswa.Tanggal_Lahir_Ayah = DTP_ayah.Value
       ClassSiswa.Kota_Ayah = txt_kotalahirayah.Text
       ClassSiswa.Agama_Ayah = cmb_agamaayah.Text
       ClassSiswa.Ijazah_Ayah = txt_ijazahayah.Text
       ClassSiswa.Pekerjaan_Ayah = txt_pekerjaanayah.Text
       ClassSiswa.Pendapatan_Ayah = txt_pendapatanayah.Text
       ClassSiswa.Alamat_Ayah = txt_alamatayah.Text

       ClassSiswa.Ket_Ibu = cmb_ket_ibu.Text
       ClassSiswa.Nama_Ibu = txt_namaibu.Text
       ClassSiswa.Tanggal_Lahir_Ibu = DTP_ibu.Value
       ClassSiswa.Kota_Ibu = txt_kotalahiribu.Text
       ClassSiswa.Agama_Ibu = cmb_agamaibu.Text
       ClassSiswa.Ijazah_Ibu = txt_ijazahibu.Text
       ClassSiswa.Pekerjaan_Ibu = txt_pekerjaanibu.Text
       ClassSiswa.Pendapatan_Ibu = txt_pendapatanibu.Text
       ClassSiswa.Alamat_Ibu = txt_alamatibu.Text

       ClassSiswa.Ket_Wali = cmb_ket_wali.Text
       ClassSiswa.Nama_Wali = txt_namawali.Text
       ClassSiswa.Kelamin_Wali = cmb_kelaminwali.Text
       ClassSiswa.Tanggal_Lahir_Wali = DTP_wali.Value
       ClassSiswa.Kota_Wali = txt_kotalahirwali.Text
       ClassSiswa.Agama_Wali = cmb_agamawali.Text
       ClassSiswa.Ijazah_Wali = txt_ijazahwali.Text
       ClassSiswa.Pekerjaan_Wali = txt_pekerjaanwali.Text
       ClassSiswa.Pendapatan_Wali = txt_pendapatanwali.Text
       ClassSiswa.Alamat_Wali = txt_alamatwali.Text

       ClassSiswa.RekamData(ClassSiswa.opencon)
       MsgBox("Data siswa Berhasil Disimpan")
       datagridview()
       Call kosongkan()

   End Sub



class:
VB
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 & "','" & Ket_Ayah & "','" & Nama_Ayah & "','" & Tanggal_Lahir_Ayah & "','" & Kota_Ayah & "','" & Agama_Ayah & "','" & Ijazah_Ayah & "','" & Pekerjaan_Ayah & "','" & Pendapatan_Ayah & "','" & Alamat_Ayah & "','" & Ket_Ibu & "','" & Nama_Ibu & "','" & Tanggal_Lahir_Ibu & "','" & Kota_Ibu & "','" & Agama_Ibu & "','" & Ijazah_Ibu & "','" & Pekerjaan_Ibu & "','" & Pendapatan_Ibu & "','" & Alamat_Ibu & "','" & Ket_Wali & "','" & Nama_Wali & "','" & Kelamin_Wali & "','" & Tanggal_Lahir_Wali & "','" & Kota_Wali & "','" & Agama_Wali & "','" & Ijazah_Wali & "','" & Pekerjaan_Wali & "','" & Pendapatan_Wali & "','" & Alamat_Wali & "')"
        sql.ExecuteNonQuery()
    End Sub
Posted
Updated 17-May-15 1:00am
v2
Comments
Maciej Los 17-May-15 6:47am    
When i answered your previous question i recommended you to use stored procedures to avoid SQL injection attacks. Why don't you want to hear good advices? Why???

1 solution

Please, read my comment to the question.

To avoid duplicates, write stored procedure:
SQL
USE DatabaseName;
GO;
CREATE PROCEDURE InsertMyData
    @nis INT, @nisn INT, ...
AS 
BEGIN

    SET NOCOUNT ON;
    IF NOT EXISTS(SELECT * FROM TableName WHERE Nis=@nis AND Nisn=@nisn)
    BEGIN
        INSERT INTO TableName (Nis, Nisn, ...)
        VALUES(@nis, @nisn, ...)
    END    

END


Please see:
CREATE PROCEDURE (SQL)[^]
EXISTS (SQL)[^]

Now, call it in command:
VB
Public Shared Sub RekamData(ByVal _Cn As SqlClient.SqlConnection)
        Dim sql As SqlClient.SqlCommand
        sql = New SqlClient.SqlCommand
        sql.Connection = _Cn
        sql.CommandType = CommandType.StoredProcedure
        sql.CommandText = "InsertMyData"
        sql.Parameters.AddWithValue("Nis", Nis)
        sql.Parameters.AddWithValue("Nisn", Nisn)
        'to do: set another parameters
        sql.ExecuteNonQuery()
    End Sub


For further information, please see:
SqlCommand.Parameters Property [^]
SqlParameterCollection.AddWithValue Method [^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900