ASP.Net/ADO.net Database/Access Database

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Connect to access database file (mdb) through asp page

   <source lang="csharp">

<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="vb" runat="server"> Sub Page_Load(Sender As Object, E as EventArgs) 

 Dim objConnection As OleDbConnection
 Dim objCommand As OleDbDataAdapter
 Dim strConnect As String
 Dim strCommand As String
 Dim DataSet1 As New DataSet
 strConnect =  "Provider=Microsoft.Jet.OLEDB.4.0;"
 strConnect += "Data Source=yourDatabase.mdb;"
 strConnect += "Persist Security Info=False"
 strCommand = "SELECT ProductName, UnitPrice FROM products"
 objConnection = New OleDbConnection(strConnect)
 objCommand = New OleDbDataAdapter(strCommand, objConnection)
 objCommand.Fill(DataSet1, "products")
 DataGrid1.DataSource=DataSet1.Tables("Products").DefaultView
 DataGrid1.DataBind()

End Sub </script> <html> <head> <title>Data Grid Control example</title> </head> <body> <asp:DataGrid id="DataGrid1" runat="server" /> </body> </html> 

      </source>


Display data from Access in different controls

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OLEDB" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   If Not IsPostBack Then
       Dim DBConn as OleDbConnection
       Dim DBCommand As OleDbDataAdapter
       Dim DSPageData as New DataSet
       DBConn = New OleDbConnection( _
           "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
           & "DATA SOURCE=" _
           & Server.MapPath("EmployeeDatabase.mdb;"))
       DBCommand = New OleDbDataAdapter _
           ("Select LastName & ", " & FirstName " _
           & "as EmpName, ID " _
           & "From Employee " _
           & "Order By LastName, FirstName", DBConn)
       DBCommand.Fill(DSPageData, _
           "EmployeeNames")
       DBCommand = New OleDbDataAdapter _
           ("Select * from Employee", DBConn)
       DBCommand.Fill(DSPageData, _
           "EmployeesAll")
       DBCommand = New OleDbDataAdapter _
           ("Select LastName from Employee", DBConn)
       DBCommand.Fill(DSPageData, _
           "Emp3")
       ddlEmps.DataSource = _
           DSPageData.Tables("EmployeeNames").DefaultView
       ddlEmps.DataBind()
       dgEmps2.DataSource = _
           DSPageData.Tables("EmployeesAll").DefaultView
       dgEmps2.DataBind()
       dgEmps3.DataSource = _
           DSPageData.Tables("Emp3").DefaultView
       dgEmps3.DataBind()
   End If

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Retrieving Access Data</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:dropdownlist 

   id="ddlEmps"
   datatextfield="EmpName" 
   datavaluefield="ID"
   runat="server"

/>

<asp:datagrid 

   id="dgEmps2" 
   runat="server" 
   autogeneratecolumns="True"

> </asp:datagrid>

<asp:datagrid 

   id="dgEmps3" 
   runat="server" 
   autogeneratecolumns="True"

> </asp:datagrid> </form> </BODY> </HTML> 

      </source>

<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Display data from Access to asp:Datagrid

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OLEDB" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   Dim DBConn as OleDbConnection
   Dim DBCommand As OleDbDataAdapter
   Dim DSPageData as New DataSet
   DBConn = New OleDbConnection( _
       "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "DATA SOURCE=" _
       & Server.MapPath("EmployeeDatabase.mdb;"))
   DBCommand = New OleDbDataAdapter _
       ("Select * from Employee", DBConn)
   DBCommand.Fill(DSPageData, _
       "EmployeeAll")
   DBCommand = New OleDbDataAdapter _
       ("Select LastName " _
       & " from Employee", DBConn)
   DBCommand.Fill(DSPageData, _
       "Employees")
   dgEmps1.DataSource = _
       DSPageData.Tables("EmployeeAll").DefaultView
   dgEmps1.DataBind()
   dgEmps2.DataSource = _
       DSPageData.Tables("Employees").DefaultView
   dgEmps2.DataBind()

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Displaying Access Data in a DataGrid Control</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:datagrid 

   id="dgEmps1" 
   runat="server" 
   autogeneratecolumns="True"

> </asp:datagrid>

