to achieve this you need to use IF EXISTS.
Either you can use directly in SQLCommand or else you can write a stored procedure.i will give both of the codes as an example you can use either of them according to your convinience but its always better to write a stored procedure in this type of situations and its a best practice also.
(1) Using SQLCommand:
amicassaCon_repgen.Open();
SqlCommand get_no = new SqlCommand("DECLARE @GetNo int IF EXISTS(SELECT ContractNo FROM MC.tblContracts WHERE ContractNo='"+company_code.SelectedValue+"' and ContractPrjunitdesc='"+unit_desc.Text+"') BEGIN SET @GetNo=0 END ELSE BEGIN SET @GetNo=1 END SELECT @DeptNo", amicassaCon_repgen);
SqlDataReader drno = get_no.ExecuteReader();
if (drno.Read())
{
if(drno[0].toString()!=0) \\contract not exists
{
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
}
}
drno.Close();
(2) Using StoredProcedure
first write stored procedure in sql and execute it
Stored Procedure:
CREATE Procedure SearchCOntract
@ContractNo int,
@ContractPrjunitdesc nvarchar(50),
@Getno int output
AS
BEGIN
IF EXISTS(SELECT ContractNo FROM MC.tblContracts WHERE ContractNo=@ContractNo and ContractPrjunitdesc=@ContractPrjunitdesc)
BEGIN
SET @GetNo=0
END
ELSE
BEGIN
SET @GetNo=1
END
END
Code
amicassaCon_repgen.Open();
SqlCommand get_no = new SqlCommand("SearchCOntract", amicassaCon_repgen);
get_no.CommandType = CommandType.StoredProcedure;
SqlParameter[] p=new SqlParameter[3];
p[0]= new SqlParameter("@ContractNo", company_code.SelectedValue);
p[1]= new SqlParameter("@ContractPrjunitdesc", unit_desc.Text);
p[2]= new SqlParameter("@Getno", SqlDbType.Int);
p[2].Direction = ParameterDirection.Output;
foreach (SqlParameter val in p)
{
get_no.Parameters.Add(val);
}
get_no.ExecuteNonQuery();
if ( int.Parse(get_no.Parameters["@Getno"].Value.ToString())!=0)
{
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
}
OR
you can also write above code like this
amicassaCon_repgen.Open();
SqlCommand get_no = new SqlCommand("SearchCOntract", amicassaCon_repgen);
get_no.CommandType = CommandType.StoredProcedure;
get_no.Parameters.AddWithValue("@ContractNo", company_code.SelectedValue);
get_no.Parameters.AddWithValue("@ContractPrjunitdesc", unit_desc.Text);
get_no.Parameters.AddWithValue("@Getno", SqlDbType.Int).Direction = ParameterDirection.Output;
get_no.ExecuteNonQuery();
if ( int.Parse(get_no.Parameters["@Getno"].Value.ToString())!=0)
{
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
}