เรื่องการดึงฟิลด์ จากอีกตารางหนึ่งค่ะ คือว่า ในฟอร์มการเบิกค่ะ จะต้องมีการเลือกวัสดุ

Dim myCommand As OleCommand = Nothing 

 void Page_Load (object sender,EventArgs e)
	{
    
    }
    
    void btnSave_Click(Object sender , EventArgs e)        
	{
        SqlConnection objConn = new SqlConnection();

		String strConnString,strSQL;

        strConnString = "Server=WALAILAK-4A0863\\SQLEXPRESS;UID=stock;PASSWORD=1234;database=CEC_StockDB;Max Pool Size=400;Connect Timeout=600;";

        strSQL = "INSERT INTO Inven (InvenID,InvenName,priceperUnit,Quantity,DateBuy) " +
		" VALUES " +
        " ('" + this.txtInvenID.Text + "','" + this.txtInvenName.Text + "','" + this.txtpriceperUnit.Text + "','" + this.txtQuantity.Text + "','" + this.txtDateBuy.Text + "')";
     
        
        objConn.ConnectionString = strConnString;
		objConn.Open();
        SqlTransaction tr = objConn.BeginTransaction();
     

		this.pnlAdd.Visible = false;
	
		try
		{
            int x = new SqlCommand(strSQL, objConn, tr).ExecuteNonQuery();

            strSQL = " UPDATE Inven SET Quantity = Quantity-" + int.Parse(txtQuantity.Text) + " WHERE InvenID ='" + txtInvenID.Text + "' ";

            int x2 = new SqlCommand(strSQL, objConn, tr).ExecuteNonQuery();
            tr.Commit();
			this.lblStatus.Text = "บันทึกข้อมูลสำเร็จ";
            
			this.lblStatus.Visible = true;
           
		}
		catch (Exception ex)
		{
			this.lblStatus.Visible = true;
			this.lblStatus.Text = "ไม่สามารถบันทึกข้อมูลได้ ("+ ex.Message +")";
		}

		objConn.Close();
		objConn = null;

    }
    

void Page_Load(object sender,EventArgs e)
	{
    
    }
    
    void btnSave_Click(Object sender , EventArgs e)        
	{
        SqlConnection objConn = new SqlConnection();
		SqlCommand objCmd = new SqlCommand();
		String strConnString,strSQL;

        strConnString = "Server=WALAILAK-4A0863\\SQLEXPRESS;UID=stock;PASSWORD=1234;database=CEC_StockDB;Max Pool Size=400;Connect Timeout=600;";

        strSQL = "INSERT INTO Witdform(WitdID,ProjName,Amount) " +
		" VALUES " +" ('" + this.txtWitdID.Text + "','" + this.txtProjName.Text + "','" + this.txtAmount.Text + "' )";
        objConn.ConnectionString = strConnString;
        objConn.Open();
        objCmd.Connection = objConn;
        objCmd.CommandText = strSQL;
        objCmd.CommandType = CommandType.Text;


        this.pnlAdd.Visible = false;

        try
        {
            objCmd.ExecuteNonQuery();
            this.lblStatus.Text = "บันทึกข้อมูลสำเร็จ";
            this.lblStatus.Visible = true;
        }
        catch (Exception ex)
        {
            this.lblStatus.Visible = true;
            this.lblStatus.Text = "ไม่สามารถบันทึกข้อมูลได้ (" + ex.Message + ")";
        }

        objConn.Close();
        objConn = null;

    }

void Page_Load (object sender,EventArgs e)
{
   
   }
   
   void btnSave_Click(Object sender , EventArgs e)        
{
       SqlConnection objConn = new SqlConnection();

	String strConnString,strSQL;

       strConnString = "Server=WALAILAK-4A0863\\SQLEXPRESS;UID=stock;PASSWORD=1234;database=CEC_StockDB;Max Pool Size=400;Connect Timeout=600;";

       strSQL = "INSERT INTO Inven (InvenID,InvenName,priceperUnit,Quantity,DateBuy) " +
	" VALUES " +
       " ('" + this.txtInvenID.Text + "','" + this.txtInvenName.Text + "','" + this.txtpriceperUnit.Text + "','" + this.txtQuantity.Text + "','" + this.txtDateBuy.Text + "')";
    
       
       objConn.ConnectionString = strConnString;
	objConn.Open();
       SqlTransaction tr = objConn.BeginTransaction();
    

	this.pnlAdd.Visible = false;

	try
	{
           int x = new SqlCommand(strSQL, objConn, tr).ExecuteNonQuery();

           strSQL = " UPDATE Inven SET Quantity = Quantity-" + int.Parse(txtQuantity.Text) + " WHERE InvenID ='" + txtInvenID.Text + "' ";

           int x2 = new SqlCommand(strSQL, objConn, tr).ExecuteNonQuery();
           tr.Commit();
		this.lblStatus.Text = "บันทึกข้อมูลสำเร็จ";
           
		this.lblStatus.Visible = true;
          
	}
	catch (Exception ex)
	{
		this.lblStatus.Visible = true;
		this.lblStatus.Text = "ไม่สามารถบันทึกข้อมูลได้ ("+ ex.Message +")";
	}

	objConn.Close();
	objConn = null;

   }
   

<connectionStrings>
		<add name="ThaiCreateDbConnectionString" 
                  connectionString="Data Source=beproud\sqlexpress;Initial Catalog=test;Integrated Security=True"
                  providerName="System.Data.SqlClient"/>
</connectionStrings>

                 <pages>
			<controls>
				<add tagPrefix="Ajax" namespace="AjaxControlToolkit" assembly="AjaxControlToolkit"/>
				<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			</controls>
		</pages>

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            myAppLogic.CheckedTable(); // รันแครั้งเดียวเพื่อตรวจสอบตาราง ต่อๆไปก้อลบออกไม่ก้อ comment
            FillWithdrawDocGrid(GridViewDocumentList);
        }
    }

body
{
    background-color: White;
    font-family: Tahoma, Arial, MS Sans Serif;
    font-size: 12px;
    color: DarkSlateBlue;
    margin: 0px;
}
.WaterMarkText
{
    font-family: Tahoma, Arial, MS Sans Serif;
    font-size: 12px;
    color: LightGray;
}
.H1
{
    font-size: 22px;
    font-weight: bold;
    text-align: left;
}
.MyLinkDisabled, .MyLinkDisabled:link, .MyLinkDisabled:Active, .MyLinkDisabled:hover
{
    color: Gray;
    text-decoration: none;
    outline: 0 none;
    cursor: default;
}
.MyLink, .MyLink:link, .MyLink:Active
{
    color: Crimson;
    text-decoration: none;
    outline: 0 none;
}
.MyLink:hover
{
    color: Red;
    text-decoration: underline;
    outline: 0 none;
}

.WaterMarkText
{
    font-family: Tahoma, Arial, MS Sans Serif;
    font-size: 12px;
    color: LightGray;
}
.MyTextbox
{
    font-family: Verdana, Arial, Helvetica, sans-serif;
    font-size: 9px;
    font-style: normal;
    height: 18px;
    border: none;
    padding: 2px;
    margin: 2px;
    background-color: WhiteSmoke;
}
.MyTextboxDisabled
{
    font-family: Verdana, Arial, Helvetica, sans-serif;
    font-size: 9px;
    font-style: normal;
    height: 18px;
    background-color: DarkGray;
    padding: 2px;
    margin: 2px;
    border: none;
}
.H1
{
    font-size: 22px;
    font-weight: bold;
    text-align: left;
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Text.RegularExpressions;
using System.Web.Configuration;

#region ========== Class SqlDatabaseManager ==========
/// <summary>
/// Summary description for SqlDatabase
/// </summary>
public abstract class SqlDatabaseManager
{
    #region ========== Field ==========
    private SqlConnection sqlConnection;
    private SqlCommand sqlCommand;
    private SqlTransaction sqlTransaction;
    private List<string> errorCommand;

    private string sqlConnectionString = string.Empty;
    private string sqlConnectionKeyName = string.Empty;
    private string sqlCommandString = string.Empty;
    private string commandMessage = "You must execute command.";
    private bool commandSuccess = false;
    private bool transaction = false;
    private int rowsAffected = 0;



    #endregion

    #region ========= Constructor ==========
    /// <summary>
    /// Use sql connection string from web.config configulation.
    /// </summary>
    public SqlDatabaseManager()
    {
        if (WebConfigurationManager.ConnectionStrings[0] != null)
        {
            //สำหรับ web.config ที่มีการ add connection string ชื่อ SqlConnectionString เอาไว้
            //
            //ตัวอย่างใน web.config
            //<connectionStrings>
            //    <add name="SqlConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=SqlDatabase;Integrated Security=True;" providerName="System.Data.SqlClient"/>
            //</connectionStrings>

            sqlConnectionKeyName = WebConfigurationManager.ConnectionStrings[0].Name;
            sqlConnectionString = WebConfigurationManager.ConnectionStrings[0].ToString();
            sqlConnection = new SqlConnection(sqlConnectionString);
        }
        else
        {
            sqlConnection = new SqlConnection();
        }
    }

    private bool CheckExistConnectionStringKey(string SqlConnectionKeyName)
    {
        bool bRet = false;

        for (int i = 0; i < WebConfigurationManager.ConnectionStrings.Count; i++)
        {
            if (SqlConnectionKeyName.Equals(WebConfigurationManager.ConnectionStrings[i].Name))
                return true;
        }

        return bRet;
    }



    /// <summary>
    /// Try to find connection string via section key name first 
    /// and next try directly use connection string from parameter
    /// </summary>
    /// <param name="argConnectionAlias">May be web.config section name or ConnectionString</param>
    public SqlDatabaseManager(string argConnectionAlias)
    {
        //First seek in webconfig


        //Modification by proud PNK.U
        if (CheckExistConnectionStringKey(argConnectionAlias))
        {
            sqlConnectionKeyName = argConnectionAlias;
            sqlConnectionString
                = WebConfigurationManager.ConnectionStrings[sqlConnectionKeyName].ToString();
        }
        else
        {
            sqlConnectionKeyName = string.Empty;
            sqlConnectionString = sqlConnectionKeyName;
        }

        sqlConnection = new SqlConnection(sqlConnectionString);
    }

    /// <summary>
    /// Use sql connection string by user define.
    /// </summary>
    public SqlDatabaseManager(string SqlConnectionKeyName, string SqlConnectionString)
    {
        sqlConnectionKeyName = SqlConnectionKeyName;
        sqlConnectionString = SqlConnectionString;
        sqlConnection = new SqlConnection(sqlConnectionString);
    }
    #endregion

    #region ========== Property ==========
    /// <summary>
    /// Gets or sets Sql connection key name from web.config.
    /// </summary>
    protected virtual string ConnectionKeyName
    {
        get { return sqlConnectionKeyName; }
        set
        {
            sqlConnectionKeyName = value;
            sqlConnectionString = WebConfigurationManager.ConnectionStrings[sqlConnectionKeyName].ToString();
            sqlConnection = new SqlConnection(sqlConnectionString);
        }
    }

    /// <summary>
    /// Gets or sets Sql connection.
    /// </summary>
    protected virtual string ConnectionString
    {
        get { return sqlConnectionString; }
        set
        {
            sqlConnectionString = value;
            sqlConnection = new SqlConnection(sqlConnectionString);
        }
    }

    /// <summary>
    /// Gets or sets Sql command.
    /// </summary>
    protected virtual string CommandString
    {
        get { return sqlCommandString; }
        set
        {
            sqlCommandString = ConvertDateCommand(value);
            sqlCommand = new SqlCommand(sqlCommandString, sqlConnection);

            commandMessage = "You must execute command.";
            commandSuccess = false;
            rowsAffected = 0;

            if (transaction)
                sqlCommand.Transaction = sqlTransaction;
        }
    }

    /// <summary>
    /// Check for Sql command.
    /// </summary>
    protected virtual bool IsSuccess
    {
        get { return commandSuccess; }
    }

    /// <summary>
    /// Gets message from Sql command.
    /// </summary>
    protected virtual string Message
    {
        get { return commandMessage; }
    }

    /// <summary>
    /// Gets Number of rows affected.
    /// </summary>
    protected virtual int RowsAffected
    {
        get { return rowsAffected; }
    }
    #endregion

    #region ========== Method ==========
    /// <summary>
    /// Add the parameter value to the sql command.
    /// </summary>
    /// <param name="ParameterName">The name of Parameter.</param>
    /// <param name="ParameterValue">The value to be added.</param>
    protected virtual void AddParameter(string ParameterName, object ParameterValue)
    {
        sqlCommand.Parameters.AddWithValue(ParameterName, ParameterValue);
    }

    /// <summary>
    /// Add the parameter value to the sql command.
    /// </summary>
    ///<param name="ParameterName">The name of the parameter.</param>
    ///<param name="SqlDataType">One of the SqlDbType  values.</param>
    ///<param name="Size">The column length.</param>
    ///<param name="ColumnName">The name of the source column.</param>
    protected virtual void AddParameter(string ParameterName, SqlDbType SqlDataType, int Size, string ColumnName)
    {
        sqlCommand.Parameters.Add(ParameterName, SqlDataType, Size, ColumnName);
    }

    /// <summary>
    /// Start Sql Transaction.
    /// </summary>
    protected virtual void TransactionStart()
    {
        transaction = true;
        errorCommand = new List<string>();

        if (sqlConnection.State != ConnectionState.Open)
            sqlConnection.Open();

        sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
    }

    /// <summary>dataa
    /// Insert the DataTable to database.
    /// </summary>
    /// <param name="DataSource">The name of the DataTable. </param>
    /// <returns>Result of command.</returns>
    protected virtual bool Insert(DataTable DataSource)
    {
        bool result = false;

        try
        {
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            sqlAdapter.InsertCommand = sqlCommand;
            rowsAffected = sqlAdapter.Update(DataSource);

            commandMessage = "Command is successfully.";
            commandSuccess = true;
            result = true;
        }
        catch (Exception ex)
        {
            commandMessage = ErrorMessage(ex.Message);
            commandSuccess = false;
        }

        return result;
    }

    /// <summary>
    /// Update the database by DataTeble.
    /// </summary>
    /// <param name="DataSource">The name of the DataTable. </param>
    /// <returns>Result of command.</returns>
    protected virtual bool Update(DataTable DataSource)
    {
        bool result = false;

        try
        {
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            sqlAdapter.UpdateCommand = sqlCommand;
            rowsAffected = sqlAdapter.Update(DataSource);

            commandMessage = "Command is successfully.";
            commandSuccess = true;
            result = true;
        }
        catch (Exception ex)
        {
            commandMessage = ErrorMessage(ex.Message);
            commandSuccess = false;
        }

        return result;
    }

    /// <summary>
    /// Delete the database by DataTeble.
    /// </summary>
    /// <param name="DataSource">The name of the DataTable. </param>
    /// <returns>Result of command.</returns>
    protected virtual bool Delete(DataTable DataSource)
    {
        bool result = false;

        try
        {
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            sqlAdapter.DeleteCommand = sqlCommand;
            rowsAffected = sqlAdapter.Update(DataSource);

            commandMessage = "Command is successfully.";
            commandSuccess = true;
            result = true;
        }
        catch (Exception ex)
        {
            commandMessage = ErrorMessage(ex.Message);
            commandSuccess = false;
        }

        return result;
    }

    /// <summary>
    /// Execute Sql Transaction.
    /// </summary>
    /// <returns>Result of transaction.</returns>
    protected virtual bool ExecuteTransaction()
    {
        transaction = false;

        if (errorCommand.Count == 0)
        {
            sqlTransaction.Commit();

            commandMessage = "All command is successfully. <font color=\"darkgreen\">Transaction Commited.</font>";
            commandSuccess = true;
        }
        else
        {
            sqlTransaction.Rollback();

            string ErrorText = "Some command has error. <font color=\"red\">Transaction Rollback.</font><br />Error in: <br />";

            foreach (string aErrorSqlCommand in errorCommand)
            {
                ErrorText += aErrorSqlCommand + "<br />";
            }

            commandMessage = ErrorText;
            commandSuccess = false;
        }

        errorCommand.Clear();

        if (sqlConnection.State == ConnectionState.Open)
            sqlConnection.Close();

        return commandSuccess;
    }

    /// <summary>
    /// Execute Query Sql command.
    /// </summary>
    /// <returns>Query data in DataTable.</returns>
    protected virtual DataTable ExecuteQuery()
    {
        DataTable dataTable = new DataTable();

        try
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            sqlDataAdapter.Fill(dataTable);
            sqlDataAdapter.Dispose();

            commandMessage = "Command is successfully.";
            commandSuccess = true;
        }
        catch (Exception ex)
        {
            commandMessage = ErrorMessage(ex.Message);
            commandSuccess = false;
        }

        rowsAffected = dataTable.Rows.Count;

        return dataTable;
    }

    /// <summary>
    /// Execute Scalar Sql command.
    /// </summary>
    /// <returns>Object of value.</returns>
    protected virtual object ExecuteScalar()
    {
        object Result = 0;

        try
        {
            if (transaction)
            {
                Result = sqlCommand.ExecuteScalar();
            }
            else
            {
                if (sqlConnection.State != ConnectionState.Open)
                    sqlConnection.Open();

                Result = sqlCommand.ExecuteScalar();
                sqlConnection.Close();
            }

            commandMessage = "Command is successfully.";
            commandSuccess = true;
        }
        catch (Exception ex)
        {
            commandMessage = ErrorMessage(ex.Message);
            commandSuccess = false;
            if (transaction) AddErrorCommand(sqlCommandString, ex.Message);
        }

        return Result;
    }

    /// <summary>
    /// Execute Non Query Sql command.
    /// </summary>
    /// <returns>Result of execute command.</returns>
    protected virtual bool ExecuteNonQuery()
    {
        rowsAffected = 0;

        try
        {
            if (transaction)
            {
                rowsAffected = sqlCommand.ExecuteNonQuery();
            }
            else
            {
                if (sqlConnection.State != ConnectionState.Open)
                    sqlConnection.Open();

                rowsAffected = sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
            }

            commandMessage = "Command is successfully.";
            commandSuccess = true;
        }
        catch (Exception ex)
        {
            commandMessage = ErrorMessage(ex.Message);
            commandSuccess = false;
            if (transaction) AddErrorCommand(sqlCommandString, ex.Message);
        }

        return commandSuccess;
    }

    /// <summary>
    /// Build error message.
    /// </summary>
    /// <param name="Message">Message string.</param>
    /// <returns>Error message string.</returns>
    protected virtual string ErrorMessage(string MessageString)
    {
        return "<font color=\"red\">Command error.</font> " + MessageString;
    }

    /// <summary>
    /// Add error sql command to string collections.
    /// </summary>
    /// <param name="commandString">The sql command.</param>
    /// <param name="errorMessage">The error message.</param>
    protected virtual void AddErrorCommand(string commandString, string errorMessage)
    {
        errorCommand.Add(commandString + " <font color=\"red\">[Error message: " + errorMessage + "]</font>");
    }

    /// <summary>
    /// Convert native command to sql command.
    /// </summary>
    /// <param name="commandString">The native sql command.</param>
    /// <returns>The standard sql command.</returns>
    protected virtual string ConvertDateCommand(string commandString)
    {
        string SmallDateTimePattern = "[sS][mM][aA][lL][lL][dD][aA][tT][eE][tT][iI][mM][eE]\\([@][0-9a-zA-Z\\s]{1,}\\)";
        Regex SmallDateTimeRgx = new Regex(SmallDateTimePattern);

        foreach (Match SmallDateTimeMatchCase in SmallDateTimeRgx.Matches(commandString))
        {
            string MatchCasePattern = "^[sS][mM][aA][lL][lL][dD][aA][tT][eE][tT][iI][mM][eE]";
            Regex MatchCaseRgx = new Regex(MatchCasePattern);
            Match RemoveMatch = MatchCaseRgx.Match(SmallDateTimeMatchCase.Value);
            string TempMatchCase = SmallDateTimeMatchCase.Value.Replace(RemoveMatch.Value, "");

            commandString = commandString.Replace(SmallDateTimeMatchCase.Value, TempMatchCase.Replace("(", "Convert(SmallDateTime, ").Replace(")", ", 103)"));
        }

        string DateTimePattern = "[dD][aA][tT][eE][tT][iI][mM][eE]\\([@][0-9a-zA-Z\\s]{1,}\\)";
        Regex DateTimeRgx = new Regex(DateTimePattern);

        foreach (Match DateTimeMatchCase in DateTimeRgx.Matches(commandString))
        {
            string MatchCasePattern = "^[dD][aA][tT][eE][tT][iI][mM][eE]";
            Regex MatchCaseRgx = new Regex(MatchCasePattern);
            Match RemoveMatch = MatchCaseRgx.Match(DateTimeMatchCase.Value);
            string TempMatchCase = DateTimeMatchCase.Value.Replace(RemoveMatch.Value, "");

            commandString = commandString.Replace(DateTimeMatchCase.Value, TempMatchCase.Replace("(", "Convert(DateTime, ").Replace(")", ", 103)"));
        }

        return commandString;
    }
    #endregion

    //RAW METHOD ADDITION BY PROUD COZ FOUND SOME STRANGE RESULT
    public DataTable GetDataTable()
    {
        DataTable dataTable = new DataTable();
        using ( SqlConnection myConn = new SqlConnection (sqlConnectionString ))
        {
            SqlCommand iCommand = sqlCommand.Clone();
            iCommand.Connection = myConn;

            try
            {
                myConn.Open();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(iCommand);
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();

                int totalRows = dataTable.Rows.Count;

                commandMessage = "Command is successfully.";
                commandSuccess = true;
            }
            catch (Exception ex)
            {
                commandMessage = ErrorMessage(ex.Message);
                commandSuccess = false;
            }
            finally
            {
                if (myConn != null)
                    myConn.Close();
            }

            rowsAffected = dataTable.Rows.Count;
        }
        return dataTable;
    }

}
#endregion

