1 Replies - 4548 Views - Last Post: 18 April 2011 - 11:26 AM Rate Topic: -----

#1 NotarySojac   User is offline

  • D.I.C Regular
  • member icon

Reputation: 53
  • View blog
  • Posts: 428
  • Joined: 30-September 10

Error: "Could Not Update; Currently Locked" access database

Posted 18 April 2011 - 10:53 AM

Edit:Hey, this post, I regret, lol. I think this was like... my first Insert method, and I'm switching to a more mature version that I'm using now. Odd error still.. But now fairly academic, and my code is so, so, so shabby I don't want to waste anyone's eyes on it.

Hey, I get this mysterious error on windows 98SE usually (I think only...) when I run a routine that parses data from a text file and puts it into an access database.

Here's my code for updating the database... It's very terrible, but it did the job (I thought) so I speed along to the next task, but it seems that my work around is causing duplicated records in the database (but that could actually be an unrelated bug).

// this loop is hit many times as I parse more and more of the file
// I do this to update the progress bar accordingly
for (int i = 0; i < mydataset.Tables[tblSalesLines].Rows.Count; i++)      // for every ROW
                            {
                                DataRow iRow = mydataset.Tables[tblSalesLines].Rows[i];   
                                for (int l = 1; l < columns.Count; l++)                               // for every COLUMN/field
                                {
                                    object iField = iRow[l];  // skip first column since it's autoIncremented
                                    insertCommand.Parameters[l - 1].Value = iField;  // Param is zero based
                                }
                                try
                                {
                                    insertCommand.ExecuteNonQuery();
                                }
                                catch (Exception exp)
                                {
                                    MessageBox.Show("oops: " + exp.Message + " \n attempting correction");

                                    #region close SQL CONNECTION
                                    insertCommand1.Connection.Close();
                                    insertCommand.Connection.Close();
                                    #endregion

                                    #region Open Connection
                                    insertCommand1.Connection.Open();
                                    insertCommand.Connection.Open();
                                    #endregion
                                    insertCommand.ExecuteNonQuery();

                                }
                            }
                            




                            for (int i = 0; i < mydataset.Tables[tblSales].Rows.Count; i++)      // for every ROW
                            {
                                DataRow iRow = mydataset.Tables[tblSales].Rows[i];
                                for (int l = 1; l < columns1.Count; l++)// for every COLUMN
                                {
                                    object iField = iRow[l];
                                    insertCommand1.Parameters[l - 1].Value = iField;
                                }
                                try
                                {
                                    insertCommand1.ExecuteNonQuery();
                                }
                                catch (Exception exp)
                                {
                                    // ditto above...
                                }

                            }

                            mydataset.Tables[tblSales].Clear();       // these iterate many times
                            mydataset.Tables[tblSalesLines].Clear();



I read the MS bug disclaimer on this problem and it said the error was caused:
When you try to execute a SQL statement that changes a record of a table that was edited earlier in the same transaction, you may receive the following error message: ...


I don't think that is happening in my code, but maybe it means the way I'm .Clear()ing the tables the way I do? Any help on this is appreciated.

This post has been edited by NotarySojac: 18 April 2011 - 11:24 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Error: "Could Not Update; Currently Locked" access database

#2 NotarySojac   User is offline

  • D.I.C Regular
  • member icon

Reputation: 53
  • View blog
  • Posts: 428
  • Joined: 30-September 10

Re: Error: "Could Not Update; Currently Locked" access database

Posted 18 April 2011 - 11:26 AM

Oops, i think I fixed this problem... I don't know why I was using that method, but I'm going with a call to the below algo that is made to work with my tables:

/// <summary>
        /// Uploads (inserts) everything in the DATASET under the specified TABLENAME, putting it into the DATABASE
        /// 
        /// This function skips the first item in the DATASET because they are assumed to NOT be parsed "P_Id" autoincrementers...
        /// (you know, because they get autocalculated by the database.)
        /// </summary>
        /// <param name="mydataset"></param>
        /// <param name="tableName"></param>
        /// <param name="columnNames"></param>
        /// <param name="colTypes"></param>
        private static void UploadToDbsClean(DataSet mydataset, string tableName, string pstrDB)
        {
            DataColumnCollection columns = mydataset.Tables[tableName].Columns;

            OleDbConnection myConnection = new OleDbConnection();
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
            //string pstrDB = @"c:\test.mdb";           // string pointing to database location
            OleDbCommand insertCommand = new OleDbCommand();
            insertCommand.Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + pstrDB);

            //  Parameterized Method

            #region SQL Parameterized statement
            string refstringz = "INSERT INTO " + tableName + "( " + columns[1].ToString();

            for (int l = 2; l < columns.Count; l++)                            // we're doing all except [0] by the end of it all...
            {
                refstringz += ", " + columns[l].ToString();
            }

            refstringz += ") VALUES (";

            refstringz += "?";

            for (int l = 2; l < columns.Count; l++)                               // we're skipping the first one since it's above (the ?) and skipping one because we forget the first column
            {

                refstringz += ", ?";
            }
            refstringz += ")";

            insertCommand.CommandText = refstringz;
            #endregion
            string SQLStatementSetup = refstringz;

            #region Parameter Section

            for (int i = 1; i < columns.Count; i++)  // for each column minus the first
            {
                string colName = columns[i].ColumnName;

                string iDataTypeOfColumn = columns[i].DataType.Name.ToLower();
                switch (iDataTypeOfColumn)
                {
                    case "autoincrement":
                        {
                            MessageBox.Show("You (the user) were never supposed to see this error message.  Something has gone slightly wrong.  Call for hlep!");
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Variant, 40, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "datetime":
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Date, 30, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "int32":
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Integer, 30, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "decimal":
                        {
                            // FIXME  CRAP!  What if this messes things up (see that I put size as one to make it obviouse if it will mess with currency...)
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Currency, 1, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "double":
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Double, 30, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "string":
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Variant, 200, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "yesno":
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Boolean, 200, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    case "boolean":
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Boolean, 200, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                    default:       // assume text...
                        {
                            insertCommand.Parameters.Add(new OleDbParameter(colName, OleDbType.Variant, 200, colName));      // UID, datatype, maxLength, DataSet.ColumnName
                            break;
                        }
                }
            }

            #endregion
            OleDbParameterCollection ParametersAreSetup = insertCommand.Parameters;
            

            #region Fill values Loop
            insertCommand.Connection.Open();
            for (int i = 0; i < mydataset.Tables[tableName].Rows.Count; i++)      // for every ROW
            {
                DataRow iRow = mydataset.Tables[tableName].Rows[i];

                // for every COLUMN
                // columns.Count should be greater than Parameters count by exactly one because we're supposed to skip the first column
                for (int l = 1; l < columns.Count; l++)
                {
                    object iField = iRow[l];
                    insertCommand.Parameters[l - 1].Value = iField;
                }
                try
                {
                    insertCommand.ExecuteNonQuery();
                }
                catch (OleDbException exp)
                {
                    if (exp.ErrorCode != -2147467259)
                        MessageBox.Show("Some Unexpected Error \n\n" + exp.Message);
                    //MessageBox.Show("Some Error \n\n" + exp.Message);

                    
                }//  -2147467259
                
            }
            insertCommand.Connection.Close();
            #endregion
        }



Edit: it was a dumb optimization attempt on my part after all (which, for the record, would have worked but the framework is bugged =(

This post has been edited by NotarySojac: 18 April 2011 - 01:07 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1