ASP.Net/ADO.net Database/Access Database — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 18:30, 26 мая 2010
Содержание
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>