แจกโค้ดทำ AutoNumber ทำเลขที่เอกสาร VB.NET/VB2008 กับ Access2007

OleDbCommand Cmd;

OleDbDataReader Dr;
private void Button1_Click(System.Object sender, System.EventArgs e)
{
	GenOrderID();
}

public void GenOrderID()
{
	string sql = "Select Max(Right(id_staff,5)) as oid from tb_staff";
	Cmd = new OleDbCommand(sql, conn);
	ConnectDB();
	//MsgBox(Cmd.ExecuteScalar)
	//Dim newOid As Integer = CInt(Cmd.ExecuteScalar)
	Dr = Cmd.ExecuteReader;
	Dr.Read();
	if (!Dr.HasRows) {
		//ถ้าค่าที่ได้รับเป็นค่า Null
		//   MsgBox("ok")
		//MsgBox(Now.ToString("yy"))
		t_id.Text = "S" + DateAndTime.Now.ToString("yy") + "0000000001";
	} else {
		int newID = Convert.ToInt32(Dr.Item("oid"));
		newID += 1;
		t_id.Text = "S" + DateAndTime.Now.ToString("yy") + newID.ToString("0000000000");
	}

	//newOid = newOid + 1
	//txtOrderId.Text = newOid.ToString
	//Conn.Close()
}

Protected Sub GenOrderID()
        Dim dv As DataView

        Dim sql As String = "Select Max(Right(staff_id,4)) as staff_id from tb_staff"
        dv = ObjDb.GetDataSet(sql).Tables(0).DefaultView
        'dv.Table(tb_staff)
        If Not dv.Count > 0 Then
            'ถ้าค่าที่ได้รับเป็นค่า Null
            '   MsgBox("ok")
            'MsgBox(Now.ToString("yy"))
            txtstaff_id.Text = "S" & "001"
        Else
            Dim newID As Integer = CInt(dv.Equals("staff_id"))
            newID = newID + 1
            '  MsgBox(newID)
            txtstaff_id.Text = "S" & newID.ToString("000")
        End If

    End Sub

CREATE TABLE [MyTable](
	[ID] [int] IDENTITY(1,1) Primary Key Clustered,
        [MyCode] [nvarchar](50) NULL,
	[MyName] [nvarchar](50) NULL
)

INSERT INTO MyTable
(MyCode, MyName)
VALUES        ('CR' + SUBSTRING(CONVERT(NVARCHAR(4), YEAR(GETDATE()) + 543), 3, 2) + SUBSTRING(REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 103), '/', ''), 3, 2) 
                         + REPLICATE('0', 6 - LEN(IDENT_CURRENT('MyTable'))) + CONVERT(NVARCHAR(6), IDENT_CURRENT('MyTable')), 'Thaicreate')

// --> CR5605000001

DECLARE @CAPTURE  INT;
SELECT @CAPTURE=RequestNumber 	FROM [config] ;
IF (@CAPTURE IS NULL)  
BEGIN
	INSERT INTO [CONFIG]
	( 
		RequestNumber 		
	) 
	VALUES 
	( 
		0 
	) 
