ASP.Net/ADO.net Database/Access Database — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 11:50, 26 мая 2010
Содержание
Connect to access database file (mdb) through asp page
<%@ 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>
Display data from Access in different controls
<%@ 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">
<BR><BR>
<asp:dropdownlist
id="ddlEmps"
datatextfield="EmpName"
datavaluefield="ID"
runat="server"
/>
<BR><BR>
<asp:datagrid
id="dgEmps2"
runat="server"
autogeneratecolumns="True"
>
</asp:datagrid>
<BR><BR>
<asp:datagrid
id="dgEmps3"
runat="server"
autogeneratecolumns="True"
>
</asp:datagrid>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Display data from Access to asp:Datagrid
<%@ 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">
<BR><BR>
<asp:datagrid
id="dgEmps1"
runat="server"
autogeneratecolumns="True"
>
</asp:datagrid>
<BR><BR>
<asp:datagrid
id="dgEmps2"
runat="server"
autogeneratecolumns="True"
>
</asp:datagrid>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Displaying Access Data in a DropDownList Control
<%@ 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">
<BR><BR>
<asp:dropdownlist
id="ddlEmps"
datatextfield="EmpName"
datavaluefield="ID"
runat="server"
/>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Iterating through Records in an Access Table
<%@ 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 _
& "<BR>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">
<BR><BR>
<asp:label
id="lblMessage"
runat="Server"
/>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Updating Access Data
<%@ 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
<BR><BR>
<asp:dropdownlist
id="ddlEmps"
datatextfield="EmpName"
datavaluefield="ID"
runat="server"
/>
<BR><BR>
New First Name:
<BR>
<asp:textbox
id="txtFirstName"
runat="Server"
/>
<BR><BR>
<asp:button
id="butOK"
text=" OK "
onclick="SubmitBtn_Click"
runat="server"
/>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>