<asp:datagrid 

   id="dgEmps2" 
   runat="server" 
   autogeneratecolumns="True"

> </asp:datagrid> </form> </BODY> </HTML> 

      </source>

<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Displaying Access Data in a DropDownList Control

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OLEDB" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   If Not IsPostBack Then
       Dim DBConn as OleDbConnection
       Dim DBCommand As OleDbDataAdapter
       Dim DSPageData as New DataSet
       DBConn = New OleDbConnection( _
           "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
           & "DATA SOURCE=" _
           & Server.MapPath("EmployeeDatabase.mdb;"))
       DBCommand = New OleDbDataAdapter _
           ("Select LastName & ", " & FirstName " _
           & "as EmpName, ID " _
           & "From Employee " _
           & "Order By LastName, FirstName", DBConn)
       DBCommand.Fill(DSPageData, _
           "Employee")
       ddlEmps.DataSource = _
           DSPageData.Tables("Employee").DefaultView
       ddlEmps.DataBind()
   End If

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Displaying Access Data in a DropDownList Control</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:dropdownlist 

   id="ddlEmps"
   datatextfield="EmpName" 
   datavaluefield="ID"
   runat="server"

/> </form> </BODY> </HTML> 

      </source>

<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Iterating through Records in an Access Table

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OLEDB" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   Dim DBConn as OleDbConnection
   Dim DBCommand As OleDbDataAdapter
   Dim DSPageData as New DataSet
   Dim I as Long
   DBConn = New OleDbConnection( _
       "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "DATA SOURCE=" _
       & Server.MapPath("EmployeeDatabase.mdb;"))
   DBCommand = New OleDbDataAdapter _
       ("Select * from Employee", DBConn)
   DBCommand.Fill(DSPageData, _
       "Employee")
   For I = 0 To _
       DSPageData.Tables("Employee").Rows.Count - 1
       "Code to process record
       lblMessage.Text = lblMessage.Text _
           & "
Processed Record: " _
           & DSPageData.Tables("Employee"). _
           Rows(I).Item("ID")
   Next

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Iterating through Records in an Access Table</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:label 

   id="lblMessage"
   runat="Server"

/> </form> </BODY> </HTML> 

      </source>

<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Updating Access Data

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OLEDB" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   If Not IsPostBack Then
       Dim DBConn as OleDbConnection
       Dim DBCommand As OleDbDataAdapter
       Dim DSPageData as New DataSet
       DBConn = New OleDbConnection( _
           "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
           & "DATA SOURCE=" _
           & Server.MapPath("EmployeeDatabase.mdb;"))
       DBCommand = New OleDbDataAdapter _
           ("Select LastName & ", " & FirstName " _
           & "as EmpName, ID " _
           & "From Employee " _
           & "Order By LastName, FirstName", DBConn)
       DBCommand.Fill(DSPageData, _
           "Employee")
       ddlEmps.DataSource = _
           DSPageData.Tables("Employee").DefaultView
       ddlEmps.DataBind()
   End If

End Sub Sub SubmitBtn_Click(Sender As Object, E As EventArgs) 

   Dim DBConn as OleDbConnection
   Dim DBUpdate As New OleDbCommand
   DBConn = New OleDbConnection( _
       "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "DATA SOURCE=" _
       & Server.MapPath("EmployeeDatabase.mdb;"))
   DBUpdate.rumandText = "Update Employee Set " _
       & "FirstName = "" & txtFirstName.Text & "" Where " _
       & "ID = " & ddlEmps.SelectedItem.Value
       
   DBUpdate.Connection = DBConn
   DBUpdate.Connection.Open
   DBUpdate.ExecuteNonQuery()

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Updating Access Data</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server"> Select the Employee to Update

<asp:dropdownlist 

   id="ddlEmps"
   datatextfield="EmpName" 
   datavaluefield="ID"
   runat="server"

/>

New First Name:
<asp:textbox 

   id="txtFirstName"
   runat="Server"

/>

<asp:button 

   id="butOK"
   text="  OK  "
   onclick="SubmitBtn_Click" 
   runat="server"

/> </form> </BODY> </HTML> 

      </source>

<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>

Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/Access_Database&oldid=1573»