如何在visual basic 2012中防止相同的数据（OOP方法） [英] how to prevent the same data in visual basic 2012 (OOP method)

问题描述

我正在使用visual studio 2012和SQL server 2012来制作表单以在Windows窗体中注册数据。



我的表单的主键叫做NIS。



我想要的功能是：



- 我在数据库中插入数据后，当我尝试要插入具有相同主键值的另一个数据，然后会出现一个消息框告诉我数据库中已存在具有相同值的数据（以避免/规避错误）。



如何创建编码来执行此操作？



保存功能的编码：



表格：



保存按钮：

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:

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：

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 & "','" & 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

推荐答案

请，阅读我对这个问题的评论。



为避免重复，请编写存储过程：

Please, read my comment to the question.

To avoid duplicates, write stored procedure:

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

< br $>
请参阅：

创建过程（SQL） [ ^ ]

<小时ef =https://msdn.microsoft.com/en-us/library/ms188336.aspx> EXISTS（SQL） [ ^ ]



现在，在命令中调用它：

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

Now, call it in command:

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

如需了解更多信息，请参阅：

SqlCommand.Parameters属性 [ ^ ]

SqlParameterCollection.AddWithValue方法 [ ^ ]

这篇关于如何在visual basic 2012中防止相同的数据（OOP方法）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！