#region ========== Class SqlConvert ==========
/// <summary>
/// Summary description for SqlConvert
/// </summary>
public sealed class SqlConvert
{
    /// <summary>
    /// Convert to byte[].
    /// </summary>
    /// <param name="BinaryStream">File upload binary stream.</param>
    /// <param name="StreamLength">Lenght of File upload binary stream.</param>
    /// <returns>Byte[] of binary stream.</returns>
    public static byte[] ToVarBinary(Stream BinaryStream, int StreamLength)
    {
        BinaryReader BinaryRead = new BinaryReader(BinaryStream);
        byte[] binaryData = BinaryRead.ReadBytes(StreamLength);

        return binaryData;
    }

    /// <summary>
    /// Convert to DataTime DataType with d/M/yyyy format.
    /// </summary>
    /// <param name="DateString">DateTime sring.</param>
    /// <returns>Datetime Type.</returns>
    public static DateTime ToDateTime(string DateString)
    {
        //ควรกำหนด culture ใน web.config เป็น th
        //
        //ตัวอย่างใน web.config
        //<globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="th-TH" uiCulture="th-TH"/>

        return DateTime.ParseExact(DateString, "d/M/yyyy", CultureInfo.InvariantCulture);
    }

    /// <summary>
    /// Convert to DataTime DataType with user define format.
    /// </summary>
    /// <param name="DateString">DateTime sring.</param>
    /// <param name="DateFormat">DateTime Format</param>
    /// <returns>Datetime Type.</returns>
    public static DateTime ToDateTime(string DateString, string DateFormat)
    {
        //ควรกำหนด culture ใน web.config เป็น th
        //
        //ตัวอย่างใน web.config
        //<globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="th-TH" uiCulture="th-TH"/>

        return DateTime.ParseExact(DateString, DateFormat, CultureInfo.InvariantCulture);
    }

    public static bool ToDateTime(object argRequester, out DateTime retDateTime)
    {
        retDateTime = new DateTime(0, 0, 0);
        if (Convert.IsDBNull(argRequester))
            return false;
        retDateTime = Convert.ToDateTime(argRequester);
        return true;
    }

    public static string ToDateTimeStr(object argRequester)
    {
        DateTime iDate = Convert.ToDateTime(argRequester);
        return iDate.Day.ToString("00") + "/" + iDate.Month.ToString("00")
            + "/" + iDate.Year.ToString("0000");
    }


    public static String ToString(object argRequester)
    {
        if (Convert.IsDBNull(argRequester))
            return string.Empty;
        else
            return Convert.ToString(argRequester);
    }

    public static int ToInt32(object argRequester)
    {
        if (Convert.IsDBNull(argRequester))
            return 0;
        else
            return Convert.ToInt32(argRequester);
    }

    public static double ToDouble(object argRequester)
    {
        if (Convert.IsDBNull(argRequester))
            return 0;
        else
            return Convert.ToDouble(argRequester);
    }

}
#endregion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Drawing;

/// <summary>
/// Summary description for AppBackEnd
/// </summary>
public class AppBackEnd : SqlDatabaseManager
{

    public const int constItemIsAlreadyCutOff = -1;
    public const int constItemIsDisabled = -9;
    public const int constItemIsNormal = 0;

    public static Color ColorItemNormal = Color.Navy;
    public static Color ColorItemDisabled = Color.Purple;
    public static Color ColorItemCutOff = Color.Red;

    public static string StatusTextNormal = "กำลังดำเนินการ";
    public static string StatusTextDisabled = "ยกเลิก";
    public static string StatusTextCutOff = "จ่ายแล้ว";

    public AppBackEnd()
    { }

    public AppBackEnd(string argConnectionKeyAlias)
        : base(argConnectionKeyAlias)
    { }

    public string GetNewWithDrawItemNewID()
    {
        string retStr = string.Empty;
        string prefixStr = string.Empty;
        string defaultID = string.Empty;
        int Counter = 0;
        DateTime CurrentTime = DateTime.Now;
        const string Separator = "/";
        defaultID = "0" + Separator + "0";

        prefixStr = (CurrentTime.Year + 543).ToString("0000") + Separator;

        base.CommandString = "SELECT ISNULL(MAX([DocNumber]),'" + defaultID + "') AS MAXNUM "
            + " FROM [WithdrawDoc] "
            + " WHERE SUBSTRING([DocNumber] ,1 ,CHARINDEX('" + Separator + "' ,[DocNumber]))=@VAR_PREFIX ";
        base.AddParameter("@VAR_PREFIX", prefixStr);

        retStr = base.ExecuteScalar().ToString();

        //retStr = "0xxx" + Separator + "00000";
        retStr = retStr.Substring(retStr.IndexOf(Separator) + 1);
        Int32.TryParse(retStr, out Counter);
        Counter++;

        retStr = prefixStr + Counter.ToString("00000");

        return retStr;
    }


    public System.Data.DataTable GetSingleRecord(string argTableName, string argFieldName, object argValue)
    {
        base.CommandString = "SELECT * FROM [" + argTableName + "] "
            + " WHERE [" + argFieldName + "] = @VAR_VALUE";
        base.AddParameter("@VAR_VALUE", argValue);


        return base.ExecuteQuery();
    }

    public object GetSingleRecord(string argTableName, string argFieldName, object argValue, string argOutPutFieldName)
    {
        base.CommandString = "SELECT [" + argOutPutFieldName + "] AS OUTPUT_FIELD "
            + " FROM [" + argTableName + "] "
            + " WHERE [" + argFieldName + "] = @VAR_VALUE";
        base.AddParameter("VAR_VALUE", argValue);

        return base.ExecuteScalar();
    }

    public DataTable GetWithdrawerDataSource()
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("SELECT id ");
        sqlBuilder.AppendLine("   ,([Officer].[Prefix] + ' ' + [Officer].[name] + '  ' + [Officer].[lastname]) AS compName ");
        sqlBuilder.AppendLine("FROM [Officer] ");
        sqlBuilder.AppendLine("ORDER BY [Officer].[Prefix]  ,[Officer].[name] ");

