You can try to change your SP with IF NOT EXIST:
CREATE PROCEDURE [dbo].[Usp_Insert_Data_In_Student_Detial_Table]
(
@ROLLNUMBER int,
@NAME varchar(50)
)
AS
IF NOT EXISTS (SELECT '#' FROM Student_Detail WHERE NAME = @NAME)
BEGIN
INSERT INTO Student_Detail
(
ROLLNUMBER,
NAME
)
VALUES
(
@ROLLNUMBER,
@NAME
)
select 1 as DataExist
END
ELSE
BEGIN
select 0 as DataExist
END
If result DataExistis 0, show message box That data already exist
OR You can separate the query between checking duplicate and save operation something like this:
string sQuery = string.Empty;
try
{
int iTotal = 0;
int iResultSave = 0;
sQuery = @"SELECT count(*) TOTAL FROM Student_Detail WHERE NAME = @NAME";
using (SqlConnection con = new SqlConnection(CommonConst.Conn_String_MS_SQL_SERVER))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sQuery, con))
{
cmd.Parameters.AddWithValue("@NAME", nameTextBox.Text);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr != null)
{
while (dr.Read())
{
iTotal = Convert.ToInt32(dr["TOTAL "]);
}
}
}
}
}
if(iTotal <= 0){
sQuery = @"INSERT INTO Student_Detail
(
ROLLNUMBER,
NAME
)
VALUES
(
@ROLLNUMBER,
@NAME
)";
using (SqlConnection con = new SqlConnection(CommonConst.Conn_String_MS_SQL_SERVER))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sQuery, con))
{
cmd.Parameters.AddWithValue("@ROLLNUMBER", rollNumberTextBox.Text);
cmd.Parameters.AddWithValue("@NAME", nameTextBox.Text);
iResultSave = cmd.ExecuteNonQuery();
}
}
}else{
MessageBox.Show("Data Already Exist");
}
} catch(Exception ex){
}