<%@ Page language="vb"%>
<!-----------------------------------------------------------------------------------------------------------------
Example of TreeGrid using synchronous (submit, non AJAX) communication with server
Example of simple table without tree
Uses DataTable for database communication
Uses new ASP.NET style scripting with event Page_Load
Uses MS Access database TableData.mdb as data and XML file TableDef.xml as TreeGrid layout
! Check if ASP application has write access to TableData.mdb file
------------------------------------------------------------------------------------------------------------------>
<html>
   <head>
      <link id="GridCSS" href="../../Grid/Grid.css" title="GridStyle" type="text/css" rel="stylesheet">
      <script src="../../Grid/GridE.js"> </script>
   </head>
   <body>
      <div style="WIDTH:100%;HEIGHT:90%">
         <bdo 
            Layout_Url="TableDef.xml" 
            Data_Tag="TGData" 
            Upload_Tag="TGData" Upload_Format="Internal"
            Export_Url="Export.aspx" Export_Data="TGData" Export_Param_File="SubmitTable.xls"
            ></bdo>
      </div>
      <form id="Form1" method="post" runat="server">
         <input id="TGData" type="hidden" runat="server"/>
         <input type="submit" value="Submit changes to server"/>
      </form>
   </body>
</html>
<script language="vb" runat="server">
   ' -------------------------------------------------------------------------------------------------------------------------------
   Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

      ' --- Database initialization ---
      Dim Path As String : Path = System.IO.Path.GetDirectoryName(Context.Request.PhysicalPath)
      Dim Conn As System.Data.OleDb.OleDbConnection : Conn = New System.Data.OleDb.OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""" + Path + "\\Database.mdb"";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1")
      Conn.Open()
      Dim Cmd As System.Data.OleDb.OleDbCommand : Cmd = Conn.CreateCommand()
   
      ' --- Response initialization ---
      Response.ContentType = "text/html"
      Response.Charset = "utf-8"
      Response.AppendHeader("Cache-Control", "max-age=1, must-revalidate")
      System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

      ' --- Save data to database ---
      Dim XML As String : XML = TGData.Value
      If XML <> "" And XML <> Nothing Then
   
         Dim X As System.Xml.XmlDocument : X = New System.Xml.XmlDocument()
         X.LoadXml(HttpUtility.HtmlDecode(XML))
         Dim Ch As System.Xml.XmlNodeList : Ch = X.GetElementsByTagName("Changes")
         If (Ch.Count > 0) Then
            For Each I As System.Xml.XmlElement In Ch(0)
               Dim SQL As String : SQL = ""
               Dim id As String : id = I.GetAttribute("id")
         
               If I.GetAttribute("Deleted") = "1" Then
                  SQL = "DELETE FROM TableData WHERE ID=" + id

               ElseIf I.GetAttribute("Added") = "1" Then
                  SQL = "INSERT INTO TableData(ID,Project,Resource,Week,Hours) VALUES(" _
                     + id + "," _
                     + "'" + I.GetAttribute("Project").Replace("'", "''") + "'," _
                     + "'" + I.GetAttribute("Resource").Replace("'", "''") + "'," _
                     + I.GetAttribute("Week") + "," _
                     + I.GetAttribute("Hours") + ")"
               
               ElseIf I.GetAttribute("Changed") = "1" Then
                  SQL = "UPDATE TableData SET "
                  For Each A As System.Xml.XmlAttribute In I.Attributes
                     If A.Name = "Project" Or A.Name = "Resource" Then
                        SQL += A.Name + " = '" + A.Value.Replace("'", "''") + "',"
                     ElseIf A.Name = "Week" Or A.Name = "Hours" Then
                        SQL += A.Name + " = " + A.Value + ","
                     End If
                  Next A
                  SQL = SQL.TrimEnd(",".ToCharArray()) 'Last comma away
                  SQL = SQL + " WHERE ID=" + id
               End If
               
               Cmd.CommandText = SQL
               Cmd.ExecuteNonQuery()
            Next I
         End If
      End If
      
      ' --- Load data from database ---

      Cmd.CommandText = "SELECT * FROM TableData"
      Dim R As System.Data.OleDb.OleDbDataReader : R = Cmd.ExecuteReader()
      Dim S As String
      S = "<Grid><Body><B>"
      Do While R.Read()
         S = S + "<I id='" + R(0).ToString() + "'" _
            + " Project='" + R(1).ToString().Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "'" _
            + " Resource='" + R(2).ToString().Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "'" _
            + " Week='" + R(3).ToString() + "'" _
            + " Hours='" + R(4).ToString() + "'" _
            + "/>"
      Loop
      S = S + "</B></Body></Grid>"
      TGData.Value = S
      Conn.Close()
   End Sub
   ' -------------------------------------------------------------------------------------------------------------------------------
</script>
