检查数据表中的现有条目

本文关键字：数据表 检查 | 更新日期: 2023-09-27 17:50:35

private void txtBarcode_KeyUp(object sender, KeyEventArgs e)
        {
            try
            {
                string connString = "Server=192.168.1.100;Database=product;Uid=newuser;Pwd=password";
                MySqlConnection conn = new MySqlConnection(connString);
                    //cmd = conn.CreateCommand();
                    //cmd.CommandText = "Select * From tblindividualproduct";
                    if (e.KeyCode == Keys.Enter)
                    {
                        if (txtBarcode.Text == "")
                        {
                            MessageBox.Show("Please Fill the correct ProductID");
                        }
                        else
                        {
                            if (HasRecord("tblindividualproduct.ProductID", connString) == false)
                            {
                                string sql = "Select Iproduct.ProductId, prodInfo.Name,Iproduct.UpdatedPrice From product.tblproductinformation AS prodInfo INNER JOIN product.tblindividualproduct AS Iproduct ON prodInfo.Code = Iproduct.Code where Iproduct.ProductID = @idText";
                                using (var adapt = new MySqlDataAdapter(sql, conn))
                                using (var cmd = new MySqlCommandBuilder(adapt)) //Not sure what you need this for unless you are going to update the database later.
                                {
                                    adapt.SelectCommand.Parameters.AddWithValue("@idText", txtBarcode.Text);
                                    adapt.Fill(dt);
                                    dgItems.ReadOnly = true;
                                    dgItems.DataSource = dt;
                                }
                            }
                        }
                    }
               }
            catch (MySqlException ex)
            {
                // output the error to see what's going on
                MessageBox.Show(ex.Message);
            }
        }
        static public bool HasRecord(string ProductID, string connString)
        {
            //add try catches where required
            bool foundRecord = true;
            Int32 numRecords = 0;
            string sql =
                "Select count(Iproduct.ProductId, prodInfo.Name,Iproduct.UpdatedPrice) From product.tblproductinformation AS prodInfo INNER JOIN product.tblindividualproduct AS Iproduct ON prodInfo.Code = Iproduct.Code where Iproduct.ProductID = @idText";
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Add("@idText", SqlDbType.VarChar);
                cmd.Parameters["@idText"].Value = ProductID;
                try
                {
                    conn.Open();
                    numRecords = (Int32)cmd.ExecuteScalar();
                    if (numRecords == 0) foundRecord = false;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
            return foundRecord;
        }

我使用这个代码在我的数据集中添加项目，你能帮助我如何检查条目和不接受它，如果它会在数据集中有重复的条目吗?

编辑:我已经更新了我的代码，向您展示我当前的工作。

检查数据表中的现有条目

实际的方法是将您的ProductID作为数据库表tblindividualproduct中的PrimaryKey，然后您可以在代码中处理异常。

但是如果你没有权限修改数据库模式，你可以写一个查询来获取这些信息

Select count(*) From tblindividualproduct where ProductID = @ProductIdText

检查具有特定ID的行是否已经存在的函数，仅当该函数返回false时插入。connString将是您的数据库连接字符串。

static public bool HasRecord(string ProductID, string connString)
{
    //add try catches where required
    bool foundRecord = true;
    Int32 numRecords = 0;
    string sql =
        "Select count(*) From tblindividualproduct where ProductID = @ProductIdText";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@ProductIdText", SqlDbType.VarChar);
        cmd.Parameters["@ProductIdText"].Value = ProductID;
        try
        {
            conn.Open();
            numRecords = (Int32)cmd.ExecuteScalar();
            if(numRecords == 0) foundRecord = false;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return foundRecord;
}

然后像在函数中那样使用插入代码

if(HasRecord("1","Data Source=192.x.x.x;Initial Catalog=mydbName;UserID=user;Password=passw;provider=SQLOLEDB") == false)
{
      //your insert code
}
http://www.dotnetperls.com/sqlclient

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar (v = vs.110) . aspx

相关文章：