时间戳

首页 > 解决方案 > 如何检查数据库中是否存在数据并显示错误

c# - 如何检查数据库中是否存在数据并显示错误

问题描述

我试图出现一个错误，如果用户在同一日期提交数据，他们不应该被允许这样做。我不太确定如何做到这一点，但已经给它一个刺，但没有用。我不确定如何让读者工作。

public string conString = "***ConnectionString***";

    private void mileage_submit_btn_Click(object sender, EventArgs e)
    {
        if (location_comboBox.Text == "" || mileage_textbox.Text == "" || other_location_textBox.Text == "" || other_mileage_textBox.Text == "")
        {
            MessageBox.Show("Fill all required fields", ("Validation"), MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        else
        {
            SqlConnection con = new SqlConnection(conString);
            con.Open();
            if (con.State == System.Data.ConnectionState.Open)

            {
                string query = "INSERT INTO Mileage_Table(eb_number , Date, Location, Mileage, Other_location, Other_Miles, Total_Miles) values(@eb_number, @Date, @Location, @Mileage, @Other_location,@Other_Miles, @Total_Miles)";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.Add("@eb_number", SqlDbType.Text).Value = Username_Mileage_lbl.Text.ToString();
                cmd.Parameters.Add("@Location", SqlDbType.Text).Value = location_comboBox.Text.ToString();
                cmd.Parameters.Add("@Mileage", SqlDbType.Decimal).Value = Decimal.Parse(mileage_textbox.Text);
                cmd.Parameters.Add("@Other_Location", SqlDbType.Text).Value = other_location_textBox.Text.ToString();
                cmd.Parameters.Add("@Other_Miles", SqlDbType.Decimal).Value = Decimal.Parse(other_mileage_textBox.Text);
                cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Parse(Mileage_dateTimePicker.Text).ToString("dd-MM-yyyy");
                decimal b = Convert.ToDecimal(mileage_textbox.Text);
                decimal c = Convert.ToDecimal(other_mileage_textBox.Text);
                cmd.Parameters.Add("@Total_Miles", SqlDbType.Decimal).Value = Convert.ToString(b + c);


                using (var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows == true)
                        {
                            MessageBox.Show("Date = " + reader[7].ToString() + "Already Exists");
                        }
                    }
                }
                cmd.ExecuteNonQuery();
                decimal TotalMiles = b + c;
                MessageBox.Show(TotalMiles + " Miles has been Logged");
                con.Close();

标签： c#winforms

解决方案

假设您使用的是 Sql Server，您可以使用以下 SQL 方法来做到这一点

If NOT Exists(select from table where datecolumn = @myDateParam)
    Insert Into table (col list) values (@param list);

然后你调用这个 sql 使用

int retVal = cmd.ExecuteNonQuery( ..  ); // for plain sql number of affected rows returned. 0 - would mean nothing inserted
MessageBox.Show((retVal > 0 ? "Record Inserted" : "Record with specific date already existed"));

现在，如果您想使用 输出插入的值cmd.ExecuteReader，您需要添加到您的插入 SqlOUTPUT INSERTED子句https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?视图=sql-server-2017

推荐阅读