example.vb

asp入门到精通的源代码

Option Strict Off

Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HTMLControls
Imports System.Data
Imports System.Data.ADO

Public Class Example : Inherits Page

   'dim variables from aspx page
   public DataGrid1 as DataGrid
   public Label1 as Label
   public tbFName as TextBox
   public tbLName as TextBox
   public tbAddress as TextBox
   public tbCity as TextBox
   public tbState as TextBox
   public tbZip as TextBox
   public tbPhone as TextBox
   public Submit as button
   public Panel1 as Panel
   
   'declare connection
   dim Conn as new ADOConnection("DSN=21DaysBanking")
   
   sub Page_Load(obj as Object, e as EventArgs) 
      if Not Page.IsPostBack then
         FillDataGrid()
      end if
   end sub
   
   sub Submit_Click(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 Panel1.Controls.Count - 1
         if Panel1.controls(i).GetType = GetType(TextBox) then
            strText = Ctype(Panel1.Controls(i), TextBox).Text
            if strText <> "" then
               params(j) = strText
            else
               blnGo = false
               Label1.Text = Label1.Text & "You forgot to enter " & _
                  "a value for " & Panel1.Controls(i).ID & "<p>"
               Label1.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 DataGrid1_Edit(obj as object, e as DataGridCommandEventArgs)
       FillDataGrid(e.Item.ItemIndex)
    end sub
    
    sub DataGrid1_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 DataGrid1_Update(obj as object, e as DataGridCommandEventArgs)
       if UpdateDataStore(e) then
          FillDataGrid(-1)
       end if
    end sub
    
    sub DataGrid1_Cancel(obj as object, e as DataGridCommandEventArgs)
       FillDataGrid(-1)
    end sub
    
    sub DataGrid1_PageIndexChanged(obj as Object, e as DataGridPageChangedEventArgs)
       DataGrid1.DataBind()
    end sub
    
    private 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
             Label1.Text = Label1.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
    
    private sub FillDataGrid(Optional EditIndex as integer=-1)
       'open connection
       dim objCmd as ADOCommand = new ADOCommand _
                             ("select * from tblUsers", Conn)
       dim objReader as ADODataReader
       
       try
          objCmd.ActiveConnection.Open()
          objCmd.Execute(objReader)
       catch ex as ADOException
          Label1.Text = "Error retrieving from the database. Please" & _
             " make sure all values are correctly input"
       end try
       
       DataGrid1.DataSource = objReader
       if not EditIndex.Equals(Nothing) then
          DataGrid1.EditItemIndex = EditIndex
       end if
       
       DataGrid1.DataBind()
       
       objReader.Close
       objCmd.ActiveConnection.Close()
       
    end sub
    
    private function ExecuteStatement(strSQL) 
       dim objCmd as new ADOCommand(strSQL, Conn)
       
       try
          objCmd.ActiveConnection.Open()
          objCmd.ExecuteNonQuery()
       catch ex as ADOException
          Label1.Text = "Error updating the database. Please" & _
             " make sure all values are correctly input"
          'Label.Style("Forecolor") = "red"
       end try
       
       objCmd.ActiveConnection.Close()
    end function
    
End Class