        base.CommandString = sqlBuilder.ToString();
        return base.ExecuteQuery();

    }


    public string InsertWithDrawDoc(DateTime argRegDate, DateTime argWithdrawDate
        , string argProject, int argWithdrawer, string argComment)
    {
        string retStr = string.Empty;
        retStr = GetNewWithDrawItemNewID();

        base.CommandString = "INSERT INTO [WithDrawDoc] "
            + " ([docnumber], [registerDate], [WithDrawDate], [Project] "
            + "    ,[officerID], [Comment], [flag])"
            + " VALUES (@VAR_DOCNUM ,@VAR_REGDATE ,@VAR_WITHDRAWDATE "
            + "    ,@VAR_PROJECT ,@VAR_WITHDRAWER ,@VAR_COMMENT ,0 )";


        base.AddParameter("@VAR_DOCNUM", retStr);
        base.AddParameter("@VAR_REGDATE", argRegDate);
        base.AddParameter("@VAR_WITHDRAWDATE ", argWithdrawDate);
        base.AddParameter("@VAR_PROJECT", argProject);
        base.AddParameter("@VAR_WITHDRAWER", argWithdrawer);
        base.AddParameter("@VAR_COMMENT", argComment);

        base.ExecuteNonQuery();

        return retStr;
    }


    public void UpdateWithDrawDoc(int argID, DateTime argWithdrawDate
        , string argProject, int argWithdrawer, int argFlag, string argComment)
    {
        base.CommandString = "UPDATE [WithDrawDoc] "
            + " SET [WithDrawDate] = @VAR_WITHDRAWDATE "
            + "    , [Project] = @VAR_PROJECT "
            + "    , [officerID] = @VAR_WITHDRAWER "
            + "    , [Comment] = @VAR_COMMENT , [flag] = @VAR_FLAG "
            + " WHERE [id]=@VAR_ID ";

        base.AddParameter("@VAR_ID", argID);
        base.AddParameter("@VAR_FLAG", argFlag);
        base.AddParameter("@VAR_WITHDRAWDATE ", argWithdrawDate);
        base.AddParameter("@VAR_PROJECT", argProject);
        base.AddParameter("@VAR_WITHDRAWER", argWithdrawer);
        base.AddParameter("@VAR_COMMENT", argComment);

        base.ExecuteNonQuery();

    }

    public DataTable GetAllInventoryDataSource()
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("SELECT *  ");
        sqlBuilder.AppendLine("FROM [Inventory] ");
        sqlBuilder.AppendLine("ORDER BY [name] ");

        base.CommandString = sqlBuilder.ToString();
        return base.GetDataTable();

    }

    public DataTable GetWithdrawDocDataSource()
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("SELECT row_number() over(order by [DocNumber]) as rec_no ,[withdrawdoc].[id] AS [id]  ");
        sqlBuilder.AppendLine("   ,DocNumber ,Convert(VARCHAR ,[RegisterDate] ,103) as [RegisterDate] ");
        sqlBuilder.AppendLine("   ,project ,Convert(VARCHAR ,[WithdrawDate] ,103) as [WithdrawDate] ");
        sqlBuilder.AppendLine("   ,([Officer].[Prefix] + ' ' + [Officer].[name] + '  ' + [Officer].[lastname]) AS compName ");
        sqlBuilder.AppendLine("   ,ISNULL([COUNT_ITEM],0) AS  [COUNT_ITEM],[comment] ,[flag] ");
        sqlBuilder.AppendLine("   ,CONVERT(VARCHAR, CONVERT(MONEY, SUM_VALUE ), 1) AS [SUM_VALUE] ");
        sqlBuilder.AppendLine("   ,CASE [flag] ");
        sqlBuilder.AppendLine("      WHEN " + constItemIsAlreadyCutOff.ToString() + "  THEN N'" + StatusTextCutOff + "' ");
        sqlBuilder.AppendLine("      WHEN " + constItemIsDisabled.ToString() + "  THEN N'" + StatusTextDisabled + "' ");
        sqlBuilder.AppendLine("      ELSE N'" + StatusTextNormal + "' ");
        sqlBuilder.AppendLine("   END AS [doc_status] ");
        sqlBuilder.AppendLine("FROM [withdrawdoc] ");
        sqlBuilder.AppendLine("   LEFT JOIN [Officer] ");
        sqlBuilder.AppendLine("      ON [withdrawdoc].[officerID] = [Officer].[id] ");
        sqlBuilder.AppendLine("   LEFT JOIN ");
        sqlBuilder.AppendLine("   ( ");
        sqlBuilder.AppendLine("      SELECT COUNT([ID]) AS [COUNT_ITEM] ,[withdrawDocID] ");
        sqlBuilder.AppendLine("         ,SUM([QUANTITY]*[UNITPRICE]) AS SUM_VALUE ");
        sqlBuilder.AppendLine("      FROM [WithdrawDocItem] ");
        sqlBuilder.AppendLine("      GROUP BY [withdrawDocID] ");
        sqlBuilder.AppendLine("   ) as [QR_COUNT_ITEM]([COUNT_ITEM] ,[withdrawDocID] ,[SUM_VALUE]) ");
        sqlBuilder.AppendLine("      ON [QR_COUNT_ITEM].[withdrawDocID] = [withdrawdoc].[id] ");

        base.CommandString = sqlBuilder.ToString();
        return base.ExecuteQuery();

    }

    public DataTable GetWithdrawDocItemDataSource(int argID)
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("SELECT QITEM.[id] as id ,QITEM.[withdrawDocId] as doc_id ,[Inventory].[id] AS inv_id, [Inventory].[name] as Inv_name");
        sqlBuilder.AppendLine("   ,CONVERT(VARCHAR, CONVERT(MONEY, QITEM.[unitPrice] ), 1) AS [unitPrice] ");
        sqlBuilder.AppendLine("   ,CONVERT(VARCHAR, CONVERT(MONEY, QITEM.[quantity] * QITEM.[unitPrice]), 1) AS [sum_item] ");
        sqlBuilder.AppendLine("   ,QITEM.[quantity] ,QITEM.[flag] ,QITEM.[comment] ,QITEM.[FLAG] AS FLAG ");
        sqlBuilder.AppendLine("   ,CASE QITEM.[flag]  ");
        sqlBuilder.AppendLine("      WHEN " + constItemIsAlreadyCutOff.ToString() + "  THEN N'" + StatusTextCutOff + "' ");
        sqlBuilder.AppendLine("      WHEN " + constItemIsDisabled.ToString() + "  THEN N'" + StatusTextDisabled + "' ");
        sqlBuilder.AppendLine("      ELSE N'" + StatusTextNormal + "' ");
        sqlBuilder.AppendLine("   END AS [item_status] ");
        sqlBuilder.AppendLine("FROM [withdrawDocItem] AS QITEM  ");
        sqlBuilder.AppendLine("   LEFT JOIN [withdrawDoc]");
        sqlBuilder.AppendLine("      ON [withdrawDoc].[id] = QITEM.[withdrawDocId] ");
        sqlBuilder.AppendLine("   LEFT JOIN [Inventory] ");
        sqlBuilder.AppendLine("      ON QITEM.[itemID] =  [Inventory].[id]");
        sqlBuilder.AppendLine("WHERE [withdrawDoc].[id]=@VAR_ID");

        base.CommandString = sqlBuilder.ToString();
        base.AddParameter("@VAR_ID", argID);

        return base.ExecuteQuery();
    }

    public void InsertWithdrawDocItem(int argWithdrawDocID, int argInvID, double argQuantity, string argComment)
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

        // ในการเพิ่มรายการเข้าไปใหม่จะมีการตรวจสอบกับรายการพัสดุเดิมว่ามีอยู่แล้วหรือไม่
        // ถ้ามีจะทำการ update เพิ่มจำนวนเข้าไป และต้องเป็นรายการที่ยังไม่ยกเลิกหรือตัดจ่ายไปแล้ว
        sqlBuilder.AppendLine("DECLARE @UNIT_PRICE AS FLOAT ;");
        sqlBuilder.AppendLine("DECLARE @HAS_ROW AS INT ;");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("SELECT @UNIT_PRICE =[UnitPrice] ");
        sqlBuilder.AppendLine("FROM [INVENTORY]");
        sqlBuilder.AppendLine("WHERE [ID]=@VAR_INV_ID ;");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("SET @HAS_ROW=-1 ; ");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("SELECT TOP 1 @HAS_ROW =[ID]");
        sqlBuilder.AppendLine("FROM [WithdrawDocItem]");
        sqlBuilder.AppendLine("WHERE ([ItemID]=@VAR_INV_ID) ");
        sqlBuilder.AppendLine("   AND ([WithdrawDocID]= @VAR_WITHDRAW_DOC_ID) AND ([FLAG]=" + constItemIsNormal.ToString() + ");  ");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("IF (@HAS_ROW<=0) ");
        sqlBuilder.AppendLine("   INSERT INTO [WithDrawDocItem] ");
        sqlBuilder.AppendLine("      ([WithdrawDocID], [itemID] ,[UnitPrice] ,[Quantity] ");
        sqlBuilder.AppendLine("         ,[Sequence], [flag], [Comment])");
        sqlBuilder.AppendLine("   VALUES");
        sqlBuilder.AppendLine("      (@VAR_WITHDRAW_DOC_ID ,@VAR_INV_ID ,@UNIT_PRICE ,@VAR_QUANTITY ");
        sqlBuilder.AppendLine("         ,0 ,@VAR_FLAG ,@VAR_COMMENT) ; ");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("ELSE ");
        sqlBuilder.AppendLine("   UPDATE [WithDrawDocItem] ");
        sqlBuilder.AppendLine("      SET [itemID] = @VAR_INV_ID ");
        sqlBuilder.AppendLine("         ,[UnitPrice] = @UNIT_PRICE");
        sqlBuilder.AppendLine("         ,[Quantity] = [Quantity] + @VAR_QUANTITY"); // INCREASING 
        sqlBuilder.AppendLine("         ,[Comment] = @VAR_COMMENT");
        sqlBuilder.AppendLine("   WHERE ([ID] = @HAS_ROW) ;");
        sqlBuilder.AppendLine("");
        //Delete if item quantity = 0
        //sqlBuilder.AppendLine("DELETE FROM [WithDrawDocItem]");
        //sqlBuilder.AppendLine("WHERE ([Quantity]=0) AND ([WithdrawDocID]=@VAR_WITHDRAW_DOC_ID); ");

        base.CommandString = sqlBuilder.ToString();

        base.AddParameter("@VAR_WITHDRAW_DOC_ID", argWithdrawDocID);
        base.AddParameter("@VAR_INV_ID", argInvID);
        base.AddParameter("@VAR_QUANTITY", argQuantity);
        base.AddParameter("@VAR_FLAG", constItemIsNormal);
        base.AddParameter("@VAR_COMMENT", argComment);


        base.ExecuteNonQuery();
    }

    public void DeleteWithdrawDocItem(int argID)
    {
        base.CommandString = "DELETE FROM [WithDrawDocItem] "
            + " WHERE [ID]= " + argID.ToString();
        base.ExecuteNonQuery();
    }

    public void UpdateWithdrawDocItem(int argID, int argInvID, double argQuantity, string argComment)
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("DECLARE @UNIT_PRICE AS FLOAT ;");

        sqlBuilder.AppendLine("SELECT @UNIT_PRICE = [UnitPrice] ");
        sqlBuilder.AppendLine("FROM [INVENTORY] ");
        sqlBuilder.AppendLine("WHERE [ID]=@VAR_INV_ID ;");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("   UPDATE [WithDrawDocItem] ");
        sqlBuilder.AppendLine("      SET [itemID] = @VAR_INV_ID ");
        sqlBuilder.AppendLine("         ,[UnitPrice] = @UNIT_PRICE");
        sqlBuilder.AppendLine("         ,[Quantity] = @VAR_QUANTITY");
        sqlBuilder.AppendLine("         ,[Comment] = @VAR_COMMENT");
        sqlBuilder.AppendLine("   WHERE ([ID] = @VAR_ID) ;");
        sqlBuilder.AppendLine("");
        //sqlBuilder.AppendLine("DELETE FROM [WithDrawDocItem]");
        //sqlBuilder.AppendLine("WHERE ([Quantity]=0) AND ([ID]=@VAR_WITHDRAW_DOC_ID); ");

        base.CommandString = sqlBuilder.ToString();

        base.AddParameter("@VAR_ID", argID);
        base.AddParameter("@VAR_INV_ID", argInvID);
        base.AddParameter("@VAR_QUANTITY", argQuantity);
        base.AddParameter("@VAR_COMMENT", argComment);

        base.ExecuteNonQuery();
    }

    /// <summary>
    /// ในการแก้ไขบางครั้งหากเลือกเปลี่ยน item เป็น item ที่มีอยู่แล่วในรายการเบิก จะทำให้เกิด 2 item ได้   
    /// </summary>
    public void MergeWithDrawDocItem(int ArgDocID)
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("-- GET PHASE");
        sqlBuilder.AppendLine("WITH QR_DUPLICATE_ITEM AS ");
        sqlBuilder.AppendLine("(");
        sqlBuilder.AppendLine("   SELECT [itemID] ,[FLAG] ");
        sqlBuilder.AppendLine("      ,MIN(ID) AS MIN_ID ");
        sqlBuilder.AppendLine("      ,SUM(QUANTITY) AS NEW_QUANTITY");
        sqlBuilder.AppendLine("   FROM [WITHDRAWDOCITEM]");
        sqlBuilder.AppendLine("   WHERE [withdrawDocID]=@WITHDRAW_DOC_ID");
        sqlBuilder.AppendLine("   GROUP BY [itemID],[FLAG]");
        sqlBuilder.AppendLine("   HAVING COUNT(ID) >1 ");
        sqlBuilder.AppendLine(")");
        sqlBuilder.AppendLine("-- UPDATE ONLY ATLEAST DUPLICATE ITEM");
        sqlBuilder.AppendLine("UPDATE [WITHDRAWDOCITEM]");
        sqlBuilder.AppendLine("SET QUANTITY = QR_DUPLICATE_ITEM.NEW_QUANTITY");
        sqlBuilder.AppendLine("FROM QR_DUPLICATE_ITEM ,[WITHDRAWDOCITEM] ");
        sqlBuilder.AppendLine("WHERE (QR_DUPLICATE_ITEM.[MIN_ID]=[WITHDRAWDOCITEM].[ID]) ;");
        sqlBuilder.AppendLine("");
        sqlBuilder.AppendLine("-- DELETE PHASE");
        sqlBuilder.AppendLine("WITH QR_MASK_DELETE AS ");
        sqlBuilder.AppendLine("(");
        sqlBuilder.AppendLine("   SELECT [itemID] ,[FLAG] ");
        sqlBuilder.AppendLine("      ,MIN(ID) AS MASK_ID ");
        sqlBuilder.AppendLine("   FROM [WITHDRAWDOCITEM]");
        sqlBuilder.AppendLine("   WHERE [withdrawDocID]=@WITHDRAW_DOC_ID");
        sqlBuilder.AppendLine("   GROUP BY [itemID],[FLAG]");
        sqlBuilder.AppendLine("   HAVING COUNT(ID) >1 ");
        sqlBuilder.AppendLine(")");
        sqlBuilder.AppendLine("-- DELETE FROM MULTIPLE SOURCE");
        sqlBuilder.AppendLine("DELETE FROM [WITHDRAWDOCITEM]");
        sqlBuilder.AppendLine("   FROM [WITHDRAWDOCITEM] ,QR_MASK_DELETE");
        sqlBuilder.AppendLine("   WHERE ([WITHDRAWDOCITEM].[itemID]=QR_MASK_DELETE.[itemID])");
        sqlBuilder.AppendLine("      AND ([WITHDRAWDOCITEM].[FLAG]=QR_MASK_DELETE.[FLAG])");
        sqlBuilder.AppendLine("      AND ([WITHDRAWDOCITEM].ID <> QR_MASK_DELETE.[MASK_ID]);");
        sqlBuilder.AppendLine("-- DELETE ZERO SUM ");
        sqlBuilder.AppendLine("DELETE FROM [WITHDRAWDOCITEM]");
        sqlBuilder.AppendLine("WHERE ([withdrawDocID]=@WITHDRAW_DOC_ID) ");
        sqlBuilder.AppendLine("   AND ([QUANTITY]=0)");
        sqlBuilder.AppendLine("   AND ([FLAG]=" + constItemIsNormal.ToString() + ")");

        base.CommandString = sqlBuilder.ToString();
        base.AddParameter("@WITHDRAW_DOC_ID", ArgDocID);

        base.ExecuteNonQuery();
    }

    private int CutOffFromStocByWithdrawDocItem(int argID, int argItemID, double argQuantity, int argFlag)
    {
        if (argFlag != constItemIsNormal)
            return argFlag;
        if (argQuantity < 0)
            return -999;

        //ตัดสต้อก จ้า
        //CUT FROM MAIN STOCK
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("UPDATE [Inventory] ");
        sqlBuilder.AppendLine("SET [Quantity]=[Quantity] - @VAR_QUANTITY");
        sqlBuilder.AppendLine("WHERE [Inventory].[id] = @VAR_INV_ID ; ");

        //TURN FLAG 
        sqlBuilder.AppendLine("UPDATE [withdrawDocItem] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsAlreadyCutOff.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.CommandString = sqlBuilder.ToString();

        base.AddParameter("@VAR_ID", argID);
        base.AddParameter("@VAR_INV_ID", argItemID);
        base.AddParameter("@VAR_QUANTITY", argQuantity);

        base.ExecuteNonQuery();

        return constItemIsNormal;
    }

    public int CutOffFromStocByWithdrawDoc(int argID)
    {

        int iRet = 0;
        base.CommandString = "SELECT flag FROM [WithdrawDoc] "
            + " WHERE [id]= " + argID.ToString();
        int myFlag = Convert.ToInt32(base.ExecuteScalar());

        // ตัดจ่ายจากเอกสารที่ดำเนินการอยู่ และเอกสารที่ตัดจ่ายแล้ว << จะตัดเฉพาะรายการใหม่
        if ((myFlag != constItemIsNormal) && (myFlag != constItemIsAlreadyCutOff ))
            return myFlag;

        //จะทำการจ่ายพัสดุเฉพาะรายการ ที่กำลังดำเนินการ
        base.CommandString = "SELECT * FROM [WithdrawDocItem] "
           + " WHERE ([WithdrawDocID]= " + argID.ToString() + ")\n"
           + "    AND ([flag]=" + constItemIsNormal.ToString() + ")";

        DataTable docItems = base.ExecuteQuery();
        foreach (DataRow ItemRow in docItems.Rows)
        {
            //double checked 
            int itemFlag = SqlConvert.ToInt32(ItemRow["flag"]);

            CutOffFromStocByWithdrawDocItem(SqlConvert.ToInt32(ItemRow["id"])
                , SqlConvert.ToInt32(ItemRow["itemID"])
                , SqlConvert.ToDouble(ItemRow["Quantity"])
                , itemFlag);

        }

        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

        //TURN FLAG
        sqlBuilder.AppendLine("UPDATE [withdrawDoc] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsAlreadyCutOff.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.CommandString = sqlBuilder.ToString();
        base.AddParameter("@VAR_ID", argID);
        base.ExecuteNonQuery();

        //จัดระเบียบเอกสาร
        MergeWithDrawDocItem(argID);

        return iRet;

    }

    private int ReturnToStocByWithdrawDocItem(int argID, int argItemID, double argQuantity, int argFlag)
    {
        if (argFlag != constItemIsAlreadyCutOff)
            return argFlag;
        if (argQuantity < 0)
            return -999;

        // ตัดเข้าสต๊อก
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        sqlBuilder.AppendLine("UPDATE [Inventory] ");
        sqlBuilder.AppendLine("SET [Quantity]=[Quantity] + @VAR_QUANTITY");
        sqlBuilder.AppendLine("WHERE [Inventory].[id] = @VAR_INV_ID ; ");

        //TURN FLAG 
        sqlBuilder.AppendLine("UPDATE [withdrawDocItem] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsNormal.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.CommandString = sqlBuilder.ToString();

        base.AddParameter("@VAR_ID", argID);
        base.AddParameter("@VAR_INV_ID", argItemID);
        base.AddParameter("@VAR_QUANTITY", argQuantity);

        base.ExecuteNonQuery();

        return constItemIsNormal;
    }

    /// <summary>
    /// only cutoff item
    /// </summary>
    /// <param name="argID"></param>
    /// <returns></returns>
    public int ReturnToStocByWithdrawDoc(int argID)
    {

        int iRet = 0;

        base.CommandString = "SELECT flag FROM [WithdrawDoc] "
            + " WHERE [id]= " + argID.ToString();
        int myFlag = Convert.ToInt32(base.ExecuteScalar());

        //เฉพาะรายการที่จ่ายแล้ว
        if (myFlag != constItemIsAlreadyCutOff)
            return myFlag;

        //เลือกรายการที่จ่ายแล้ว
        base.CommandString = "SELECT * FROM [WithdrawDocItem] "
           + " WHERE ([WithdrawDocID]= " + argID.ToString() + ") \n "
           + "    AND ([FLAG]=" + constItemIsAlreadyCutOff.ToString() + ")";
        DataTable docItems = base.ExecuteQuery();
        foreach (DataRow ItemRow in docItems.Rows)
        {
            int itemFlag = SqlConvert.ToInt32(ItemRow["flag"]);
            //Cut off only normal item
            if (itemFlag == constItemIsNormal)
            {
                ReturnToStocByWithdrawDocItem(SqlConvert.ToInt32(ItemRow["id"])
                    , SqlConvert.ToInt32(ItemRow["itemID"])
                    , SqlConvert.ToDouble(ItemRow["Quantity"])
                    , itemFlag);
            }
        }

        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

        //TURN FLAG
        sqlBuilder.AppendLine("UPDATE [withdrawDoc] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsNormal.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.CommandString = sqlBuilder.ToString();
        base.AddParameter("@VAR_ID", argID);
        base.ExecuteNonQuery();

        return iRet;

    }

    private int CancelledToStocByWithdrawDocItem(int argID, int argItemID, double argQuantity, int argFlag)
    {
        if (argFlag == constItemIsDisabled  )
            return argFlag;
        if (argQuantity < 0)
            return -999;

        // ตัดเข้าสต๊อก
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        //sqlBuilder.AppendLine("UPDATE [Inventory] ");
        //sqlBuilder.AppendLine("SET [Quantity]=[Quantity] + @VAR_QUANTITY");
        //sqlBuilder.AppendLine("WHERE [Inventory].[id] = @VAR_INV_ID ; ");

        //TURN FLAG 
        sqlBuilder.AppendLine("UPDATE [withdrawDocItem] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsDisabled.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.CommandString = sqlBuilder.ToString();

        base.AddParameter("@VAR_ID", argID);
        base.AddParameter("@VAR_INV_ID", argItemID);
        base.AddParameter("@VAR_QUANTITY", argQuantity);

        base.ExecuteNonQuery();

        return constItemIsNormal;
    }


    public int CancelledToStocByWithdrawDoc(int argID)
    {

        int iRet = 0;

        base.CommandString = "SELECT flag FROM [WithdrawDoc] "
            + " WHERE [id]= " + argID.ToString();
        int myFlag = Convert.ToInt32(base.ExecuteScalar());

        //เฉพาะรายการที่จ่ายแล้ว
        if (myFlag == constItemIsDisabled )
            return myFlag;

        //เลือกรายการที่จ่ายแล้ว
        base.CommandString = "SELECT * FROM [WithdrawDocItem] "
           + " WHERE ([WithdrawDocID]= " + argID.ToString() + ") \n "
           + "    AND ([FLAG]!=" +   constItemIsDisabled.ToString() + ")";
        DataTable docItems = base.ExecuteQuery();
        foreach (DataRow ItemRow in docItems.Rows)
        {
            int itemFlag = SqlConvert.ToInt32(ItemRow["flag"]);
            //Cut off only normal item

            //ถ้าจ่ายออกแล้ว ให้ตัดกลับก่อน
            if (itemFlag == constItemIsAlreadyCutOff)
            {
                ReturnToStocByWithdrawDocItem (SqlConvert.ToInt32(ItemRow["id"])
                , SqlConvert.ToInt32(ItemRow["itemID"])
                , SqlConvert.ToDouble(ItemRow["Quantity"])
                , itemFlag);
                itemFlag = constItemIsNormal;
            }

            //ยกเลิกรายการ
            CancelledToStocByWithdrawDocItem(SqlConvert.ToInt32(ItemRow["id"])
                , SqlConvert.ToInt32(ItemRow["itemID"])
                , SqlConvert.ToDouble(ItemRow["Quantity"])
                , itemFlag);

        }

        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

        //TURN FLAG
        sqlBuilder.AppendLine("UPDATE [withdrawDoc] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsDisabled.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.CommandString = sqlBuilder.ToString();
        base.AddParameter("@VAR_ID", argID);
        base.ExecuteNonQuery();

        return iRet;
    }

    private bool TableExist(string argTableName)
    {
        bool bRet = false;

        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        base.CommandString = " SELECT name FROM sysObjects WHERE name=@VAR_NAME ;";
        base.AddParameter("@VAR_NAME", argTableName);

        string tempStr = SqlConvert.ToString(base.ExecuteScalar());
        bRet = (argTableName.ToLower().Equals(tempStr.ToLower()));

        return bRet;
    }

    public void CheckedTable()
    {
        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
        bool CreateWithdrawDoc = false;
        bool CreatewithdrawDocItem = false;

        if (!TableExist("WithdrawDoc"))
        {

            CreateWithdrawDoc = true;

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("SET QUOTED_IDENTIFIER ON");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("CREATE TABLE [dbo].[WithdrawDoc](");
            sqlBuilder.AppendLine("	[id] [int] IDENTITY(1,1) NOT NULL,");
            sqlBuilder.AppendLine("	[DocNumber] [nvarchar](50) NULL,");
            sqlBuilder.AppendLine("	[registerDate] [datetime] NULL,");
            sqlBuilder.AppendLine("	[Project] [nvarchar](255) NULL,");
            sqlBuilder.AppendLine("	[withdrawDate] [datetime] NULL,");
            sqlBuilder.AppendLine("	[Issuer] [nvarchar](100) NULL,");
            sqlBuilder.AppendLine("	[officerID] [int] NULL,");
            sqlBuilder.AppendLine("	[comment] [nvarchar](max) NULL,");
            sqlBuilder.AppendLine("	[flag] [int] NULL,");
            sqlBuilder.AppendLine(" CONSTRAINT [PK_WithdrawDoc] PRIMARY KEY CLUSTERED ");
            sqlBuilder.AppendLine("(");
            sqlBuilder.AppendLine("	[id] ASC");
            sqlBuilder.AppendLine(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
            sqlBuilder.AppendLine(") ON [PRIMARY]");
            sqlBuilder.AppendLine("");


            base.CommandString = sqlBuilder.ToString();

            base.ExecuteNonQuery();
        }

        if (!TableExist("withdrawDocItem"))
        {

            sqlBuilder = new System.Text.StringBuilder();
            CreatewithdrawDocItem = true;

            sqlBuilder.AppendLine("SET ANSI_NULLS ON");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("SET QUOTED_IDENTIFIER ON");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("CREATE TABLE [dbo].[withdrawDocItem](");
            sqlBuilder.AppendLine("	[id] [int] IDENTITY(1,1) NOT NULL,");
            sqlBuilder.AppendLine("	[withdrawDocID] [int] NOT NULL,");
            sqlBuilder.AppendLine("	[sequence] [int] NULL,");
            sqlBuilder.AppendLine("	[itemID] [int] NULL,");
            sqlBuilder.AppendLine("	[quantity] [float] NULL,");
            sqlBuilder.AppendLine("	[unitPrice] [float] NULL,");
            sqlBuilder.AppendLine("	[flag] [int] NULL,");
            sqlBuilder.AppendLine("	[comment] [text] NULL,");
            sqlBuilder.AppendLine(" CONSTRAINT [PK_withdrawDocItem] PRIMARY KEY CLUSTERED ");
            sqlBuilder.AppendLine("(");
            sqlBuilder.AppendLine("	[id] ASC");
            sqlBuilder.AppendLine(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
            sqlBuilder.AppendLine(") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]");
            sqlBuilder.AppendLine("");

            sqlBuilder.AppendLine("");

            base.CommandString = sqlBuilder.ToString();

            base.ExecuteNonQuery();
        }

        if (CreateWithdrawDoc && CreatewithdrawDocItem)
        {
            sqlBuilder = new System.Text.StringBuilder();
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[WithdrawDoc]  WITH CHECK ADD  CONSTRAINT [FK_WithdrawDoc_WithdrawDoc] FOREIGN KEY([id]);");
            sqlBuilder.AppendLine("REFERENCES [dbo].[WithdrawDoc] ([id])");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[WithdrawDoc] CHECK CONSTRAINT [FK_WithdrawDoc_WithdrawDoc];");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[WithdrawDoc] ADD  CONSTRAINT [DF_WithdrawDoc_flag]  DEFAULT ((0)) FOR [flag];");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[withdrawDocItem]  WITH CHECK ADD  CONSTRAINT [FK_withdrawDocItem_Inventory] FOREIGN KEY([itemID])");
            sqlBuilder.AppendLine("REFERENCES [dbo].[Inventory] ([id]);");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[withdrawDocItem] CHECK CONSTRAINT [FK_withdrawDocItem_Inventory];");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[withdrawDocItem]  WITH CHECK ADD  CONSTRAINT [FK_withdrawDocItem_WithdrawDoc] FOREIGN KEY([withdrawDocID])");
            sqlBuilder.AppendLine("REFERENCES [dbo].[WithdrawDoc] ([id]);");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("ALTER TABLE [dbo].[withdrawDocItem] CHECK CONSTRAINT [FK_withdrawDocItem_WithdrawDoc];");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("");

            base.CommandString = sqlBuilder.ToString();
            base.ExecuteNonQuery();
        }

        if (!TableExist("inventory"))
        {
            sqlBuilder = new System.Text.StringBuilder();

            sqlBuilder.AppendLine("SET ANSI_NULLS ON;");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("SET QUOTED_IDENTIFIER ON;");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("CREATE TABLE [dbo].[Inventory](");
            sqlBuilder.AppendLine("	[id] [int] IDENTITY(1,1) NOT NULL,");
            sqlBuilder.AppendLine("	[inventoryID] [nvarchar](100) NULL,");
            sqlBuilder.AppendLine("	[NII] [nvarchar](100) NULL,");
            sqlBuilder.AppendLine("	[name] [nvarchar](255) NULL,");
            sqlBuilder.AppendLine("	[unitPrice] [float] NULL,");
            sqlBuilder.AppendLine("	[dateBuy] [datetime] NULL,");
            sqlBuilder.AppendLine("	[typeID] [int] NULL,");
            sqlBuilder.AppendLine("	[offID] [int] NULL,");
            sqlBuilder.AppendLine("	[UnitID] [int] NULL,");
            sqlBuilder.AppendLine("	[Quantity] [float] NULL,");
            sqlBuilder.AppendLine(" CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED ");
            sqlBuilder.AppendLine("(");
            sqlBuilder.AppendLine("	[id] ASC");
            sqlBuilder.AppendLine(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
            sqlBuilder.AppendLine(") ON [PRIMARY];");
            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("CREATE UNIQUE NONCLUSTERED INDEX [IX_InventoryID] ON [dbo].[Inventory] ");
            sqlBuilder.AppendLine("(");
            sqlBuilder.AppendLine("	[inventoryID] ASC");
            sqlBuilder.AppendLine(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];");

            base.CommandString = sqlBuilder.ToString();
            base.ExecuteNonQuery();

            sqlBuilder = new System.Text.StringBuilder();
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000001', 00100, 00100, N'เก้าอีบุนวม' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000002', 00100, 00100, N'เก้าอี้พลาสติก' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000003', 00100, 00100, N'เครื่อขยายเสียง' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000004', 00100, 00100, N'เครื่องฉายแผ่นใส' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000005', 00100, 00100, N'แก้วน้ำพลาสติก' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000006', 00100, 00100, N'แท่นพิธีกร' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000007', 00100, 00100, N'โคมไฟ' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000008', 00100, 00100, N'ไฟฉาย' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000009', 00100, 00100, N'ไม้บรรทัด' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000010', 00100, 00100, N'กระเป๋าเอกสาร' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000011', 00100, 00100, N'กระดาษปรุไข' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000012', 00100, 00100, N'จานกระเบื้อง' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000013', 00100, 00100, N'ดินสอ' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000014', 00100, 00100, N'ตราประทับ' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000015', 00100, 00100, N'ที่เย็บกระดาษ' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000016', 00100, 00100, N'ปากกา' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000017', 00100, 00100, N'ปากกาไวท์บอร์ด' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000018', 00100, 00100, N'พัดลม' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000019', 00100, 00100, N'ยางลบ' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000020', 00100, 00100, N'สมุด' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000021', 00100, 00100, N'สานไฟ' );");
            sqlBuilder.AppendLine("INSERT INTO [INVENTORY] ([INVENTORYID] ,[UNITPRICE] ,[QUANTITY] ,[NAME] ) VALUES ('2032000022', 00100, 00100, N'หมุดปักกระดาน' );");
            sqlBuilder.AppendLine("");

            base.CommandString = sqlBuilder.ToString();
            base.ExecuteNonQuery();
        }

        if (!TableExist("OFFICER"))
        {
            sqlBuilder = new System.Text.StringBuilder();

            sqlBuilder.AppendLine("SET ANSI_NULLS ON;");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("SET QUOTED_IDENTIFIER ON;");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("CREATE TABLE [dbo].[Officer](");
            sqlBuilder.AppendLine("	[id] [int] IDENTITY(1,1) NOT NULL,");
            sqlBuilder.AppendLine("	[prefix] [nvarchar](50) NULL,");
            sqlBuilder.AppendLine("	[name] [nvarchar](50) NULL,");
            sqlBuilder.AppendLine("	[lastName] [nvarchar](50) NULL,");
            sqlBuilder.AppendLine("	[Position] [nvarchar](50) NULL,");
            sqlBuilder.AppendLine("	[Tel] [nvarchar](10) NULL,");
            sqlBuilder.AppendLine("	[Email] [nvarchar](50) NULL,");
            sqlBuilder.AppendLine(" CONSTRAINT [PK_Officer] PRIMARY KEY CLUSTERED ");
            sqlBuilder.AppendLine("(");
            sqlBuilder.AppendLine("	[id] ASC");
            sqlBuilder.AppendLine(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
            sqlBuilder.AppendLine(") ON [PRIMARY];");
            sqlBuilder.AppendLine("");

            sqlBuilder.AppendLine("");
            sqlBuilder.AppendLine("");

            base.CommandString = sqlBuilder.ToString();
            base.ExecuteNonQuery();

            sqlBuilder = new System.Text.StringBuilder();
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาง', N'ภานุมาศ	', N'วิญญาเงือก', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'นเรศ	', N'สายทะโชติ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'ฉันทนา	', N'บุญมา', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'ทิพย์พวัลย์	', N'ตุ่นแก้ว', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'สุภาพ	', N'ปัทสุทธะ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'นัฎฐิญา	', N'มณีวรรณ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาง', N'อรทัย	', N'ทิพกนก', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'เพียงนภา	', N'กาติ๊บ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'สุริยา	', N'มหายศ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาง', N'สุกัญญา	', N'เตจ๊ะยวง', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'อรุณี	', N'ผัดเป้า', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาง', N'คมคาย	', N'สิทธิปราณี', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'พรประภา	', N'มายาง', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'สุทัน	', N'คำปันทิพย์', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'อภิรดา	', N'ตัณฑเจริญกิจ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'จตุพร	', N'แก่นจันทร์', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'นลิตา	', N'ขันธพัฒน์', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาง', N'วนารัตน์	', N'กันยะมูล', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'ณัฐยา	', N'ชัยลังกา', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'จุฑามาศ	', N'กรงทอง', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'ศราวุธ	', N'โทปุรินทร์', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'เอกชัย	', N'คำชื่น', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'ทวีศักดิ์	', N'ฝั้นใจ', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'ศิริรักษ์	', N'คงธะรังษี', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'แววดาว	', N'พรมณี', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'ภัทราวุธ	', N'ยอดพล', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาง', N'กัญญา	', N'ทาแกง', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'อภิญญา	', N'เค้ามูล', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นาย', N'พิชาติ	', N'มากมาย', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("INSERT INTO [OFFICER] ([PREFIX] ,[NAME] ,[LASTNAME] ,[POSITION] ) VALUES ( N'นางสาว', N'รุ่งอรุณ	', N'ปกติจิตต์', N'เจ้าหน้าที่ธุรการ' );");
            sqlBuilder.AppendLine("UPDATE [OFFICER] SET [PREFIX] = RTRIM(LTRIM([PREFIX])) ,[NAME] = RTRIM(LTRIM([NAME])), [LASTNAME] = RTRIM(LTRIM([LASTNAME]));");

            base.CommandString = sqlBuilder.ToString();
            base.ExecuteNonQuery();
        }

    }

    private int DisabledToStocByWithdrawDoc(int argID)
    {

        int iRet = 0;

        base.CommandString = "UPDATE [WithdrawDocItem] "
            + " SET FLAG = " + constItemIsDisabled.ToString()
            + " WHERE [WithdrawDocID]= " + argID.ToString();
        base.ExecuteNonQuery();

        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

        //TURN FLAG
        sqlBuilder.AppendLine("UPDATE [withdrawDoc] ");
        sqlBuilder.AppendLine("SET [flag]= " + constItemIsDisabled.ToString());
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.AddParameter("@VAR_ID", argID);
        base.ExecuteNonQuery();

        return iRet;

    }

    public int DeleteToStocByWithdrawDoc(int argID)
    {

        int iRet = 0;

        base.CommandString = "DELETE FROM  [WithdrawDocItem] "
            + " WHERE [WithdrawDocID]= " + argID.ToString();
        base.ExecuteNonQuery();

        System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

        //TURN FLAG
        sqlBuilder.AppendLine("DELETE [withdrawDoc] ");
        sqlBuilder.AppendLine("WHERE [id] = @VAR_ID ; ");

        base.AddParameter("@VAR_ID", argID);
        base.ExecuteNonQuery();

        return iRet;

    }

}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="WithDrawList.aspx.cs" Inherits="WithDrawList" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="Inventory.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <Ajax:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </Ajax:ToolkitScriptManager>
    <div>
        <div style="width: 100%; background-color: whitesmoke; padding: 10px;">
            <asp:Label ID="Label1" runat="server" Text="บัญชีใบเบิกพัสดุ" CssClass="H1"></asp:Label>
            &nbsp(&nbsp<asp:LinkButton ID="LinkButtonAddNewDoc" runat="server" CssClass="MyLink"
                OnClick="LinkButtonAddNewDoc_Click" PostBackUrl="~/WithDrawItem.aspx?mode=addnew&id=">ออกใบเบิกพัสดุใหม่</asp:LinkButton>&nbsp)
            <%-- &nbsp|&nbsp
            <asp:LinkButton ID="LinkButtonCancelDoc" runat="server" CssClass="MyLink" OnClick="LinkButtonCancelDoc_Click">ยกเลิกใบเบิกพัสดุ</asp:LinkButton>--%>
        </div>
        <div style="margin-left: auto; margin-right: auto; background-color: transaprent;
            text-align: center; width: auto">
            <asp:GridView ID="GridViewDocumentList" runat="server" AutoGenerateColumns="False"
                Width="100%" DataKeyNames="id" OnRowDataBound="GridViewDocumentList_RowDataBound"
                CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCommand="GridViewDocumentList_RowCommand">
                <RowStyle BackColor="#EFF3FB" />
                <Columns>
                    <asp:TemplateField>
                        <ItemStyle Width="160px" HorizontalAlign="Center" />
                        <ItemTemplate>
                            <asp:LinkButton ID="LinkButtonDeleteItem" runat="server" CommandName="request delete document"
                                CommandArgument='<%# Bind("id") %>' CssClass="MyLink">ลบ</asp:LinkButton>&nbsp|&nbsp
                            <asp:LinkButton ID="LinkButtonCutOff" runat="server" CommandName="request cutoff document"
                                CommandArgument='<%# Bind("id") %>' CssClass="MyLink">จ่ายพัสดุ</asp:LinkButton>&nbsp|&nbsp
                            <asp:LinkButton ID="LinkButtonCancelItem" runat="server" CommandName="request cencelled document"
                                CommandArgument='<%# Bind("id") %>' CssClass="MyLink">ยกเลิก</asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="rec_no" HeaderText="ที่" ItemStyle-Width="40px" ItemStyle-HorizontalAlign="Center"
                        SortExpression="DocNumber">
                        <ItemStyle HorizontalAlign="Center" Width="40px"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="doc_status" HeaderText="สถานะ" SortExpression="doc_status"
                        ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center">
                        <ItemStyle HorizontalAlign="Center"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="DocNumber" HeaderText="หมายเลขเอกสาร" SortExpression="DocNumber"
                        ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center">
                        <ItemStyle HorizontalAlign="Center" Width="100px"></ItemStyle>
                    </asp:BoundField>
                    <%-- <asp:BoundField DataField="registerDate" HeaderText="ลง วันที่" SortExpression="registerDate"
                        ItemStyle-Width="80px" ItemStyle-HorizontalAlign="Center">
                        <ItemStyle HorizontalAlign="Center" Width="80px"></ItemStyle>
                    </asp:BoundField>--%>
                    <asp:BoundField DataField="Project" HeaderText="โครงงาน" SortExpression="Project"
                        ItemStyle-Width="200px" ItemStyle-HorizontalAlign="left">
                        <ItemStyle HorizontalAlign="Left"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="withdrawDate" HeaderText="วดป. ที่เบิก" SortExpression="withdrawDate"
                        ItemStyle-Width="80px" ItemStyle-HorizontalAlign="Center">
                        <ItemStyle HorizontalAlign="Center" Width="80px"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="compName" HeaderText="ผู้เบิก" SortExpression="compName"
                        ItemStyle-Width="150px" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left">
                        <ItemStyle HorizontalAlign="Left"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="COUNT_ITEM" HeaderText="จำนวนรายการ" SortExpression="COUNT_ITEM"
                        ItemStyle-Width="40px" ItemStyle-HorizontalAlign="Center">
                        <ItemStyle HorizontalAlign="Center" Width="40px"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="SUM_VALUE" HeaderText="มูลค่ารวม" SortExpression="SUM_VALUE"
                        ItemStyle-Width="100px" HeaderStyle-HorizontalAlign="Right">
                        <ItemStyle HorizontalAlign="Right"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="comment" HeaderText="หมายเหตุ" SortExpression="comment"
                        ItemStyle-HorizontalAlign="Left">
                        <ItemStyle HorizontalAlign="Left"></ItemStyle>
                    </asp:BoundField>
                    <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                        Visible="false" SortExpression="id" />
                    <asp:BoundField DataField="flag" HeaderText="flag" InsertVisible="False" ReadOnly="True"
                        Visible="false" SortExpression="flag" />
                </Columns>
                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <EditRowStyle BackColor="#2461BF" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
        </div>
    </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class WithDrawList : System.Web.UI.Page
{
    private AppBackEnd myAppLogic = new AppBackEnd("ThaiCreateDbConnectionString");
    private DataTable PageDataSource
    {
        get
        {

            if (ViewState["_PageDataSource_"] == null)
                ViewState["_PageDataSource_"] = myAppLogic.GetWithdrawDocDataSource();

            return ViewState["_PageDataSource_"] as DataTable;
        }
        set { ViewState["_PageDataSource_"] = value; }
    }

    public const string constCommandDeleteRequest = "request delete document";
    public const string constCommandCancelledRequest = "request cencelled document";
    public const string constCommandCutOffRequest = "request cutoff document";

    public bool UsesDeleteMode = false;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            myAppLogic.CheckedTable(); // รันแครั้งเดียวเพื่อตรวจสอบตาราง ต่อๆไปก้อลบออกไม่ก้อ comment
            FillWithdrawDocGrid(GridViewDocumentList);
        }
    }

    private void FillWithdrawDocGrid(GridView argGrid)
    {
        DataTable dtItem = myAppLogic.GetWithdrawDocDataSource();

        if (dtItem.Rows.Count > 0)
        {
            argGrid.DataSource = dtItem;
            argGrid.DataBind();
        }
        else
        {
            //NO RECORD 
            dtItem.Rows.Add(dtItem.NewRow());
            argGrid.DataSource = dtItem;
            argGrid.DataBind();

            int TotalColumns = argGrid.Rows[0].Cells.Count;
            argGrid.Rows[0].Cells.Clear();
            argGrid.Rows[0].Cells.Add(new TableCell());
            argGrid.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            argGrid.Rows[0].Cells[0].Text = "<center>ไม่มีข้อมูลนะคะ</center>";
        }
    }

    protected void LinkButtonAddNewDoc_Click(object sender, EventArgs e)
    {

    }
    protected void LinkButtonCancelDoc_Click(object sender, EventArgs e)
    {

    }
    protected void GridViewDocumentList_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //TRACE NO RECORD MODE
            if (Convert.IsDBNull(DataBinder.Eval(e.Row.DataItem, "flag")))
            {
                return;
            }

            int iflag = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "flag"));
            string URLWithParameter = string.Empty;

            LinkButton DeleteButton = e.Row.FindControl("LinkButtonDeleteItem") as LinkButton;
            LinkButton CancelledButton = e.Row.FindControl("LinkButtonCancelItem") as LinkButton;
            LinkButton CutOffButton = e.Row.FindControl("LinkButtonCutOff") as LinkButton;
            AjaxControlToolkit.ConfirmButtonExtender CutOffConfirmation = null;
            AjaxControlToolkit.ConfirmButtonExtender CancelledConfirmation = null;
            AjaxControlToolkit.ConfirmButtonExtender DeleteConfirmation = null;

            DeleteButton.CommandName = constCommandDeleteRequest;
            DeleteButton.CommandArgument = DataBinder.Eval(e.Row.DataItem, "id").ToString();

            CancelledButton.CommandName = constCommandCancelledRequest;
            CancelledButton.CommandArgument = DataBinder.Eval(e.Row.DataItem, "id").ToString();

            if (DeleteButton != null)
            {
                DeleteConfirmation = new AjaxControlToolkit.ConfirmButtonExtender();
                DeleteConfirmation.TargetControlID = DeleteButton.ID;
                DeleteConfirmation.ConfirmText = "กรุณายืนยันเพื่อที่จะ =ลบ= เอกสารเบิกพัสดุ  #"
                    + DataBinder.Eval(e.Row.DataItem, "DocNumber").ToString();
                e.Row.Cells[0].Controls.Add(DeleteConfirmation);
            }
            if (CancelledButton != null)
            {
                CancelledConfirmation = new AjaxControlToolkit.ConfirmButtonExtender();
                CancelledConfirmation.TargetControlID = CancelledButton.ID;
                CancelledConfirmation.ConfirmText = "กรุณายืนยันเพื่อที่จะ =ยกเลิก= เอกสารเบิกพัสดุ  #"
                    + DataBinder.Eval(e.Row.DataItem, "DocNumber").ToString();
                e.Row.Cells[0].Controls.Add(CancelledConfirmation);
            }
            if (CutOffButton != null)
            {
                CutOffConfirmation = new AjaxControlToolkit.ConfirmButtonExtender();
                CutOffConfirmation.TargetControlID = CutOffButton.ID;
                CutOffConfirmation.ConfirmText = "กรุณายืนยันเพื่อที่จะ =จ่ายพัสดุ= เอกสารเบิกพัสดุ  #"
                    + DataBinder.Eval(e.Row.DataItem, "DocNumber").ToString();
                e.Row.Cells[0].Controls.Add(CutOffConfirmation);
            }
            //ENEABLED DELETE MODE
            if (!UsesDeleteMode)
            {
                DeleteButton.Enabled = false;
                DeleteButton.CssClass = "MyLinkDisabled";
                DeleteConfirmation.Enabled = false;
            }

            

            switch (iflag)
            {
                case AppBackEnd.constItemIsAlreadyCutOff:
                    //e.Row.BackColor = System.Drawing.Color.WhiteSmoke ;
                    //e.Row.Font.Italic = true;
                    //e.Row.Style.Add(HtmlTextWriterStyle.Cursor, "pointer");
                    e.Row.BackColor = System.Drawing.Color.LemonChiffon;
                    e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='lightcoral';");
                    e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='LemonChiffon';");
                    e.Row.ForeColor = AppBackEnd.ColorItemCutOff;
                    URLWithParameter = "WithDrawItem.aspx?mode=edit&id=" + DataBinder.Eval(e.Row.DataItem, "id").ToString();

                    //ยกเว้น คอลัมภ์แรกที่เป็น templete เพราะจะเกิด event สองอย่าง
                    //คือ ปุ่ม คลิก และ แถวคลิกซึ่งจะวิ่งไป detailpage
                    //แก้ไขเพิ่มเติมถ้าเอกสารเป็น Mode Disabled เอกสารจะถูก VIEWได้เท่านั้น
                    for (int i = 1; i < e.Row.Cells.Count; i++)
                    {
                        TableCell iCell = e.Row.Cells[i];
                        iCell.Attributes.Add("onclick", "window.location= '" + URLWithParameter + "' ; return false;");
                        iCell.Style.Add(HtmlTextWriterStyle.Cursor, "pointer");
                    }
                    break;
                case AppBackEnd.constItemIsDisabled:
                    e.Row.Font.Bold = true;
                    e.Row.ForeColor = System.Drawing.Color.Gray;
                    e.Row.BackColor = System.Drawing.Color.LightGray;
                    e.Row.ForeColor = AppBackEnd.ColorItemDisabled;
                    if (DeleteButton != null)
                    {
                        DeleteButton.Enabled = false;
                        DeleteButton.CssClass = "MyLinkDisabled";
                        DeleteConfirmation.Enabled = false;
                    }
                    if (CancelledButton != null)
                    {
                        CancelledButton.Enabled = false;
                        CancelledButton.CssClass = "MyLinkDisabled";
                        CancelledConfirmation.Enabled = false;
                    }
                    if (CutOffButton != null)
                    {
                        CutOffButton.Enabled = false;
                        CutOffButton.CssClass = "MyLinkDisabled";
                        CutOffConfirmation.Enabled = false;
                    }

                    URLWithParameter = "WithDrawItem.aspx?mode=readonly&id=" + DataBinder.Eval(e.Row.DataItem, "id").ToString();
                    //ยกเว้น คอลัมภ์แรกที่เป็น templete เพราะจะเกิด event สองอย่าง
                    //คือ ปุ่ม คลิก และ แถวคลิกซึ่งจะวิ่งไป detailpage
                    //แก้ไขเพิ่มเติมถ้าเอกสารเป็น Mode Disabled เอกสารจะถูก VIEWได้เท่านั้น
                    for (int i = 1; i < e.Row.Cells.Count; i++)
                    {
                        TableCell iCell = e.Row.Cells[i];
                        iCell.Attributes.Add("onclick", "window.location= '" + URLWithParameter + "' ; return false;");
                        iCell.Style.Add(HtmlTextWriterStyle.Cursor, "pointer");
                    }
                    break;
                default:
                    e.Row.ForeColor = AppBackEnd.ColorItemNormal;
                    e.Row.BackColor = System.Drawing.Color.White;
                    //e.Row.Style.Add(HtmlTextWriterStyle.Cursor, "pointer");
                    e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='OrangeRed';");
                    e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='White';");

                    URLWithParameter = "WithDrawItem.aspx?mode=edit&id=" + DataBinder.Eval(e.Row.DataItem, "id").ToString();
                    //ยกเว้น คอลัมภ์แรกที่เป็น templete เพราะจะเกิด event สองอย่าง
                    //คือ ปุ่ม คลิก และ แถวคลิกซึ่งจะวิ่งไป detailpage
                    //แก้ไขเพิ่มเติมถ้าเอกสารเป็น Mode Disabled เอกสารจะถูก VIEWได้เท่านั้น
                    for (int i = 1; i < e.Row.Cells.Count; i++)
                    {
                        TableCell iCell = e.Row.Cells[i];
                        iCell.Attributes.Add("onclick", "window.location= '" + URLWithParameter + "' ; return false;");
                        iCell.Style.Add(HtmlTextWriterStyle.Cursor, "pointer");
                    }
                    break;
            }


        }
    }
    protected void GridViewDocumentList_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        switch (e.CommandName)
        {
            case constCommandDeleteRequest:
                break;
            case constCommandCancelledRequest:
                myAppLogic.CancelledToStocByWithdrawDoc(SqlConvert.ToInt32(e.CommandArgument));
                FillWithdrawDocGrid(GridViewDocumentList);
                break;
            case constCommandCutOffRequest:
                myAppLogic.CutOffFromStocByWithdrawDoc(SqlConvert.ToInt32(e.CommandArgument));
                FillWithdrawDocGrid(GridViewDocumentList);
                break;
        }
    }
}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="WithDrawItem.aspx.cs" Inherits="WithDrawItem" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="Inventory.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <Ajax:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </Ajax:ToolkitScriptManager>
    <div style="width: 100%; background-color: PapayaWhip; padding: 10px;">
        <asp:Label ID="Label1" runat="server" Text="ใบเบิกพัสดุ" CssClass="H1"></asp:Label>
        <br />
        <asp:LinkButton ID="LinkButtonSaveDoc" runat="server" CssClass="MyLink" OnClick="LinkButtonSaveDoc_Click">บันทึกใบเบิกพัสดุ</asp:LinkButton>&nbsp|&nbsp
        <asp:LinkButton ID="LinkButtonCutOff" runat="server" CssClass="MyLink" OnClick="LinkButtonCutOff_Click">จ่ายของจากใบเบิกพัสดุ</asp:LinkButton>&nbsp|&nbsp
        <%--<asp:LinkButton ID="LinkButtonReturn" runat="server" CssClass="MyLink">ตัดยอดคืนจากใบเบิกพัสดุ</asp:LinkButton>&nbsp|&nbsp--%>
        <asp:LinkButton ID="LinkButtonCancelDoc" runat="server" CssClass="MyLink" 
            onclick="LinkButtonCancelDoc_Click">ยกเลิกใบเบิกพัสดุ</asp:LinkButton>&nbsp|&nbsp
        <asp:LinkButton ID="LinkButtonPrint" runat="server" CssClass="MyLink">พิมพ์ใบเบิกพัสดุ</asp:LinkButton>&nbsp|&nbsp
        <asp:LinkButton ID="LinkButtonBackToMain" runat="server" CssClass="MyLink" PostBackUrl="~/WithDrawList.aspx">กลับไปที่บัญชีใบเบิกพัสดุ</asp:LinkButton>
    </div>
    <div style="paading-top: 20px; background-color: LightYellow">
        <table border="0" cellpadding="2" cellspacing="2">
            <colgroup>
                <col width="120px" />
                <col width="500px" />
            </colgroup>
            <tr>
                <td>
                    สถานะ
                </td>
                <td>
                    <asp:Label ID="LabelDocStatus" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td>
                    หมายเลข
                </td>
                <td>
                    <asp:HiddenField ID="HiddenFieldID" runat="server" />
                    <asp:TextBox ID="TextBoxDocNo" Enabled="false" ReadOnly="true" Width="100px" CssClass="MyTextboxDisabled"
                        runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    วดป. ออกเอกสาร
                </td>
                <td>
                    <asp:TextBox ID="TextBoxRegDate" Enabled="false" ReadOnly="true" Width="100px" CssClass="MyTextboxDisabled"
                        runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    วดป. เบิกพัสดุ
                </td>
                <td>
                    <asp:TextBox ID="TextBoxWithDrawDate" Width="100px" CssClass="MyTextbox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    โครงการ/แผนงาน
                </td>
                <td>
                    <asp:TextBox ID="TextBoxProject" Width="500px" CssClass="MyTextbox" runat="server"></asp:TextBox>
                   
                </td>
            </tr>
            <tr>
                <td>
                    ผู้เบิก
                </td>
                <td>
                    <Ajax:ComboBox ID="ComboBoxWithdrawer" runat="server">
                    </Ajax:ComboBox>
                </td>
            </tr>
            <tr>
                <td>
                    หมายเหตุ
                </td>
                <td>
                    <asp:TextBox ID="TextBoxComment" Width="500px" CssClass="MyTextbox" runat="server"></asp:TextBox>
                </td>
            </tr>
        </table>
    </div>
    <div>
        <asp:Panel ID="ItemPanel" Visible="false" runat="server">
            <asp:GridView ID="GridViewItem" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                OnRowCancelingEdit="GridViewItem_RowCancelingEdit" OnRowCommand="GridViewItem_RowCommand"
                OnRowDataBound="GridViewItem_RowDataBound" OnRowEditing="GridViewItem_RowEditing"
                OnRowUpdating="GridViewItem_RowUpdating" ShowFooter="True" CellPadding="4" ForeColor="#333333"
                GridLines="None">
                <RowStyle BackColor="#EFF3FB" />
                <EmptyDataRowStyle BackColor="#CCCCCC" />
                <Columns>
                    <asp:TemplateField HeaderText="Edit" ShowHeader="False" HeaderStyle-HorizontalAlign="Center"
                        ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center" HeaderStyle-Width="100px"
                        FooterStyle-Width="100px" ItemStyle-Width="100px">
                        <EditItemTemplate>
                            <asp:LinkButton ID="lbkUpdate" runat="server" CausesValidation="True" CommandName="Update"
                                Text="Update"></asp:LinkButton>
                            <asp:LinkButton ID="lnkCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                                Text="Cancel"></asp:LinkButton>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:LinkButton ID="lnkAdd" runat="server" CausesValidation="False" CommandName="Insert"
                                Text="Add"></asp:LinkButton>
                        </FooterTemplate>
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkEdit" runat="server" CausesValidation="False" CommandName="Edit"
                                Text="Edit"></asp:LinkButton>
                            <asp:LinkButton ID="lnkDelete" runat="server" CausesValidation="False" CommandName="Delete Item"
                                CommandArgument='<%# Bind("id") %>' Text="Del"></asp:LinkButton>                            
                        </ItemTemplate>
                        <FooterStyle HorizontalAlign="Center" Width="100px" />
                        <HeaderStyle HorizontalAlign="Center" Width="100px" />
                        <ItemStyle HorizontalAlign="Center" Width="100px" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="ที่" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center"
                        HeaderStyle-Width="80px" FooterStyle-Width="80px">
                        <ItemTemplate>
                            <asp:Label ID="lblSequence" runat="server" Width="80px"></asp:Label>
                        </ItemTemplate>
                        <FooterStyle Width="80px" />
                        <HeaderStyle HorizontalAlign="Center" Width="80px" />
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                    <asp:BoundField DataField="item_status" HeaderText="สถานะ" SortExpression="item_status"
                        ItemStyle-Width="100px" ReadOnly="true" HeaderStyle-Width="100px" ItemStyle-HorizontalAlign="Center">
                        <HeaderStyle Width="100px" />
                        <ItemStyle Width="100px" />
                    </asp:BoundField>
                    <asp:TemplateField HeaderText="รายการ" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="200px">
                        <ItemTemplate>
                            <asp:Label ID="lbItemName" runat="server" Text='<%# Bind("Inv_name") %>' Width="200px"></asp:Label>
                            <asp:HiddenField ID="HiddenFieldFlagInItemTemplate" runat="server" Value='<%# Bind("flag") %>' />
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownListInventory" Width="200px" runat="server" DataSourceID="SqlDataSourceInv"
                                DataTextField="name" DataValueField="id">
                            </asp:DropDownList>
                            <asp:HiddenField ID="HiddenFieldItemID" runat="server" Value='<%# Bind("id") %>' />
                            <asp:HiddenField ID="HiddenFieldInvID" runat="server" Value='<%# Bind("inv_id") %>' />
                            <asp:HiddenField ID="HiddenFieldFlag" runat="server" Value='<%# Bind("flag") %>' />
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:DropDownList ID="DropDownListNewInventory" Width="200px" runat="server" DataSourceID="SqlDataSourceInv"
                                DataTextField="name" DataValueField="id">
                            </asp:DropDownList>
                        </FooterTemplate>
                        <HeaderStyle HorizontalAlign="Left" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="จำนวน" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="80px"
                        ItemStyle-HorizontalAlign="Right">
                        <ItemTemplate>
                            <asp:Label ID="lblQuantity" runat="server" Text='<%# Bind("Quantity") %>' Width="80px"></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBoxQuantity" runat="server" Width="80px" Text='<%# Bind("Quantity") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="TextBoxNewQuantity" runat="server" Width="80px" Text='<%# Bind("Quantity") %>'></asp:TextBox>
                        </FooterTemplate>
                        <HeaderStyle HorizontalAlign="Left" />
                        <ItemStyle HorizontalAlign="Right" />
                    </asp:TemplateField>
                    <asp:BoundField DataField="UnitPrice" HeaderText="ราคา@หน่วย" SortExpression="UnitPrice"
                        ReadOnly="true" ItemStyle-Width="100px" HeaderStyle-Width="100px" ItemStyle-HorizontalAlign="Right">
                        <HeaderStyle Width="100px" />
                        <ItemStyle Width="100px" />
                    </asp:BoundField>
                    <asp:BoundField DataField="sum_item" HeaderText="รวม" SortExpression="sum_item" ItemStyle-Width="100px"
                        ReadOnly="true" HeaderStyle-Width="100px" ItemStyle-HorizontalAlign="Right">
                        <HeaderStyle Width="100px" />
                        <ItemStyle Width="100px" />
                    </asp:BoundField>
                    <asp:TemplateField HeaderText="หมายเหตุ" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="200px">
                        <ItemTemplate>
                            <asp:Label ID="lblComment" runat="server" Text='<%# Bind("Comment") %>' Width="200px"></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBoxComment" runat="server" Width="200px" Text='<%# Bind("Comment") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="TextBoxNewComment" runat="server" Width="200px" Text='<%# Bind("Comment") %>'></asp:TextBox>
                        </FooterTemplate>
                        <HeaderStyle HorizontalAlign="Left" />
                    </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <EditRowStyle BackColor="#2461BF" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSourceInv" runat="server" ConnectionString="<%$ ConnectionStrings:ThaiCreateDbConnectionString %>"
                SelectCommand="SELECT [id], [name] FROM [Inventory] ORDER BY [NAME]"></asp:SqlDataSource>
        </asp:Panel>
    </div>
    <Ajax:CalendarExtender ID="CalendarExtenderCommon" Format="dd/MM/yyyy" TargetControlID="TextBoxWithDrawDate"
        runat="server">
    </Ajax:CalendarExtender>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;

