how to prevent the same data in visual basic 2012 (OOP method)

Question

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 16-May-15 23:58pm

asasql

Updated 17-May-15 0:00am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2015-05-17T01:04:00

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 [^]