如何验证现有记录？ [英] How to validate existing records?

问题描述

嗨！



在我的页面中，我有一个下拉菜单，一个文本框，2个图像按钮（1个用于搜索，1个用于保存按钮）和网格视图。这里的场景是，在填写所需信息后，当我点击保存按钮时，系统将首先检查输入的信息是否存在于数据库中。如果是，将显示错误消息。如果没有，它将显示在网格视图中。但是当我尝试输入的信息存在时，没有任何反应。它进入下一个不应该的条件。也不会显示错误信息。



这里是代码：

Hi!

In my page, I have a drop down, a text box,2 image buttons(1 for search and 1 for save button) and a grid view. the scenario here is that after filling up the information needed, when I click the Save button, the system will check first if the information inputted were existing in the database . If yes, an error message will display. If not, it will display in the grid view. But when I try the information inputted exist, nothing happens. It goes to the next condition which should not be.Also, the error message do not display.

here's the code:

amicassaCon_repgen.Open();
        string amic = "";
        string amicc = "";
        SqlCommand get_no = new SqlCommand("SELECT ContractNo FROM MC.tblContracts WHERE ContractNo='"+company_code.SelectedValue+"' ", amicassaCon_repgen);
        SqlDataReader drno = get_no.ExecuteReader();
        while (drno.Read())
        {
            amic = drno[0].ToString();

        }
        drno.Close();

        SqlCommand get_nno = new SqlCommand("SELECT ContractPrjUnitDesc FROM MC.tblContracts Where ContractPrjUnitDesc='"+unit_desc.Text+"' ", amicassaCon_repgen);
        SqlDataReader drnno = get_nno.ExecuteReader();
        while (drnno.Read())
        {
            amicc = drnno[0].ToString();

        }
       
        if (company_code.SelectedValue == amic && unit_desc.Text == amicc)
        {
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
}
else
{
//my codes for inserting data and displaying to the grid view
}

推荐答案

为了达到这个目的，你需要使用IF EXISTS。

你可以直接在SQLCommand中使用，否则你可以编写一个存储过程。我会给你们两个代码作为例子你可以使用其中任何一个都是根据你的方便，但在这种情况下编写存储过程总是更好，也是最好的做法。



（1）使用SQLCommand：

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
		{
			//my codes for inserting data and displaying to the grid view
		}
	    else
		{
			ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
		}
 
        }
        drno.Close();

（2）使用StoredProcedure



首先在sql中编写存储过程并执行它

存储过程：

(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)
        {
           //my codes for inserting data and displaying to the grid view
        }
        else        
        {            
            ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error:                                                          Already had a Contract!');", true);
        } 

OR

你也可以写上面这样的代码

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)
        {
           //my codes for inserting data and displaying to the grid view
        }
        else 
       {            
            ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
        }  

您的方法容易出错。只需检查值的存在就不需要两次往返。 Bullet受害者是对的，您应该在SP中检查这个数据，然后在表中插入数据并返回错误，以防您已经存在可以检查的值。但是，让我们站在你的一边，建议你如何按照自己的方式去做。请参阅下面的伪非测试代码。

Your approach is error prone. There is no need to have two round trips for just checking the existance of values. Bullet Victim is right, you should check this in the SP before inserting the data in the table and return error in case if values already present which you can check. But lets be just on your side and advice you how to do it your way. Please see below the pseudo non tested code.

amicassaCon_repgen.Open();

string sql = "select COUNT(*) from (Select COUNT(*) TOTAL from MC.tblContracts
where MC.tblContracts WHERE ContractNo = '{0}' UNION ALL SELECT COUNT(*) FROM MC.tblContracts Where ContractPrjUnitDesc='{1}') A";

sql = string.Format(sql, company_code.SelectedValue, unit_desc.Text);

SqlCommand get_no = new SqlCommand(sql, amicassaCon_repgen);

int valuePresent = (int)get_no.ExecuteScalar();

regen.Close();

if (valuePresent > 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);

}
else
{
//my codes for inserting data and displaying to the grid view
}

这篇关于如何验证现有记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！