public partial class WithDrawItem : System.Web.UI.Page
{

    enum enumDocumentMode
    {
        AddNewMode,
        EditMode,
        ReadOnlyMode
    }


    private AppBackEnd myAppLogic = new AppBackEnd("ThaiCreateDbConnectionString");
    const String commonDateTimePattern = "dd/MM/yyyy";

    private bool RunOnce
    {
        get
        {
            if (ViewState["CAP_RUNONCE"] == null)
                ViewState["CAP_RUNONCE"] = false;

            return Convert.ToBoolean(ViewState["CAP_RUNONCE"]);
        }
        set
        {
            ViewState["CAP_RUNONCE"] = value;
        }
    }
    private int CAPID
    {
        get
        {
            if (Session["CAP_WITHDRAW_DOC_ID"] == null)
                Session["CAP_WITHDRAW_DOC_ID"] = 0;

            return Convert.ToInt32(Session["CAP_WITHDRAW_DOC_ID"]);
        }
        set
        {
            Session["CAP_WITHDRAW_DOC_ID"] = value;
        }
    }
    private bool IsEditMode
    {
        get
        {
            if (ViewState["CAP_IS_EDIT_MODE"] == null)
                ViewState["CAP_IS_EDIT_MODE"] = false;

            return Convert.ToBoolean(ViewState["CAP_IS_EDIT_MODE"]);
        }
        set
        {
            ViewState["CAP_IS_EDIT_MODE"] = value;
        }
    }

