locked
Duplicated rows after inserting new values on sql database. RRS feed

  • Question

  • User-352524747 posted

    I have a page that automatically gets the data by reading another webpage and update the database based on what data has changed.

    When my page runs db.execute code to insert new data i found sometimes they are duplicated. They are entered twice. Why is that happening?

    I have on my table a column that is for date and time values, entered upon update. Values that change for duplicated rows are seconds, milliseconds in the datetime field on my table.

    Friday, October 9, 2015 7:57 AM

Answers

  • User325035487 posted

    I solved this issue by doing this in sql statement

    var sql = "IF NOT EXISTS (SELECT 1 FROM TABLE WHERE COLUMNNAME = @0) INSERT INTO TABLE (COLUMNNAME,ANothercoLUMN) values (@0,@1);

    db.Execute(sql,value1,value2);

    hope its clear

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 9, 2015 10:20 AM

All replies

  • User-821857111 posted

    It's impossible to say fr sure without seeing your code and knowing exactly what causes it to execute, but it sounds most likely that whatever causes the code to execute is happening twice.

    Friday, October 9, 2015 8:15 AM
  • User325035487 posted

    I solved this issue by doing this in sql statement

    var sql = "IF NOT EXISTS (SELECT 1 FROM TABLE WHERE COLUMNNAME = @0) INSERT INTO TABLE (COLUMNNAME,ANothercoLUMN) values (@0,@1);

    db.Execute(sql,value1,value2);

    hope its clear

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 9, 2015 10:20 AM
  • User-352524747 posted
    @using System.Text.RegularExpressions;
    @using System.Globalization;
    @using HtmlAgilityPack
    @{
        var db = Database.Open((string)App.Database);
    
        var timeZone = TimeZoneInfo.FindSystemTimeZoneById("W. Europe Standard Time");
        var datetime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, timeZone);
        
        DateTime latestentry = db.QueryValue("SELECT TOP 1 Date FROM fxd ORDER BY Date DESC");
        var latestvalues = db.QuerySingle("SELECT TOP 1 * FROM fxd ORDER BY Date DESC");
        string nodata = string.Empty;
        string urlfromboa = ""; 
        string BOADate = string.Empty, BOADateInt = string.Empty, US = string.Empty, EU = string.Empty, GB = string.Empty, CH = string.Empty, XA = string.Empty, XG = string.Empty;
    
    if (latestentry.Day != datetime.Day && datetime.Hour > 11)
        {
            try
            {
                var doc = new HtmlDocument();
                doc.LoadHtml(new WebClient().DownloadString(urlfromboa));
                var table = doc.DocumentNode.Descendants("table")
                .Where(t => t.GetAttributeValue("class", "").Equals("tabcontent"))
                .First();
                var rows = table.Descendants("tr");
                var firstCellBOADate = rows.ElementAt(0).Descendants("td").ElementAt(0).InnerText;
                var thirdCellUS = rows.ElementAt(2).Descendants("td").ElementAt(2).InnerText;
                var thirdCellEU = rows.ElementAt(3).Descendants("td").ElementAt(2).InnerText;
                var thirdCellGB = rows.ElementAt(4).Descendants("td").ElementAt(2).InnerText;
                var thirdCellCH = rows.ElementAt(5).Descendants("td").ElementAt(2).InnerText;
                var thirdCellXA = rows.ElementAt(13).Descendants("td").ElementAt(2).InnerText;
                var thirdCellXG = rows.ElementAt(14).Descendants("td").ElementAt(2).InnerText;
    
                BOADate = Regex.Match(firstCellBOADate, @"\d{2}.\d{2}.\d{4}").Value;
                US = thirdCellUS;
                EU = thirdCellEU;
                GB = thirdCellGB;
                CH = thirdCellCH;
                XA = thirdCellXA;
                XG = thirdCellXG;
    
                BOADateInt = Regex.Match(firstCellBOADate, @"\d{2}.\d{2}.\d{4}").Value.Split('.')[0];
    
                var insertQuery = "INSERT INTO ForexData (Date, US, GB, CH, EU, XA, XG)" + "VALUES (@0, @1, @2, @3, @4, @5, @6)";
    
                if (latestentry.Day != BOADateInt.AsInt() && latestentry.Day != datetime.Day)
                {
                    db.Execute(insertQuery, datetime.DayOfWeek == DayOfWeek.Saturday ? DateTime.Now.AddDays(-1) : (datetime.DayOfWeek == DayOfWeek.Sunday ? DateTime.Now.AddDays(-2) : datetime), US, GB, CH, EU, XA, XG);
                }
    
            }
            catch (Exception)
            {
                nodata = "*";
            }
        }
    

    Thats the code i use to update the database.

    Friday, October 9, 2015 10:56 AM
  • User325035487 posted

    http://stackoverflow.com/questions/1175217/sql-server-if-not-exists-usage

    var insertQuery = "IF NOT EXISTS
            (
            SELECT 1
            FROM ForexData
            WHERE Date=@0 AND US=@1 AND GB =@2 AND CH=@3 EU=@4 AND XA=@5 AND XG=@6
            )
            INSERT INTO ForexData (Date, US, GB, CH, EU, XA, XG)" + ..

    Saturday, October 10, 2015 6:11 AM
  • User-352524747 posted

    I hope that SQL "IF NOT EXIST" will do the job.

    Thank you.

    Saturday, October 10, 2015 8:26 AM
  • User325035487 posted

    Oh. And by the way. The actual problem might lie in the code which call this code you posted above.

    Sunday, October 11, 2015 2:02 AM
  • User-352524747 posted
    This code is my default home page. What else could be?
    Sunday, October 11, 2015 3:41 AM