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);
}
}
/// <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);
}