    private enumDocumentMode docMode
    {
        get
        {
            if (ViewState["CAP_DOC_MODE"] == null)
                ViewState["CAP_DOC_MODE"] = enumDocumentMode.AddNewMode;

            return (enumDocumentMode)Enum.Parse(typeof(enumDocumentMode), Convert.ToString(ViewState["CAP_DOC_MODE"]));
        }
        set
        {
            ViewState["CAP_DOC_MODE"] = value;
        }
    }

    private int Flag
    {
        get
        {
            if (ViewState["CAP_FLAG"] == null)
                ViewState["CAP_FLAG"] = 0;

            return Convert.ToInt32(ViewState["CAP_FLAG"]);
        }
        set
        {
            ViewState["CAP_FLAG"] = value;
        }
    }
    private DataTable invDataTable;


    protected void Page_Load(object sender, EventArgs e)
    {
        //Response.Write(myAppLogic.GetNewWithDrawItemNewID() + "  <br />");


        if (!IsPostBack)
        {
            if (!this.RunOnce)
            {
                invDataTable = myAppLogic.GetAllInventoryDataSource(); //Cache
                ItemPanel.Visible = true;
                string tempStr = Request.QueryString["mode"].ToString();

                if (tempStr.Equals("addnew"))
                    this.docMode = enumDocumentMode.AddNewMode;
                else if (tempStr.Equals("edit"))
                    this.docMode = enumDocumentMode.EditMode;
                else
                    this.docMode = enumDocumentMode.ReadOnlyMode;

                if (this.docMode != enumDocumentMode.AddNewMode)
                {
                    this.CAPID = 0;
                    int temp = 0;
                    tempStr = Request.QueryString["id"].ToString();
                    Int32.TryParse(tempStr, out  temp);
                    // parameter ,property cann't assigned throug out statement
                    this.CAPID = temp;
                }
                this.RunOnce = true;
            }
            Refresh();
        }
    }


