一、使用 ODBC 連接 Access 資料庫 (VB.NET)
Sub LoadCommandList()
Dim objConn As New Odbc.OdbcConnection
Dim objCmd As New Odbc.OdbcCommand
Dim objDataReader As Odbc.OdbcDataReader
CommandList.Items.Clear()
objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Application.StartupPath & "database.mdb"
Debug.Print(Application.StartupPath & "database.mdb")
objConn.Open() ' 連接資料庫
objCmd.Connection = objConn
objCmd.CommandText = "select * from Commands"
objDataReader = objCmd.ExecuteReader() ' 執行 SQL 語句
While objDataReader.Read() ' 讀取資料
CommandList.Items.Add(objDataReader.GetString(0) & "," & objDataReader.GetString(1))
End While
objConn.Close()
End Sub
二、ASP.NET 連接 SQL Server 2000 完整範例
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ds As DataSet
' 1. 連接資料庫
myConnection = New SqlConnection("server=localhost;database=Pubs;uid=ueytjdf;pwd=doekdf")
myConnection.Open()
la1.Text = "Connection Opened!"
' 2. 建立新資料表
myCommand = New SqlCommand("CREATE TABLE [test] ([id] [int] IDENTITY (1,1) NOT NULL, [name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL, [sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL)", myConnection)
myCommand.ExecuteNonQuery()
la2.Text = "New table created!"
' 3. 新增記錄
myCommand = New SqlCommand("INSERT INTO [test] (name, sex) VALUES ('黃志文', '男')", myConnection)
myCommand.ExecuteNonQuery()
la3.Text = "New Record Inserted!"
' 4. 更新資料
myCommand = New SqlCommand("UPDATE [test] SET name='Smith' WHERE name='李明'", myConnection)
myCommand.ExecuteNonQuery()
la4.Text = "Record Updated!"
' 5. 刪除資料
myCommand = New SqlCommand("DELETE FROM [test] WHERE name='Smith'", myConnection)
myCommand.ExecuteNonQuery()
la5.Text = "Record Deleted!"
' 6. 使用 DataGrid 顯示資料
myCommand = New SqlCommand("SELECT * FROM [test]", myConnection)
MyDataGrid.DataSource = myCommand.ExecuteReader()
MyDataGrid.DataBind()
End Sub
</script>
<html>
<body>
<asp:Label ID="la1" runat="server" /><br/>
<asp:Label ID="la2" runat="server" /><br/>
<asp:Label ID="la3" runat="server" /><br/>
<asp:Label ID="la4" runat="server" /><br/>
<asp:Label ID="la5" runat="server" /><br/>
<asp:DataGrid ID="MyDataGrid" runat="server"
BorderColor="black" BorderWidth="1" GridLines="Both"
CellPadding="3" CellSpacing="0"
Font-Name="Verdana" Font-Size="10pt"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="#eeeeee">
</asp:DataGrid>
</body>
</html>
三、ASP.NET 連接 Access 資料庫範例 (VB.NET)
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Sub Page_Load(sender As Object, e As EventArgs)
' 1. 連接資料庫
Dim dbName As String = Server.MapPath("authors.mdb")
myConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbName)
myConnection.Open()
la1.Text = "Connection Opened!"
' 2. 新增記錄
myCommand = New OleDbCommand("INSERT INTO Authors (Authors, country) VALUES ('Simson', 'usa')", myConnection)
myCommand.ExecuteNonQuery()
la2.Text = "New Record Inserted!"
' 3. 更新資料
myCommand = New OleDbCommand("UPDATE Authors SET Authors='Bennett' WHERE Authors='Simson'", myConnection)
myCommand.ExecuteNonQuery()
la3.Text = "Record Updated!"
' 4. 刪除資料
myCommand = New OleDbCommand("DELETE FROM Authors WHERE Authors='David'", myConnection)
myCommand.ExecuteNonQuery()
la4.Text = "Record Deleted!"
' 5. 使用 DataGrid 顯示資料
myCommand = New OleDbCommand("SELECT * FROM Authors", myConnection)
MyDataGrid.DataSource = myCommand.ExecuteReader()
MyDataGrid.DataBind()
End Sub
</script>
<html>
<body>
<asp:Label ID="la1" runat="server" /><br/>
<asp:Label ID="la2" runat="server" /><br/>
<asp:Label ID="la3" runat="server" /><br/>
<asp:Label ID="la4" runat="server" /><br/>
<asp:DataGrid ID="MyDataGrid" runat="server"
BorderColor="black" BorderWidth="1" GridLines="Both"
CellPadding="3" CellSpacing="0"
Font-Name="Verdana" Font-Size="10pt"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="#eeeeee">
</asp:DataGrid>
</body>
</html>
四、C# 連接 Access 資料庫
using System.Data;
using System.Data.OleDb;
// ...
string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\BegASPNET\Northwind.mdb";
OleDbConnection objConnection = new OleDbConnection(strConnection);
// ...
objConnection.Open();
objConnection.Close();
程式碼解析
- 命名空間匯入:連接 Access 需匯入
System.Data.OleDb,這是必備步驟。
- 連接字串:
Provider=Microsoft.Jet.OleDb.4.0 指定使用 Microsoft Jet 引擎(Access 核心);Data Source 指定資料庫檔案路徑,標準格式為 Data Source=MyDrive:MyPath\MyFile.MDB。
- @ 符號:防止字串中的反斜線被解析為跳脫字元。
- 相對路徑技巧:若資料庫與目前檔案同目錄,可用
strConnection += "Data Source=" + MapPath("Northwind.mdb") 簡化路徑。
- 分號分隔:連接字串參數間必須以分號分隔。