连接数据库并获取单个值

Access数据库文件与.exe文件在同一目录下 ，调试时数据库放于/bin/Debug/中。

 

连接数据库时，Access 2003与Access 2007的区别仅是rovider的值不同而已。

 

 

--------------------------------------------------------------------------------

 

Imports System.IO

 

Imports System.Data.OleDb

Imports System.Data.SqlClient

 

--------------------------------------------------------------------------------

 

 

Public Class Form1

    'Microsoft SQL Server 2008

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim count As Integer

        Dim conn As SqlConnection

        Dim connStr As String = "Data Source=TALE-PC/SQLEXPRESS;Initial Catalog=Tales;User ID=sa;Password=sa"

        Dim sqlStr As String = "select count(*) as num from Users "

        conn = New SqlConnection(connStr)

        conn.Open()

        Dim cmd As SqlCommand = New SqlCommand()

        cmd.CommandText = sqlStr

        cmd.Connection = conn

        count = cmd.ExecuteScalar()

        conn.Close()

    End Sub

 

--------------------------------------------------------------------------------

 

    'Microsoft Office Access 2003

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim count As Integer

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;"

        strConnection += "Data Source="

        strConnection += Directory.GetCurrentDirectory() + ("/RankStatistics.mdb")

        Dim sqlStr As String = "select count(*) as num from KeyWord "

        Dim conn As OleDbConnection = New OleDbConnection(strConnection)

        conn.Open()

        Dim comm As OleDbCommand = New OleDbCommand()

        comm.Connection = conn

        comm.CommandText = sqlStr

        count = comm.ExecuteScalar()

        conn.Close()

    End Sub

 

--------------------------------------------------------------------------------

 

    'Microsoft Office Access 2007

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim count As Integer

        Dim strConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0;"

        strConnection += "Data Source="

        strConnection += Directory.GetCurrentDirectory() + ("/Tales.accdb")

        Dim sqlStr As String = "select count(*) as num from Users "

        Dim conn As OleDbConnection = New OleDbConnection(strConnection)

        conn.Open()

        Dim comm As OleDbCommand = New OleDbCommand()

        comm.Connection = conn

        comm.CommandText = sqlStr

        count = comm.ExecuteScalar()

        conn.Close()

    End Sub

End Class