    private void Refresh()
    {

        string tempStr = string.Empty;
        if (this.docMode == enumDocumentMode.AddNewMode)
        {
            //Prevent error by assigned unreached value
            this.CAPID = -800;
            NewItemValues();
        }
        else
        {
            RetriveItem(this.CAPID);

            if (this.docMode == enumDocumentMode.ReadOnlyMode)
                MakeItemReadOnly();

        }
        FillwithdrawDocItem();
    }

    #region HeaderManage
    private void NewItemValues()
    {
        TextBoxDocNo.Text = " - Unassigned -";
        TextBoxRegDate.Text = SqlConvert.ToDateTimeStr(DateTime.Now);
        TextBoxWithDrawDate.Text = SqlConvert.ToDateTimeStr(DateTime.Now);
        ComboBoxWithdrawer.FillWithData(myAppLogic.GetWithdrawerDataSource()
          , "ID", "compName", string.Empty);
        LabelDocStatus.Text = "เอกสารใหม่";
        FillwithdrawDocItem();
    }

    private void RetriveItem(int argID)
    {
        System.Data.DataTable myTable = myAppLogic.GetSingleRecord("WithdrawDoc", "ID", argID);
        if (myTable == null) return;

        foreach (DataRow myRow in myTable.Rows)
        {
            this.CAPID = argID;
            TextBoxDocNo.Text = SqlConvert.ToString(myRow["DocNumber"]);
            TextBoxProject.Text = SqlConvert.ToString(myRow["Project"]);
            TextBoxComment.Text = SqlConvert.ToString(myRow["Comment"]);

            TextBoxRegDate.Text = SqlConvert.ToDateTimeStr(myRow["RegisterDate"]);
            TextBoxWithDrawDate.Text = SqlConvert.ToDateTimeStr(myRow["withdrawDate"]);

            ComboBoxWithdrawer.FillWithData(myAppLogic.GetWithdrawerDataSource()
                , "ID", "compName", SqlConvert.ToString(myRow["officerID"]));

            string tempStr = SqlConvert.ToString(myRow["FLAG"]);
            int flag = 0;
            Int32.TryParse(tempStr, out  flag);
            LabelDocStatus.Text = GetDocStatus(flag);
        }

    }

