重复项被插入到Year表中.为什么? [英] Duplicates are being inserted into the year table. Why?

问题描述

此代码在Years表中添加了一个新的年份.如果在插入同一年之前应该显示错误消息?我的代码不起作用，重复项插入了Years表.我要去哪里错了?

提前谢谢！

This code adds a new year into the years table. If the same year was inserted before it should show an error message? My code doesn''t work and duplicates are inserted into the years table. Where am I going wrong?

Thanks in advance!

//checks for duplicate year
if (txt_add_year.Text != "")
{
    //gets years list
    DataAccess connection = new DataAccess(); //my ado class collection
    DataTable dt = new DataTable();
    string query = "SELECT * FROM years;";
    dt = connection.from_database(query);

    foreach(DataRow row in dt.Rows)
    {
        //if new year is in database
        if(txt_add_year.Text == row["year_name"])
        {
            //error massage
            MessageBox.Show("سال وارد شده موجود می باشد","خطا",MessageBoxButtons.OK,MessageBoxIcon.Stop);
            return;
        }
    }

    //adds new year
    query = "INSERT INTO years(year_name) VALUES('{0}');";
    query = string.Format(query, txt_add_year.Text);

    connection.to_database(query);

    //gets school list
    query = "SELECT * from school;";
    dt = null;
    dt = connection.from_database(query);

    //gets new year id
    // but in duplication state gets f***ing x year id
    query = "SELECT year_id FROM years;";
    DataTable dt2 = new DataTable();
    dt2 = connection.from_database(query);

    //updates schoolyear table
    //insert new rows into school year so each school has a row for new year
    foreach (DataRow row in dt.Rows)
    {
        query = "INSERT INTO schoolyear(school_id, year_id)" +
                "VALUES({0}, {1});";
        query = string.Format(query, row["school_id"], dt2.Rows[0]["year_id"]);
        connection.to_database(query);
    }
    this.Text = "سال جدید ثبت شد";
}

推荐答案

您正在使用字符串比较来比较年份值.除非您从字符串的左边缘和右边缘修剪出尾随空格，否则这将无法按预期工作.用户输入的字符串以及存储在数据库中的字符串.

我建议一个更好的选择是将年份作为int存储在数据库中.然后，您可以通过这种方式进行更稳定的比较.您还可以考虑重新设计数据库表，以防止插入重复的列值(通过使其成为唯一字段).

我错过了字符串到对象的比较(请参阅Sandeep的答案).但是，即使调用ToString，也需要注意与修剪有关的错误.

You are comparing the year values using string comparison. This will not work as you expect unless you trim off trailing space from the left and right edges of the strings. Both the string the user entered, and the strings stored in the database.

I would suggest that a better option would be to store the year as an int in the database. Then you can make a more stable comparison that way. You may also consider redesigning your database table so as to not allow insertions of duplicate column values (by making it a unique field).

I missed the string to object comparison (see Sandeep''s answer). But even if you call ToString, you need to be aware of trimming related errors.

您是否尝试过调试?

检查以下行:if(txt_add_year.Text == row["year_name"])
您看到它有什么问题吗?

您正在尝试在此处将文本与Row对象进行比较.而且它*将*总是失败，因为这里有一个参考检查，它会有所不同.因此，它将在导致重复的方法中向前发展.

您需要检查的是值是否相同.

只需进行以下更改就可以了:
if(txt_add_year.Text == row["year_name"].ToString())
当您执行ToString()调用时，然后就在进行字符串比较.

Did you try to debug at all?

Check this line: if(txt_add_year.Text == row["year_name"])
Do you see anything wrong with it?

You are trying to compare a text with a Row object here. And it *will* fail always as there is a reference check here which would be different. And thus, it would move ahead in the method leading to duplication.

What you need to check is if the values are same or not.

Just make the following change and it should do:
if(txt_add_year.Text == row["year_name"].ToString())
When you do the ToString() call, you are then doing string comparison.

根据表中的数据量，您可能会发现使用以下类似方法更快地代替了您的循环:

Depending on the amount of data in your table you might find it quicker to use something like the following in place of your loop:

string selectString = string.Format("year_name = {0}", txt_add_year.Text);
DataRow[] yearsFound = dt.Select(selectString);
if (yearsFound.Length > 0)
{
  // the year is already there.
}
else
{
  // it's not there, so add it.
}

这篇关于重复项被插入到Year表中.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！