保存前如何检查人员是否存在 [英] how to check if staffid exist before saving

问题描述

我有一张存储员工记录的表格.我希望表单将sql db检入到
查看职员ID是否存在，并显示职员ID已存在"而不是
当保存按钮为
时，出现常规错误消息违反主要约束...." 点击.我试过了，但它只检查表中的第一行，但是否有staffid
在随后的行中，它显示正常的错误消息.

请帮帮我.

表格后面的代码:

 私有  void  btnSave_Click(对象发​​件人，EventArgs e)
        {

            SqlConnection conn = 新 SqlConnection(ConfigurationSettings.AppSettings [" ])；
            conn.ConnectionString = " ;
            conn.Open();
            
            // 保存数据
            尝试
            {
                如果(！validateScreen())
                    返回；
                clsStaffpersonal person = 新 clsStaffpersonal();
                             
                SqlCommand cmd = 新 SqlCommand(" ，conn)；

                SqlDataReader dr = cmd.ExecuteReader();

            如果(dr.Read())
            {
                如果(此 .txtStaffid.Text！= dr ["  staffid"].ToString())
                {
                                    
                变量
                _with1 = person;
                _with1.ID = 此 .txtStaffid.Text；
                _with1.myPicture = lblPicture.Text;
                _with1.entitle = cboTitle.Text;
                _with1.Sname = txtSurname.Text;
                _with1.Mname = txtMiddlename.Text;
                _with1.Fname = txtFirstname.Text;
                
                _with1.saveRegister();

                其他
                {
                    MessageBox.Show(" )；
                    返回；
                }
            }
            }
            捕获(例外)
            {
                MessageBox.Show(ex.Message);
            }
        } 

班级代码:

  public   void  saveRegister()
       {
           // 将数据保存在内存中


           SqlCommand cmd = 新 SqlCommand();
           字符串 dat = 空;
           dat = dateofbirth.Year + "  + dateofbirth.Month +  -" + dateofbirth.Day;
           字符串 sqlQuery = 空；
           sqlQuery = "  + staffid + "  +标题+ "  +姓氏+ " "  +名字+ " '，'" +性别+  '，'" + dat + "  + age +  '，'" +国籍+   +宗教+ " "  '，' +配偶名称+ "  +儿童人数+ " "  +住所地址+ " '，' +邮政地址+ "  +联系人号码+  '，' + contactnumber2 +  '，'" +电子邮件+  '，'" + nameofnok +  '，'" + nokcontactnumber + "  + idtype + " '，'" + idnumber +  '， '" + idexpirydate +  '，' +图片+  ')";
           //  sqlQuery =插入tblstaffpersonaldetails值('" + client_id +'，dbo.ProperCase('" +姓氏+')，'" +其他名称+'，'" +性别+'，'" + dat +'，" +年龄+'，'" +国籍+'，""+住址+ '，'" +邮政地址+'，'" + contact_number +'，'" + marital_status +'，'" +配偶名称+'，'" +电子邮件+'，'" +职业+' ，'"+ typeof_id +"'，'"+ id_number +"'，'"+ id_expirydate +"'，'"+备注+"'，'"+图片+"'，'"+ active_status +"')"; 


           cmd.Connection = conn;
           cmd.CommandText = sqlQuery;
           cmd.CommandType = System.Data.CommandType.Text;
           conn.Open();
           cmd.ExecuteNonQuery();
           cmd.Dispose();
           conn.Close();
       } 

解决方案

在btnSave_Click中，如下所示进行修改

 ...
SqlCommand cmd = 新 SqlCommand(字符串 .Format(" 选择*来自tblstaffpersonaldetails，其中StaffId ='{0}'"，如果(dr.HasRows)
{
    //  ID已存在
}
其他
{
    //  ID不存在.这样您就可以创建一个新记录.
} 

如果解决方案有效，请单击.

最好的
Kiran

您应该通过存储过程进行插入，并传递参数，而不是在代码中构造sql.

一种执行此操作的方法是，将过程包装在try/catch中-存储过程中的事务，如果该过程失败，则返回一个可以检查的值，然后采取适当的操作:

 - 包装在try catch中以返回成功/失败.
开始尝试

- 包装交易.
开始  tran 

- 添加新记录.
插入 插入 表(Field1，Field2，栏位3)
值( @ Param1 ， @ Param2 ，  @ Param3 )

- 提交交易.
提交  tran 

- 如果我们到达这里，那就成功了.
选择  1  [结果]

结束尝试
开始捕获

- 交易失败?
如果  @@ trancount >  0 
开始
- 撤消.
回滚
结束

- 通知应用失败.
选择  0  [结果]

结束捕获

另一种方法可能是通过工作人员身份并使用以下方法进行测试:

 如果(不 存在(选择 顶部  1   1  来自 表  @ StaffID ))
开始
插入 插入 表(字段1，字段2，栏位3)
值( @ Param1 ， @ Param2 ，  @ Param3 )
结束 

使用或改编这两种方法以满足您的需要，但是您当然应该考虑将所有内容包装到存储过程中，然后调用它，而不是在代码中构造一个sql字符串.

I have a form which stores staff records. I want the form to check in sql db to
see if a staff id exists and display "The Staff ID Already Exist" instead of the
normal error message "violation of primary constraint ...." when the save button is
clicked. I tried this but it only checks for the first row in the table but if the staffid
is in the subsequent rows it displays the normal error message.

Please help me out.

Code behind form:

private void btnSave_Click(object sender, EventArgs e)
        {

            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            conn.ConnectionString = "Data Source=MICKY-PC;Initial Catalog=SMS;User ID=sa;Password=mike";
            conn.Open();
            
            //save data
            try
            {
                if (!validateScreen())
                    return;
                clsStaffpersonal person = new clsStaffpersonal();
                             
                SqlCommand cmd = new SqlCommand("SELECT * FROM tblstaffpersonaldetails", conn);

                SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                if (this.txtStaffid.Text != dr["staffid"].ToString())
                {
                                    
                var
                _with1 = person;
                _with1.ID = this.txtStaffid.Text;
                _with1.myPicture = lblPicture.Text;
                _with1.entitle = cboTitle.Text;
                _with1.Sname = txtSurname.Text;
                _with1.Mname = txtMiddlename.Text;
                _with1.Fname = txtFirstname.Text;
                
                _with1.saveRegister();

                else
                {
                    MessageBox.Show("The Staff ID Already Exist");
                    return;
                }
            }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Code in class:

public void saveRegister()
       {
           //saves the data in memory


           SqlCommand cmd = new SqlCommand();
           string dat = null;
           dat = dateofbirth.Year + "-" + dateofbirth.Month + "-" + dateofbirth.Day;
           string sqlQuery = null;
           sqlQuery = "Insert into tblstaffpersonaldetails values('" + staffid + "','" + title + "','" + surname + "','" + middlename + "','" + firstname + "','" + gender + "','" + dat + "','" + age + "','" + nationality + "','" + religion + "','" + maritalstatus + "','" + spousename + "','" + numberofchildren + "','" + emergencycontactnumber + "','" + residentialaddress + "','" + postaladdress + "','" + contactnumber + "','" + contactnumber2 + "','" + email + "','" + nameofnok + "','" + nokcontactnumber + "','" + idtype + "','" + idnumber + "','" + idexpirydate + "','" + picture + "')";
           // sqlQuery = "Insert into tblstaffpersonaldetails values('" + client_id + "',dbo.ProperCase('" + surname + "'),'" + othername + "','" + gender + "','" + dat + "','" + age + "','" + nationality + "','" + residential_address + "','" + postal_address + "','" + contact_number + "','" + marital_status + "','" + spouse_name + "','" + email + "','" + occupation + "','" + typeof_id + "','" + id_number + "','" + id_expirydate + "','" + remarks + "','" + picture + "','" + active_status + "')";


           cmd.Connection = conn;
           cmd.CommandText = sqlQuery;
           cmd.CommandType = System.Data.CommandType.Text;
           conn.Open();
           cmd.ExecuteNonQuery();
           cmd.Dispose();
           conn.Close();
       }

解决方案

In the btnSave_Click please modify as shown below

...
SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM tblstaffpersonaldetails WHERE StaffId='{0}'", this.txtStaffid.Text), conn);
SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)
{
    // Id already present
}
else
{
    // Id is not present. So you can create a new record.
}

If the solution works please click like.

All the Best
Kiran

You should do the insert through a stored procedure, passing parameters rather than constructing sql in code.

One way (of many) to do this is to wrap the process in a try/catch - transaction inside a stored procedure which, if it fails, returns a value that you can inspect and then take the appropriate action:

-- Wrap in try catch to return success/failure.
begin try

	-- Wrap in a transaction.
	begin tran

		-- Add new record.
		insert into Table (Field1, Field2, Field3)
		values (@Param1, @Param2, @Param3)

	-- Commit the transaction.
	commit tran

	-- If we get here it succeeded.
	select 1 [Result]

end try
begin catch

	-- Transaction failed?
	if @@trancount > 0
	begin
		-- Undo.
		rollback
	end

	-- Let the app know that it failed.
	select 0 [Result]

end catch

Another way might be to pass the staffid and test that with:

if (not exists(select top 1 1 from Table where StaffID = @StaffID))
begin
	insert into Table (Field1, Field2, Field3)
	values (@Param1, @Param2, @Param3)
end

Use or adapt either of these methods to suit your need but you should certainly consider wrapping everything into a stored procedure and call that rather thna constructing a sql string in code.

这篇关于保存前如何检查人员是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！