    private void MakeItemReadOnly()
    {
        //disable footer
        GridViewItem.ShowFooter = false;

        TextBoxComment.Enabled = false;
        TextBoxProject.Enabled = false;
        TextBoxWithDrawDate.Enabled = false;
        CalendarExtenderCommon.Enabled = false;
        ComboBoxWithdrawer.Enabled = false;

        LinkButtonCancelDoc.Enabled = false;
        LinkButtonCutOff.Enabled = false;
        LinkButtonSaveDoc.Enabled = false;

        LinkButtonCancelDoc.CssClass = "MyLinkDisabled";
        LinkButtonCutOff.CssClass = "MyLinkDisabled";
        LinkButtonSaveDoc.CssClass = "MyLinkDisabled";
    }

    private string GetDocStatus(int argStatusCode)
    {
        string retStr = string.Empty;
        switch (argStatusCode)
        {
            case AppBackEnd.constItemIsAlreadyCutOff: retStr = AppBackEnd.StatusTextCutOff; break;
            case AppBackEnd.constItemIsDisabled: retStr = AppBackEnd.StatusTextDisabled; break;
            default: retStr = AppBackEnd.StatusTextNormal; break;
        }
        return retStr;
    }

    private void SaveItem()
    {
        switch (this.docMode)
        {
            case enumDocumentMode.AddNewMode:
                InsertedItem();
                break;
            case enumDocumentMode.EditMode:
                UpdateItem();
                break;
            default:
                return;
                break;
        }
        Refresh();
    }


    private void InsertedItem()
    {

        TextBoxDocNo.Text = myAppLogic.GetNewWithDrawItemNewID();

        DateTime RegDate
            = SqlConvert.ToDateTime(TextBoxRegDate.Text.Trim(), commonDateTimePattern);
        DateTime WithdrawDate
            = SqlConvert.ToDateTime(TextBoxWithDrawDate.Text.Trim(), commonDateTimePattern);

        string NewDocNum = myAppLogic.InsertWithDrawDoc(RegDate, WithdrawDate
            , TextBoxProject.Text.Trim(), Convert.ToInt32(ComboBoxWithdrawer.SelectedValue)
            , TextBoxComment.Text.Trim());

        this.CAPID = Convert.ToInt32(myAppLogic.GetSingleRecord("WithDrawDoc", "DocNumber", NewDocNum, "ID"));
        this.Flag = 0;
        this.docMode = enumDocumentMode.EditMode;

        FillwithdrawDocItem();

    }

    private void UpdateItem()
    {
        DateTime WithdrawDate
           = SqlConvert.ToDateTime(TextBoxWithDrawDate.Text.Trim(), commonDateTimePattern);
        int WtithdrawerID = Convert.ToInt32(ComboBoxWithdrawer.SelectedValue);

        myAppLogic.UpdateWithDrawDoc(this.CAPID, WithdrawDate
            , TextBoxProject.Text.Trim(), WtithdrawerID, this.Flag
            , TextBoxComment.Text.Trim());

    }
    protected void LinkButtonSaveDoc_Click(object sender, EventArgs e)
    {
        SaveItem();
    }
    #endregion
    #region ItemManagement
    private void FillwithdrawDocItem()
    {
        DataTable dtItem = myAppLogic.GetWithdrawDocItemDataSource(this.CAPID);

        if (dtItem.Rows.Count > 0)
        {
            GridViewItem.DataSource = dtItem;
            GridViewItem.DataBind();
        }
        else
        {
            //NO RECORD 
            dtItem.Rows.Add(dtItem.NewRow());
            GridViewItem.DataSource = dtItem;
            GridViewItem.DataBind();

            int TotalColumns = GridViewItem.Rows[0].Cells.Count;
            GridViewItem.Rows[0].Cells.Clear();
            GridViewItem.Rows[0].Cells.Add(new TableCell());
            GridViewItem.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            GridViewItem.Rows[0].Cells[0].Text = "<center>ไม่มีข้อมูลนะคะ</center>";
        }
    }
    #endregion

