Check record before inserting from Windows Form DataGridView using C# and VB.Net

Hi smile,

Refer below sample.

Namespaces

C#

using System.Data.SqlClient;

VB.Net

Imports System.Data.SqlClient

Code

C#

private void Save(object sender, EventArgs e)
{
    SqlConnection con = null;
    SqlCommand cmd = null;
    string constr = @"Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123";
    con = new SqlConnection(constr);
    cmd = new SqlCommand("SELECT Subjects FROM tblMarksTest WHERE AdmissionNo = @AdmissionNo AND Subjects = @Subjects", con);
    cmd.Parameters.AddWithValue("@AdmissionNo", txtAdmissionNo.Text);
    cmd.Parameters.AddWithValue("@Subjects", txtSubject.Text);
    con.Open();
    string subjectName = Convert.ToString(cmd.ExecuteScalar());
    if (subjectName != "English" && subjectName != "Math" && subjectName != "Science" && subjectName != "Hindi")
    {
        cmd = new SqlCommand("INSERT INTO tblMarksTest(AdmissionNo, Subjects,Max,Mark) VALUES(@AdmissionNo, @Subjects,@Max,@Mark)", con);
        cmd.Parameters.AddWithValue("@AdmissionNo", txtAdmissionNo.Text);
        cmd.Parameters.AddWithValue("@Subjects", txtSubject.Text);
        cmd.Parameters.AddWithValue("@Max", txtMax.Text);
        cmd.Parameters.AddWithValue("@Mark", txtMarks.Text);
        cmd.ExecuteNonQuery();
        con.Close();
    }
    else
    {
        MessageBox.Show("Admission No " + txtAdmissionNo.Text + "already has saved for this subjects");
    }
    cmd = new SqlCommand("SELECT * FROM tblMarksTest", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    this.dataGridView1.DataSource = dt;

}

private void Form1_Load(object sender, EventArgs e)
{
    string constr = @"Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123";
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("SELECT * FROM tblMarksTest", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    this.dataGridView1.DataSource = dt;
}

VB.Net

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim constr As String = "Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123"
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM tblMarksTest", con)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    Me.dataGridView1.DataSource = dt
End Sub

Private Sub Save(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
    Dim con As SqlConnection = Nothing
    Dim cmd As SqlCommand = Nothing
    Dim constr As String = "Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123"
    con = New SqlConnection(constr)
    cmd = New SqlCommand("SELECT Subjects FROM tblMarksTest WHERE AdmissionNo = @AdmissionNo AND Subjects = @Subjects", con)
    cmd.Parameters.AddWithValue("@AdmissionNo", txtAdmissionNo.Text)
    cmd.Parameters.AddWithValue("@Subjects", txtSubject.Text)
    con.Open()
    Dim subjectName As String = Convert.ToString(cmd.ExecuteScalar())

    If subjectName <> "English" AndAlso subjectName <> "Math" AndAlso subjectName <> "Science" AndAlso subjectName <> "Hindi" Then
        cmd = New SqlCommand("INSERT INTO tblMarksTest(AdmissionNo, Subjects,Max,Mark) VALUES(@AdmissionNo, @Subjects,@Max,@Mark)", con)
        cmd.Parameters.AddWithValue("@AdmissionNo", txtAdmissionNo.Text)
        cmd.Parameters.AddWithValue("@Subjects", txtSubject.Text)
        cmd.Parameters.AddWithValue("@Max", txtMax.Text)
        cmd.Parameters.AddWithValue("@Mark", txtMarks.Text)
        cmd.ExecuteNonQuery()
        con.Close()
    Else
        MessageBox.Show("Admission No " & txtAdmissionNo.Text & "already has saved for this subjects")
    End If

    cmd = New SqlCommand("SELECT * FROM tblMarksTest", con)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    Me.dataGridView1.DataSource = dt
End Sub

Screenshot