public static bool ResetReferenceNumbers()
        {
            var dbe = new DBEngine();

            dao.Database db = dbe.OpenDatabase(global.MDBPath);

            try
            {
                try
                {
                    db.QueryDefs.Delete("qry_tempResetRefNo1");
                }
                catch { }

                try
                {
                    db.QueryDefs.Delete("qry_tempResetRefNo2");
                }
                catch { }

                string sql = @"SELECT Left([RefNo],InStr(InStr(1,[RefNo],'-')+1,[RefNo],'-')-1) AS code,
                         CLng(Right([RefNo],Len(Left([RefNo],InStr(InStr(1,[RefNo],'-')+1,[RefNo],'-')-1))-3)) AS [counter]
                        FROM tblSampling";
                db.CreateQueryDef("qry_tempResetRefNo1", sql);

                sql = @"SELECT qry_tempResetRefNo1.code,
                    Max(qry_tempResetRefNo1.counter) AS [max]
                    From qry_tempResetRefNo1
                    GROUP BY qry_tempResetRefNo1.code";
                db.CreateQueryDef("qry_tempResetRefNo2", sql);

                sql = "Delete * from tblRefCodeCounter";
                db.Execute(sql);

                sql = @"INSERT INTO tblRefCodeCounter ( GearRefCode, [Counter] )
                    SELECT qry_tempResetRefNo2.code, [Max]+1 AS countermax
                    FROM qry_tempResetRefNo2";
                db.Execute(sql);

                db.QueryDefs.Delete("qry_tempResetRefNo1");
                db.QueryDefs.Delete("qry_tempResetRefNo2");
                return(true);
            }
            catch (Exception ex)
            {
                Logger.LogError(ex);
                return(false);
            }
        }
Beispiel #2
0
        /// <summary>
        /// Helper function to create the inland grid table
        /// </summary>
        /// <param name="dbData"></param>
        /// <returns></returns>
        private static bool CreateInlandGridTable(dao.Database dbData)
        {
            var sql = @"Create table tblGrid25Inland
                            (grid_name TEXT(8),
                             zone TEXT(3),
                             x DOUBLE,
                             y DOUBLE)";

            dbData.Execute(sql);

            sql = "Create index idxPrimary on tblGrid25Inland (grid_name, zone) with PRIMARY";

            dbData.Execute(sql);

            return(true);
        }