    protected void GridViewItem_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        //ContactTableAdapter contact = new ContactTableAdapter();
        bool flag = false;
        if (e.CommandName.Equals("Insert"))
        {
            //AjaxControlToolkit.ComboBox cmbInventory = (AjaxControlToolkit.ComboBox)GridViewItem.FooterRow.FindControl("ComboBoxNewItem");
            enumDocumentMode capMode = this.docMode;
            //double checked
            if (this.docMode == enumDocumentMode.ReadOnlyMode)
                return;

            //ADDNEW MODE


            DropDownList ddlInventory = (DropDownList)GridViewItem.FooterRow.FindControl("DropDownListNewInventory");
            TextBox txtQuantity = (TextBox)GridViewItem.FooterRow.FindControl("TextBoxNewQuantity");
            TextBox txtComment = (TextBox)GridViewItem.FooterRow.FindControl("TextBoxNewComment");

            ddlInventory.Visible = true;
            txtQuantity.Visible = true;
            txtComment.Visible = true;


            double capQuantity = 0;
            double.TryParse(txtQuantity.Text, out capQuantity);

            // จะเพิ่มข้อมูลที่มากกว่า 0 เท่านั้น
            // ถ้าป้อนมาผิดให้ Edit เอา
            if (capQuantity > 0)
            {

                if (capMode == enumDocumentMode.AddNewMode )
                {
                    //TURN TO EDIT MODE
                    SaveItem();
                    this.docMode = enumDocumentMode.EditMode ;
                }
                //Insert default value and fillgrid

                myAppLogic.InsertWithdrawDocItem(this.CAPID
                    , Convert.ToInt32(ddlInventory.SelectedValue)
                    , capQuantity, txtComment.Text);

                if (capMode == enumDocumentMode.AddNewMode)
                    Refresh();
                else
                    FillwithdrawDocItem();
            }
        }
        else if (e.CommandName.Equals("Delete Item"))
        {
            int itemID = SqlConvert.ToInt32(e.CommandArgument);
            myAppLogic.DeleteWithdrawDocItem(itemID);
            FillwithdrawDocItem();
        }
    }
    protected void GridViewItem_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        switch (e.Row.RowType)
        {
            case DataControlRowType.DataRow:

                //เพื่อ บล้อกไม่ให้แก้ไข/ลบ รายการที่จ่ายแล้วและรายการที่ยกเลิก
                HiddenField FlagInItemTemplate = (HiddenField)e.Row.FindControl("HiddenFieldFlagInItemTemplate");

                int itemFlag = 0;
                if (FlagInItemTemplate != null)
                    Int32.TryParse(FlagInItemTemplate.Value, out  itemFlag);
                LinkButton cxEditButton = (LinkButton)e.Row.FindControl("lnkEdit");
                LinkButton cxDeleteButton = (LinkButton)e.Row.FindControl("lnkDelete");
                AjaxControlToolkit.ConfirmButtonExtender DeleteConfirmation = null;

                if (cxDeleteButton != null)
                {
                    DeleteConfirmation = new AjaxControlToolkit.ConfirmButtonExtender();
                    DeleteConfirmation.TargetControlID = cxDeleteButton.ID;
                    DeleteConfirmation.ConfirmText = "ต้องการลบรายการนี้ใช่หรือไม่";
                    e.Row.Cells[0].Controls.Add(DeleteConfirmation);
                }

                switch (itemFlag)
                {
                    case AppBackEnd.constItemIsNormal:
                        e.Row.ForeColor = AppBackEnd.ColorItemNormal;
                        break;
                    case AppBackEnd.constItemIsAlreadyCutOff:
                        if (cxEditButton != null)
                        {
                            cxEditButton.Enabled = false;
                            cxEditButton.ForeColor = System.Drawing.Color.WhiteSmoke;
                        }

                        if (cxDeleteButton != null)
                        {
                            cxDeleteButton.Enabled = false;
                            cxDeleteButton.ForeColor = System.Drawing.Color.WhiteSmoke;
                            if (DeleteConfirmation != null)
                                DeleteConfirmation.Enabled = false;
                        }
                        e.Row.ForeColor = AppBackEnd.ColorItemCutOff;

                        break;
                    default:
                        if (cxEditButton != null)
                        {
                            cxEditButton.Enabled = false;
                            cxEditButton.ForeColor = System.Drawing.Color.WhiteSmoke;
                        }

                        e.Row.ForeColor = AppBackEnd.ColorItemDisabled;
                        e.Row.Font.Bold = true;

                        if (cxDeleteButton != null)
                        {
                            cxDeleteButton.Enabled = false;
                            cxDeleteButton.ForeColor = System.Drawing.Color.WhiteSmoke;
                            if (DeleteConfirmation != null)
                                DeleteConfirmation.Enabled = false;
                        }
                        break;
                }

                //TextBox txtQuantity = (TextBox)e.Row.FindControl("TextBoxQuantity");
                //TextBox txtComment = (TextBox)e.Row.FindControl("TextBoxComment");
                //string tempStr = string.Empty;
                //for (int j = 0; j < e.Row.Cells.Count; j++)
                //{
                //    int counter = 0;
                //    tempStr += j.ToString() + "\n";
                //    foreach (Control iControl in e.Row.Cells[j].Controls)
                //    {
                //        tempStr += string.Format("[{0}] ,[{1}] ,[{2}] \n", counter++, iControl.ID, iControl.ToString());
                //    }
                //    tempStr += "========================\n";
                //}

                //This Row active only when this row become edit mode
                Label counterLabel = (Label)e.Row.FindControl("lblSequence");
                if (counterLabel != null)
                {
                    counterLabel.Text = (e.Row.RowIndex + 1).ToString("#,##0");
                }

                if (e.Row.RowIndex == GridViewItem.EditIndex)
                {
                    //in edit mode grid not allow dynamic add any control
                    DropDownList ddlInventory = (DropDownList)e.Row.FindControl("DropDownListInventory");
                    HiddenField hfItemID = (HiddenField)e.Row.FindControl("HiddenFieldInvID");

                    //string invIDStr = SqlConvert.ToString (DataBinder.Eval(e.Row.DataItem, "itemID"));
                    if ((ddlInventory != null) && (hfItemID != null))
                        ddlInventory.SelectedItemByValue(hfItemID.Value);
                    //ddlInventory.
                    TextBox txtQuantity = (TextBox)e.Row.FindControl("TextBoxQuantity");
                    if (txtQuantity != null)
                    {
                        //AjaxControlToolkit.MaskedEditExtender myMask = new AjaxControlToolkit.MaskedEditExtender();
                        //myMask.TargetControlID = txtQuantity.ID;
                        //myMask.MaskType = AjaxControlToolkit.MaskedEditType.Number;
                        //e.Row.Cells[0].Controls.Add(myMask);                        
                    }

                    //LiteralControl CapItemID = new LiteralControl();
                    //CapItemID.Text = DataBinder.Eval(e.Row.DataItem, "id").ToString();
                    //CapItemID.Visible = false;
                    //CapItemID.ID = "LCForItemID";

                    //e.Row.Controls.Add(CapItemID);

                    //if (ddlInventory != null)
                    //    ddlInventory.FillWithData(invDataTable, "id", "name", string.Empty);
                }
                //ddlInventory.FillWithData(invDataTable, "id", "name", string.Empty);
                break;
            case DataControlRowType.Footer:
                //DropDownList ddlNewInvenory = (DropDownList)e.Row.FindControl("DropDownListInventory");
                //ddlNewInvenory.FillWithData(invDataTable, "id", "name", string.Empty);

                break;
        }


    }

    protected void GridViewItem_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        //In this mode all of control must be switch to edit template 
        bool flag = false;

        int capInvId = -1;
        DropDownList ddlEditIventory = (DropDownList)GridViewItem.Rows[e.RowIndex].FindControl("DropDownListInventory");
        if (ddlEditIventory != null)
            Int32.TryParse(ddlEditIventory.SelectedValue, out capInvId);

        if (capInvId > -1)
        {

            HiddenField hfFlag = (HiddenField)GridViewItem.Rows[e.RowIndex].FindControl("HiddenFieldFlag");
            int capFlag = AppBackEnd.constItemIsNormal;
            if (hfFlag != null)
                Int32.TryParse(hfFlag.Value, out capFlag);

            // ถ้ารายการนี้ตัดจ่ายแล้วห้ามแก้ไข 
            if (capFlag != AppBackEnd.constItemIsNormal)
            {
                GridViewItem.EditIndex = -1;
                return;
            }

            double capQuantity = 0;
            string capComment = string.Empty;
            TextBox txtEditQuantity = (TextBox)GridViewItem.Rows[e.RowIndex].FindControl("TextBoxQuantity");
            if (txtEditQuantity != null)
                double.TryParse(txtEditQuantity.Text, out capQuantity);

            //รายการที่แก้ไขต้องมีจำนวนมากกว่า 0 จึงจะอนุญาติให้แก้ไข

            if (capQuantity > 0)
            {

                TextBox txtEditComment = (TextBox)GridViewItem.Rows[e.RowIndex].FindControl("TextBoxComment");
                if (txtEditComment != null)
                    capComment = txtEditComment.Text.Trim();

                HiddenField hfItemID = (HiddenField)GridViewItem.Rows[e.RowIndex].FindControl("HiddenFieldItemID");


                int capItemID = 0;
                if (hfItemID != null)
                    Int32.TryParse(hfItemID.Value, out capItemID);

                myAppLogic.UpdateWithdrawDocItem(capItemID, capInvId, capQuantity, capComment);
                myAppLogic.MergeWithDrawDocItem(this.CAPID);
            }
        }
        //Turn off edit mode
        GridViewItem.EditIndex = -1;
        FillwithdrawDocItem();

    }
    protected void GridViewItem_RowEditing(object sender, GridViewEditEventArgs e)
    {

        GridViewItem.EditIndex = e.NewEditIndex;
        FillwithdrawDocItem();
    }
    protected void GridViewItem_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridViewItem.EditIndex = -1;
        FillwithdrawDocItem();
    }

    protected void LinkButtonCutOff_Click(object sender, EventArgs e)
    {
        SaveItem();
        myAppLogic.CutOffFromStocByWithdrawDoc(this.CAPID);
        Refresh();
    }
    protected void LinkButtonCancelDoc_Click(object sender, EventArgs e)
    {
        SaveItem();
        myAppLogic.CancelledToStocByWithdrawDoc (this.CAPID);
        this.docMode = enumDocumentMode.ReadOnlyMode;
        Refresh();
    }
}

public static class ControlExtension
{
    public static void SelectedItemByValue(this AjaxControlToolkit.ComboBox argCommboBox, string argValue)
    {
        if (argCommboBox.Items.Count == 0) return;

        for (int i = 0; i < argCommboBox.Items.Count; i++)
        {
            if (argCommboBox.Items[i].Value.Equals(argValue))
            {
                argCommboBox.Items[i].Selected = true;
                return;
            }
        }
    }

    public static void SelectedItemByValue(this DropDownList argControl, string argValue)
    {
        if (argControl.Items.Count == 0) return;

        for (int i = 0; i < argControl.Items.Count; i++)
        {
            if (argControl.Items[i].Value.Equals(argValue))
            {
                argControl.Items[i].Selected = true;
                return;
            }
        }
    }


    public static void FillWithData(this AjaxControlToolkit.ComboBox argCommboBox, DataTable argData
        , string argIDFieldName, string argTextFieldName, string argValue)
    {
        if ((argCommboBox == null) || (argData == null)) return;

        argCommboBox.DataSource = argData;
        argCommboBox.DataTextField = argTextFieldName;
        argCommboBox.DataValueField = argIDFieldName;
        argCommboBox.DataBind();

        if (argCommboBox.Items.Count == 0) return;

        for (int i = 0; i < argCommboBox.Items.Count; i++)
        {
            if (argCommboBox.Items[i].Value.Equals(argValue))
            {
                argCommboBox.Items[i].Selected = true;
                return;
            }
        }
    }


    public static void FillWithData(this DropDownList argControl, DataTable argData
        , string argIDFieldName, string argTextFieldName, string argValue)
    {

        //if ((argControl == null) || (argData == null)) return;

        argControl.DataSource = argData;
        argControl.DataTextField = argTextFieldName;
        argControl.DataValueField = argIDFieldName;
        argControl.DataBind();

        if (argControl.Items.Count == 0) return;

        for (int i = 0; i < argControl.Items.Count; i++)
        {
            if (argControl.Items[i].Value.Equals(argValue))
            {
                argControl.Items[i].Selected = true;
                return;
            }
        }

        if (argControl.Items.Count > 0)
            argControl.Items[0].Selected = true;
    }

}

System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.AppendLine("SELECT QITEM.[id] as id ,QITEM.[withdrawDocId] as doc_id ,[Inventory].[id] AS inv_id, [Inventory].[name] as Inv_name");
sqlBuilder.AppendLine("   ,CONVERT(VARCHAR, CONVERT(MONEY, QITEM.[unitPrice] ), 1) AS [unitPrice] ");
sqlBuilder.AppendLine("   ,CONVERT(VARCHAR, CONVERT(MONEY, QITEM.[quantity] * QITEM.[unitPrice]), 1) AS [sum_item] ");
sqlBuilder.AppendLine("   ,QITEM.[quantity] ,QITEM.[flag] ,QITEM.[comment] ,QITEM.[FLAG] AS FLAG ");
sqlBuilder.AppendLine("   ,CASE QITEM.[flag]  ");
sqlBuilder.AppendLine("      WHEN " + constItemIsAlreadyCutOff.ToString() + "  THEN N'" + StatusTextCutOff + "' ");
sqlBuilder.AppendLine("      WHEN " + constItemIsDisabled.ToString() + "  THEN N'" + StatusTextDisabled + "' ");
sqlBuilder.AppendLine("      ELSE N'" + StatusTextNormal + "' ");
sqlBuilder.AppendLine("   END AS [item_status] ");
sqlBuilder.AppendLine("FROM [withdrawDocItem] AS QITEM  ");
sqlBuilder.AppendLine("   LEFT JOIN [withdrawDoc]");
sqlBuilder.AppendLine("      ON [withdrawDoc].[id] = QITEM.[withdrawDocId] ");
sqlBuilder.AppendLine("   LEFT JOIN [Inventory] ");
sqlBuilder.AppendLine("      ON QITEM.[itemID] =  [Inventory].[id]");
sqlBuilder.AppendLine("WHERE [withdrawDoc].[id]=@VAR_ID");

System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.AppendFormat(@"SELECT QITEM.[id] as id ,QITEM.[withdrawDocId] as doc_id ,[Inventory].[id] AS inv_id, [Inventory].[name] as Inv_name
   ,CONVERT(VARCHAR, CONVERT(MONEY, QITEM.[unitPrice] ), 1) AS [unitPrice] 
   ,CONVERT(VARCHAR, CONVERT(MONEY, QITEM.[quantity] * QITEM.[unitPrice]), 1) AS [sum_item] 
   ,QITEM.[quantity] ,QITEM.[flag] ,QITEM.[comment] ,QITEM.[FLAG] AS FLAG 
   ,CASE QITEM.[flag]  
      WHEN {0}  THEN N'{1}' 
      WHEN {2}  THEN N'{3}' 
      ELSE N'{4}' 
   END AS [item_status] 
FROM [withdrawDocItem] AS QITEM  
   LEFT JOIN [withdrawDoc]
      ON [withdrawDoc].[id] = QITEM.[withdrawDocId] 
   LEFT JOIN [Inventory] 
      ON QITEM.[itemID] =  [Inventory].[id]
WHERE [withdrawDoc].[id]=@VAR_ID", constItemIsAlreadyCutOff.ToString(), StatusTextCutOff, constItemIsDisabled.ToString(), StatusTextDisabled, StatusTextNormal);