none
The Most confusing database update query problem ever. RRS feed

  • Question

  • Ok, quick background for the problem first.
    We have a simple document management system. Database does not store files (no blobs or filestream) rather than just tracks business data related to files.
    Application supports multiple databases and each db has their own document storage folders on disk.
    For example:
    1. I receive a file XYZ.doc
    2. I add doc details to databases DB1 and DB2 using app GUI.
    3. By other magic file is processed in our app and finally uploaded to correct storage foldes + flagged as 'Processed' in both databases.

    Loop in code below copies file to correct location and then sets 'Processed' flag on.
    Matchlist is a collection which tells from which databases currently processed file was found = In which databases we need to update 'Processed' flag and the db specific storage folder path.
    For example:
    Match1 - Destination: C:/DB1/Storage/ - Connection: Full connection string pointing to DB1
    Match2 - Destination: C:/DB2/Storage/ - Connection: Full connstr pointing to DB2

    So.. my problem.
    This all works perfectly in all my test environments.
    However, we came up with a client where 'Processed' flag update fails RANDOMLY.

    They upload 20 files and randomly on few files 'Processed' flag is still NULL.
    How is that possible?? No exception thrown and I have an extensive logging version on their site which always returns 1 for _cmdUpdate.ExecuteNonQuery() but DB keeps on displaying 'Processed' = null

    Client runs only ONE database so it can't be incorrect connection string.

    Environment:
    SQL Server Express 9.0.5000
    App compiled using .NET 4.0
    Uninstall Programs list on this weirdo server shows
    MS SQL Server 2005
    MS SQL Server 2008 Native Client 10.1.2531.0
    MS SQL Server Native Client 9.00.5000

    foreach (var _match in _matchList)
    {
        try
        {
           //In this loop I first move a file to a specific location and then update a flag in DB to indicate file was moved.
    
            string _location = Path.Combine(_match.ImportLocation, _sourceFile);
            if (File.Exists(_location) && !mInfo.OverwriteExisting)
               //File moving stuff here with exception handling 
            else
               //More file moving stuff with exception handling
            
            //Then database update
            using (SqlConnection _connUpd = new SqlConnection(_match.ConnectionString))
            {
                try
                {
                    _connUpd.Open();
    
                    SqlCommand _cmdDir = _connUpd.CreateCommand();    
                    _cmdDir.CommandText = "select ImporDir from t_Settings";    
                    string _importDir = _cmdDir.ExecuteScalar().ToString(); //This step works always fine
                   
                   
                      SqlCommand _cmdUpdate = _connUpd.CreateCommand();
                    _cmdUpdate.CommandText = "update t_Document set Processed= 1, ImportDir=@ImpDir, DocDate = GETDATE() where DocID = @DocId";
                    _cmdUpdate.Parameters.AddWithValue("@DocId", _match.DocumentID);
                    _cmdUpdate.Parameters.AddWithValue("@ImpDir", _importDir);
                    _cmdUpdate.ExecuteNonQuery();   //WFT happens here?? Returns always 1 but DB shows no update???? _connUpd.Close(); } catch (Exception exUpdate) { //Exception handling/logging - does not hit here } } } catch (Exception ex) { //Exception handling - Hasn't occured even once } }



    • Edited by JoopaNZ Thursday, November 29, 2012 10:35 PM
    Thursday, November 29, 2012 10:34 PM

Answers

  • We finally figured this out and it turned out to be embarrassingly classic problem.
    In some special cases users were able to leave same document row in GUI to edit mode while service on another machine uploaded actual file to the system.
    So GUI says "Not available" while service does upload and turns "Not Available" to "Available".
    User then uses GUI to SAVE current row, which overwrite all values in db, including changes made by service.

    I feel like an amateur now :(

    • Marked as answer by JoopaNZ Thursday, December 20, 2012 8:10 PM
    Thursday, December 20, 2012 8:10 PM

All replies

  • Are you able to set up a database profiler? Maybe there is other process reading and udpdating that table at the same time.

    Are the oher columns (Importir, DocDate) being updated correctly in those cases where it failes to update the Processed one?


    Juan Casanova http://jawsofdotnet.blogspot.com - My .Net babling blog

    Friday, November 30, 2012 7:22 AM
  • Probly the tree dont let you see the forest ?

    The update its been made in a different database of where you are looking for processed = 1 ?

    Follow with care _location and _match.ConnectionString

    When you say Update always return 1, are you sure, make it:

    int mustBeOne = _cmdUpdate.ExecuteNonQuery(); //check mustBeOne

    Must be something very simple.


    -

    Friday, November 30, 2012 11:49 AM
  • Hi,


    Try with the below code


    SqlConnection con = new SqlConnection(strConnection);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;

            cmd.CommandText ="update t_Document set Processed= 1, ImportDir=@ImpDir, DocDate = GETDATE() where DocID = @DocId";

    cmd.Parameters.AddWithValue("@DocId", _match.DocumentID);
    cmd.Parameters.AddWithValue("@ImpDir", _importDir);
    cmd.ExecuteNonQuery();

                                   

    up


    PS.Shakeer Hussain

    Saturday, December 15, 2012 4:33 PM
  • We finally figured this out and it turned out to be embarrassingly classic problem.
    In some special cases users were able to leave same document row in GUI to edit mode while service on another machine uploaded actual file to the system.
    So GUI says "Not available" while service does upload and turns "Not Available" to "Available".
    User then uses GUI to SAVE current row, which overwrite all values in db, including changes made by service.

    I feel like an amateur now :(

    • Marked as answer by JoopaNZ Thursday, December 20, 2012 8:10 PM
    Thursday, December 20, 2012 8:10 PM