listing1010.aspx

asp入门到精通的源代码

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
   'declare connection
   dim Conn as new OleDbConnection( _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=H:\ASPNET\data\banking.mdb")
   
   sub Page_Load(obj as Object, e as EventArgs) 
      if Not Page.IsPostBack then
         FillDataGrid()
      end if
   end sub
   
   sub Submit(obj as object, e as eventargs)
      'insert new data
      dim i, j as integer
      dim params(7) as string
      dim strText as string
      dim blnGo as boolean = true
       
      j = 0
      
      for i = 0 to AddPanel.Controls.Count - 1
         if AddPanel.controls(i).GetType Is GetType(TextBox) then
            strText = Ctype(AddPanel.Controls(i), TextBox).Text
            if strText <> "" then
               params(j) = strText
            else
               blnGo = false
               lblMessage.Text = lblMessage.Text & "You forgot to enter " & _
                  "a value for " & AddPanel.Controls(i).ID & "<p>"
               lblMessage.Style("ForeColor") = "Red"
            end if
            j = j + 1
         end if
      next
       
      if not blnGo then
         exit sub
      end if
       
      dim strSQL as string = "INSERT INTO tblUsers " & _
         "(FirstName, LastName, Address, City, State, " & _
          "Zip, Phone) VALUES (" & _
         "'" & params(0) & "'," & _
         "'" & params(1) & "'," & _
         "'" & params(2) & "'," & _
         "'" & params(3) & "'," & _
         "'" & params(4) & "'," & _
         "'" & params(5) & "'," & _
         "'" & params(6) & "')"
         
      ExecuteStatement(strSQL)
      
      FillDataGrid()
   end sub
    
   sub dgData_Edit(obj as object, e as DataGridCommandEventArgs)
      FillDataGrid(e.Item.ItemIndex)
   end sub
    
   sub dgData_Delete(obj as object, e as DataGridCommandEventArgs)
      dim strSQL as string = "DELETE FROM tblUsers " & _
         "WHERE UserID = " & e.Item.ItemIndex + 1
       
      ExecuteStatement(strSQL)
       
      FillDataGrid()
   end sub
    
   sub dgData_Update(obj as object, e as DataGridCommandEventArgs)
      if UpdateDataStore(e) then
         FillDataGrid(-1)
      end if
   end sub
   
   sub dgData_Cancel(obj as object, e as DataGridCommandEventArgs)
      FillDataGrid(-1)
   end sub
    
   sub dgData_PageIndexChanged(obj as Object, e as DataGridPageChangedEventArgs)
      dgData.DataBind()
   end sub
    
   function UpdateDataStore(e as DataGridCommandEventArgs) _
      as boolean
       
      dim i,j as integer
      dim params(7) as string
      dim strText as string
      dim blnGo as boolean = true
      
      j = 0
      
      for i = 1 to e.Item.Cells.Count - 3
         strText = Ctype(e.Item.Cells(i).Controls(0), TextBox).Text
         if strText <> "" then
            params(j) = strText
            j = j + 1
         else
            blnGo = false
            lblMessage.Text = lblMessage.Text & "You forgot to enter " & _
               "a value<p>"
         end if
      next
      
      if not blnGo then
         return false
         exit function
      end if
       
      dim strSQL as string = "UPDATE tblUsers SET " & _
         "FirstName = '" & params(0) & "'," & _
         "LastName = '" & params(1) & "'," & _
         "Address = '" & params(2) & "'," & _
         "City = '" & params(3) & "'," & _
         "State = '" & params(4) & "'," & _
         "Zip = '" & params(5) & "'," & _
         "Phone = '" & params(6) & "'" & _
         " WHERE UserID = " & Ctype(e.Item.Cells(0).Controls(1), Label).text
      
      ExecuteStatement(strSQL)
      return blnGo
   end function
    
   sub FillDataGrid(Optional EditIndex as integer=-1)
      'open connection
      dim objCmd as new OleDbCommand _
         ("select * from tblUsers", Conn)
      dim objReader as OleDbDataReader
      
      try
         objCmd.Connection.Open()
         objReader = objCmd.ExecuteReader()
      catch ex as Exception
         lblMessage.Text = "Error retrieving from the database. Please" & _
            " make sure all values are correctly input"
      end try
      
      dgData.DataSource = objReader
      if not EditIndex.Equals(Nothing) then
         dgData.EditItemIndex = EditIndex
      end if
      
      dgData.DataBind()
       
      objReader.Close
      objCmd.Connection.Close()
       
   end sub
    
   function ExecuteStatement(strSQL) 
      dim objCmd as new OleDbCommand(strSQL, Conn)
      
      try
         objCmd.Connection.Open()
         objCmd.ExecuteNonQuery()
      catch ex as Exception
         lblMessage.Text = "Error updating the database. Please" & _
            " make sure all values are correctly input"
      end try
      
      objCmd.Connection.Close()
   end function
</script>

<html><body>
   <asp:Label id="lblMessage" runat="server"/>
      
   <form runat="server">
      <asp:DataGrid id="dgData" runat="server" 
         BorderColor="black" GridLines="Vertical" 
         cellpadding="4" cellspacing="0" width="100%"
         AutoGenerateColumns="False"
         OnDeleteCommand="dgData_Delete"
         OnEditCommand="dgData_Edit"
         OnCancelCommand="dgData_Cancel"
         OnUpdateCommand="dgData_Update"
         OnPageIndexChanged="dgData_PageIndexChanged" >

         <Columns>
            <asp:TemplateColumn HeaderText="ID">
              <ItemTemplate>
                 <asp:Label id="Name" runat="server" 
                    Text='<%# Container.DataItem("UserID") %>'/>
              </ItemTemplate>
            </asp:TemplateColumn>
            
            <asp:BoundColumn HeaderText="FirstName" DataField="FirstName" />
            <asp:BoundColumn HeaderText="LastName" DataField="LastName" />
            <asp:BoundColumn HeaderText="Address" DataField="Address" />
            <asp:BoundColumn HeaderText="City" DataField="City"/>
            <asp:BoundColumn HeaderText="State" DataField="State" />
            <asp:BoundColumn HeaderText="Zip" DataField="Zip" />
            <asp:BoundColumn HeaderText="Phone" DataField="Phone"/>
            
            <asp:EditCommandColumn
               EditText="Edit"
               CancelText="Cancel"
               UpdateText="Update"
               HeaderText="Edit"/>
            
            <asp:ButtonColumn HeaderText="" text="Delete"
               CommandName="delete" />
         </Columns>
      </asp:DataGrid><p>

      <asp:Panel id="AddPanel" runat="server">
         <table>
         <tr>
            <td width="100" valign="top">
               First and last name:
            </td>
            <td width="300" valign="top">
               <asp:TextBox id="tbFName" runat="server"/>
               <asp:TextBox id="tbLName" runat="server"/>
            </td>
         </tr>
         <tr>
            <td valign="top">
               Address: 
            </td>
            <td valign="top">
               <asp:TextBox id="tbAddress" 
                  runat="server" />              
            </td>
         </tr>
         <tr>
            <td valign="top">
               City, State, ZIP: 
            </td>
            <td valign="top">
               <asp:TextBox id="tbCity" 
                  runat="server" />,
               <asp:TextBox id="tbState" runat="server"
                  size=2 />&nbsp;
               <asp:TextBox id="tbZIP" runat="server"
                  size=5 />
            </td>
         </tr>
         <tr>
            <td valign="top">
               Phone:          
            </td>
            <td valign="top">
               <asp:TextBox id="tbPhone" runat="server"
                  size=11 /><p>
            </td>
         </tr>
         <tr>
            <td colspan="2" valign="top" align="right">
               <asp:Button id="btSubmit" runat="server" 
                  text="Add"
                  OnClick="Submit" />
            </td>
         </tr>
         </table>
      </asp:Panel>
   </form>
</body></html>