END ; 
UPDATE [CONFIG] SET RequestNumber= RequestNumber+1 ; 
SELECT RequestNumber FROM [config] ; 

    Public Sub AutoID()
        Call OpenDB()
        gStrSql = ""
        'gStrSql = "select max(ID) as AutoID from Tbl_Event"
        gStrSql = "select top 1 ID from Tbl_Event order by ID desc"
        Dim daID = New OleDbDataAdapter(gStrSql, Conn)
        Dim dsID = New DataSet
        daID.Fill(dsID, "Tbl_Event")
        yy = CStr(Now.Year + 543)
        mm = Now.Month.ToString("00")
        With dsID.Tables("Tbl_Event")
            If .Rows.Count <> 0 Then
                Dim eid, Lid, Mid, Rid As String
                eid = .Rows(0).Item("ID").ToString
                Lid = eid.Substring(0, 4)
                Mid = eid.Substring(4, 2)
                Rid = eid.Substring(6, 5)
                If Lid = yy Then
                    If Mid = mm Then
                        StrIDAcc = Lid & Mid & (CInt(Rid) + 1).ToString("00000")
                    Else
                        StrIDAcc = Lid & mm & "00001"
                    End If
                Else
                    StrIDAcc = yy & mm & "00001"
                End If
            Else
                StrIDAcc = yy & mm & "00001"
            End If
        End With
    End Sub

		$sql="select * from TblServices where ID like 'SERV"."%' order by ID desc";
		$re=mssql_query($sql) or die(error());
		$row=mssql_fetch_array($re);
		$id=$row["ID"];
		$date1=date("Y-m-d");
		$dd=substr($date1,8,2);
		$mm=substr($date1,5,2);
		$yy=substr($date1,0,4);
		 if($id==""){
				 $serv_id="SERV".$yy.$mm."0001";
		 }else{
				  $chk_id=substr($id,0,10);
				  $chk_date="SERV".$yy.$mm;
				  if($chk_id==$chk_date){
					  $id=substr($id,10,4);
					  $id=$id+1;
					  if(strlen($id)==1)$id="000".$id;
					  if(strlen($id)==2)$id="00".$id;
					  if(strlen($id)==3)$id="0".$id;
					 $serv_id="SERV".$yy.$mm.$id;
				  }else{
					  $serv_id="SERV".$yy.$mm."0001";
				  }
		 }

namespace TORServices
{  
public enum EnumConnstring
        public enum EnumConnectionType
        { ODBC, OLEDB, SqlClient }
     public class _DatabaseSetting
    {
        public string FileDatabase;
        public EnumConnectionType enumConnectionType;
        public EnumConnstring enumConnstring;
        public string PASS = "";
    }
public static class clsDatabaseServices
    {
        public static ArrayList DatagridToArray(DataGridView dgv, int column)
        {
            ArrayList r = new ArrayList();
            if (dgv.RowCount - 1 > 0)
            {
                for (int i = 0; i < dgv.RowCount - 1; i++)
                {
                    r.Add(dgv[i, column].Value);
                }
            }
            return r;
        }

        public static void TextIndexAndValue(TextBox txtIndex, TextBox txtValue, string _SQL, string _ConnectionString)
        {
            List<TORServices._DabaseSelected> lst = new List<TORServices._DabaseSelected>();
            lst = clsDatabaseServices.ReturnListIndex(_SQL, _ConnectionString, TORServices.EnumConnectionType.SqlClient);
            if (lst.Count > 0)
            {
                txtIndex.Text = lst[0]._Key.ToString();
                txtValue.Text = lst[0]._Value;

            }

        }
        public static System.Windows.Forms.ComboBox DatabaseInCombobox(string _SQL, string strConn, EnumConnectionType ConnectionType)
        {
            ComboBox cmb = new ComboBox();
            cmb.DataSource = GetTableForm(_SQL, strConn, ConnectionType);
            return cmb;
        }
        public static void GetValueBindingSource(BindingSource bs, string column, string index)
        {

            if (index.Length > 0)
            {
                bs.Position = bs.Find(column, index);
            }
        }
        public static string Connstring(string _file,EnumConnstring contype ,string pass = "4410210091") 
        {
            return "Dsn=MS Access Database;dbq=" + _file + ";defaultdir=" + System.IO.Path.GetDirectoryName(_file) + ";driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;pwd=" + pass + ";uid=admin"; 
            
        }
        public static System.Data.DataTable GetTableForm(string _SQL, string strConn, EnumConnectionType ConnectionType )

        {
            
            System.Data.DataSet ds = new System.Data.DataSet();
            switch (ConnectionType)
            {
                case EnumConnectionType.ODBC:
                    System.Data.Odbc.OdbcConnection conn1 = new System.Data.Odbc.OdbcConnection(strConn);
                    conn1.Open();
                    System.Data.Odbc.OdbcDataAdapter adapter1 = new System.Data.Odbc.OdbcDataAdapter(_SQL, conn1);
                    adapter1.Fill(ds);
                    break;
                case EnumConnectionType.OLEDB:
                    System.Data.OleDb.OleDbConnection conn2 = new  System.Data.OleDb.OleDbConnection(strConn);
                    conn2.Open();
                    System.Data.OleDb.OleDbDataAdapter adapter2 = new  System.Data.OleDb.OleDbDataAdapter(_SQL, conn2);
                    adapter2.Fill(ds);
                    break;
                case EnumConnectionType.SqlClient:
                    System.Data.SqlClient.SqlConnection conn3 = new  System.Data.SqlClient.SqlConnection(strConn);
                    conn3.Open();
                    System.Data.SqlClient.SqlDataAdapter adapter3 = new  System.Data.SqlClient.SqlDataAdapter(_SQL, conn3);
                    adapter3.Fill(ds);

                    break;
            }

            return ds.Tables[0];

        }
        public static string[] GetData(string field, string table, string strConn, EnumConnectionType ConnectionType )
        {
            DataTable dt = GetTableForm("Select " + field + " FROM " + table, strConn, ConnectionType);
            string[] str= new string[dt.Rows.Count];
            for (int i = 0;i<dt.Rows.Count;i++)
            {
            str[i] = dt.Rows[i][field].ToString();
            }
            return str;

        }
        public static DataRow GetDataRow(string sql ,string strConn, EnumConnectionType ConnectionType)
        {
            DataTable dt = GetTableForm(sql, strConn, ConnectionType);
            
            return dt.Rows[0];

        }

        public static System.Data.DataTable GetTableFormExcel(string _SQL, string FileExcel)
        {
            string _ConnectionString = @"Data Source=" + FileExcel + "; Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;";
            try
            {
                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(_ConnectionString);
                conn.Open();
                string strQuery = _SQL;
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
                System.Data.DataSet ds = new System.Data.DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch { return null; }
            
        }

        public static void DataGridViewOpenEditFile(System.Windows.Forms.DataGridView DGV, System.Windows.Forms.DataGridViewCellEventArgs e, int clnOpen, int clnEdit, string PathOpen, bool EditFile = false)
        {

            
                if (e.ColumnIndex == clnEdit)
                {
                    string s = TORServices.clsFile_Path.SelectAndSaveFile(PathOpen);

                    if (System.IO.File.Exists(PathOpen + "\\" + s))
                    {
                        DGV[clnOpen, e.RowIndex].Value = s;
                    }
                }
                else if (e.ColumnIndex == clnOpen)
                {
                    if (System.IO.File.Exists(PathOpen + "\\" + DGV[clnOpen, e.RowIndex].Value.ToString()))
                    {
                        TORServices.clsFile_Path.OpenFile(PathOpen + "\\" + DGV[clnOpen, e.RowIndex].Value.ToString(), EditFile);
                    }
                    else { System.Windows.Forms.MessageBox.Show("เกิดข้อผิดพลาด ไฟล์เสียหาย" + Environment.NewLine + "กรุณาตรวจสอบ"); }
                }
            
            
        }
    }
}

public static string NewCus()
       {
           string cus = TORServices.clsDatabaseServices.GetDataRow("SELECT MAX(cusID) AS maxcusID FROM tblCustomer", POS_byTOR.Properties.Settings.Default.DATA_POSConnectionString, TORServices.EnumConnectionType.OLEDB)[0].ToString();
               cus = (cus.Length == 0)?"CUS00001":string.Format("{0:CUS00000}", int.Parse(cus.Replace("CUS", "")) +1);
           return cus;
       }