VB.NET 导出数据到EXCEL中

VB.NET 导出数据到EXCEL中
[日期：2007-07-29] 来源： 作者： [字体：大 中 小]

 ----------VB.NET 导出数据到Excel中-------------

Dim oExcel As New Excel.Application
Dim obook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim DataArray(2000, 2000) As Integer
 
 
 
 
obook = oExcel.Workbooks.Open("c:/1.xls")
oSheet = obook.Worksheets(1)
oSheet.Name = "ok"
Dim myAdapter As New OleDb.OleDbDataAdapter("select * from list", Module1.dbConn)
Dim myDataSet As New DataSet
myAdapter.Fill(myDataSet, "list")
DataGrid1.DataSource = myDataSet.Tables("list")
Dim i As Integer
Dim iCount As Integer
iCount = myDataSet.Tables("list").Rows.Count
For i = 0 To iCount - 1
DataArray(i, 0) = DataGrid1.Item(i, 0)
Next
oSheet.Range("b1").Resize(100, 1).Value = DataArray
obook.Save()
oExcel.Quit()

添加数据到Excel的行（列）,源数据的数组要与Excel的行（列）对应。

上面代码使用的是DataGrid的控件，如果使用Table也是一样一样的哦

数组大小可以自己来定义，如果数据的数量大于数组的范围，那么以数组为准（谁叫要用数组输出呢 :-)）

第一次写blog，有什么写得不对的，高手请指点了

--------------完-------------------------------

 

 

使用vb.net连接到数据库并执行命令。
[日期：2007-07-29] 来源： 作者： [字体：大 中 小]

 Imports System
Imports System.Data ’引入数据类库
Imports System.Data.SqlClient ’引入SQL Server类库（用来访问SQL Server)

Module Module1
 Sub Main()
 
 
 
 
AddRecord() ’增加记录
RemoveRecord() ’删除记录
Pause()
 End Sub

 Sub Pause()
Console.WriteLine("Press Enter To Continue...")
Console.ReadLine()
 End Sub

 Sub AddRecord()
’定义连接字符串
Dim sConnectionString As String _
 = "User ID=sa;Initial Catalog=pubs;Data Source=(local)"
’定义一个SQL连接对象
Dim objConn As New SqlConnection(sConnectionString)
’连接对象
objConn.Open()
’定义一个插入记录命令字符
Dim sSQL As String = "INSERT INTO Employee " & _
 "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
 "VALUES (’MSD12923F’, ’Duncan’, ’W’, ’Mackenzie’, " & _
 "10, 82,’0877’,’2001-01-01’)"

Dim objCmd As New SqlCommand(sSQL, objConn)
Try
 objCmd.ExecuteNonQuery() ’执行插入动作
Catch e As Exception
 Console.WriteLine(e.Message) ’无法执行时提示出错信息
End Try
Console.WriteLine("Record Added")
 End Sub

 Sub RemoveRecord()
Dim sConnectionString As String _
 = "User ID=sa;Initial Catalog=pubs;Data Source=(local)"
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
Dim sSQL As String = "DELETE FROM Employee WHERE emp_id = @emp_id"
Dim objCmd As New SqlCommand(sSQL, objConn)

objCmd.Parameters.Add("@emp_id", SqlDbType.Char, 9)
objCmd.Parameters.Item("@emp_id").Value = "MSD12923F"

Try
 objCmd.ExecuteNonQuery()
 Console.WriteLine("Record Deleted")
Catch e As Exception
 Console.WriteLine(e.ToString)
End Try
Console.ReadLine()
 End Sub

End Module

 

如何将Access导入到SQL2005
[日期：2008-08-27] 来源： 作者： [字体：大 中 小]

方法一:
 在命令提示符窗口中运行 DTSWizard.exe
 SQL Server 导入和导出向导提供了生成 Microsoft SQL Server 2005 Integration Services (SSIS) 包最简单的方法。SQL Server 导入和导出向导可以访问各种数据源。可以向下列源复制数据或从其中复制数据：
 Microsoft SQL Server
 文本文件
 Microsoft Office Access
 Microsoft Office Excel
 其他 OLE DB 访问接口
 此外，可以只使用 ADO.NET 访问接口和 ODBC 数据源作为源。
 启动 SQL Server 导入和导出向导
 在 Business Intelligence Development Studio 中，右键单击“SSIS 包”文件夹，再单击“SSIS 导入和导出向导”。
 - 或 -
 在 Business Intelligence Development Studio 中的“项目”菜单上，单击“SSIS 导入和导出向导”。
 - 或 -
 在 SQL Server Management Studio 中，连接到数据库引擎服务器类型，展开数据库，右键单击一个数据库，指向“任务”，再单击“导入数据”或“导出数据”。
 - 或 -
 在命令提示符窗口中运行 DTSWizard.exe（位于 C:/Program Files/Microsoft SQL Server/90/DTS/Binn）。

 方法二:
 执行 sql 查询:
 EXEC sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
 GO
 RECONFIGURE;
 GO
 INSERT INTO 表名(字段1,字段2,字段3)
 SELECT 字段1,字段2,字段3
 FROM opendatasource( 'Microsoft.Jet.OLEDB.4.0',’Data Source="d:/Ilovedezai.mdb";Jet
 OLEDB:Database Password=密码')...表名

 

 

VB.NET操作SQL Server完全模块
[日期：2007-11-28] 来源：cnitblog.com/MTSIT 作者：未知 [字体：大 中 小]

Module ModSql

Function GetIntByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

 

End Function

 

Function GetIntByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetStrByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As String

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As String = ""

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), "", sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return Trim(rowsAffected)

End Function

 

Function GetStrByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As String

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As String = ""

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), "", sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return Trim(rowsAffected)

End Function

 

Function GetBitByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Boolean

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Boolean = False

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), False, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetBitByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Boolean

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Boolean = False

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), False, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetDateByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As DateTime

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As DateTime

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), DateAdd(DateInterval.Year, -100, Date.Now), sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetDateByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As DateTime

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As DateTime

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), DateAdd(DateInterval.Year, -100, Date.Now), sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function DelByInt(ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "DELETE FROM " & TableName & " WHERE " & ParaName & " =" & ParaValue

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function DelByStr(ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "DELETE FROM " & TableName & " WHERE " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

Function DelBySQL(ByVal StrSQL As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = StrSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

Function UpdateBitBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Boolean, ByVal ParaSQL As String) As String

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & IIf(DataFieldValue = True, 1, 0) & " WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

sqlConnection.Open()

Try

sqlCommand.ExecuteNonQuery()

Return ""

Catch ex As Exception

Return ex.Message

Finally

sqlConnection.Close()

End Try

End Function

Function UpdateStrByInt(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal Para As String, ByVal ParaValue As Integer) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & Para & " = " & ParaValue

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function UpdateStrByStr(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal Para As String, ByVal ParaValue As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & Para & " = '" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function UpdateIntByInt(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal Para As String, ByVal ParaValue As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & Para & " = " & ParaValue

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function UpdateIntByStr(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal Para As String, ByVal ParaValue As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & Para & " = '" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

 

Function UpdateIntBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal ParaSQL As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

Function UpdateStrBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal ParaSQL As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function UpdateBySQL(ByVal TableName As String, ByVal SetSQL As String, ByVal ParaSQL As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "UPDATE " & TableName & " SET " & SetSQL & " WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetDataTableByFormat(ByVal vRecordNumber As Integer, ByVal ItemSQL As String, ByVal TableName As String, ByVal ParaSQL As String) As System.Data.DataTable

 

Dim RecordNumber As String

If vRecordNumber = 0 Then

RecordNumber = ""

Else

RecordNumber = "TOP " & vRecordNumber

End If

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "SELECT " & RecordNumber & " " & ItemSQL & " FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

Dim DataSet As System.Data.DataSet = New System.Data.DataSet

Try

dataAdapter.Fill(DataSet)

Return DataSet.Tables(0)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

End Function

 

Function GetDataTableBySQL(ByVal SuperSQL As String) As System.Data.DataTable

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SuperSQL, sqlConnection)

 

Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

Dim DataSet As System.Data.DataSet = New System.Data.DataSet

Try

dataAdapter.Fill(DataSet)

Return DataSet.Tables(0)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

End Function

 

Function GetMaxField(ByVal TableName As String, ByVal FieldName As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "SELECT MAX(" & FieldName & ") AS MaxField FROM " & TableName

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetMinField(ByVal TableName As String, ByVal FieldName As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "SELECT Min(" & FieldName & ") AS MaxField FROM " & TableName

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetFieldCount(ByVal TableName As String, ByVal ParaSQL As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "SELECT COUNT(*) AS FieldCount FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteScalar

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetFieldSumByInt(ByVal TableName As String, ByVal FieldName As String, ByVal ParaSQL As String) As Integer

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "SELECT isnull(sum(" & FieldName & "),0) AS CountNumber FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Integer

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteScalar

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function GetFieldSumByDec(ByVal TableName As String, ByVal FieldName As String, ByVal ParaSQL As String) As Decimal

 

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

Dim queryString As String = "SELECT isnull(sum(" & FieldName & "),0) AS CountNumber FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

Dim rowsAffected As Decimal

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteScalar

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

 

Return rowsAffected

End Function

 

Function ChgFieldByLeftWord(ByVal TableName As String, ByVal FieldName As String, ByVal ParaWord As String, ByVal NewWord As String) As Integer

Try

Return UpdateBySQL(TableName, FieldName & "='" & NewWord & "'+right(" & FieldName & ",len(" & FieldName & ")-" & Len(ParaWord) & ")", "Len(" & FieldName & ")>=" & Len(ParaWord) & " and left(" & FieldName & "," & Len(ParaWord) & ")='" & ParaWord & "'")

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

End Try

End Function

 

'数据库连接串

Private Function GetConn() As String

Return "server=localhost;database=pubs;uid=sa;pwd="

End Function

End Module

<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<script laguage="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
dbname=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 更新数据(Access)
myCommand = New OleDbCommand( "Update Authors SET Authors='Bennett' Where Authors = 'Simson'", myConnection )
myCommand.ExecuteNonQuery()
la3.text="Record Updated!"

'4 删除数据（access）
myCommand = New OleDbCommand( "Delete FROM Authors Where Authors = 'David'", myConnection )
myCommand.ExecuteNonQuery()
la4.text="Record Deleted!"

'5 使用DateGrid显示数据
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>

 
 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
 <title>直接将Access数据库导入到Excel文件</title>
 </head>
 <body>
 <form id="form1" runat="server">
 <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="到处数据" />
 </form>
 </body>
 </html>
 VB.NET
 
 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
 Dim sql As String
 Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/ASPNET20Book.mdb;Persist Security Info=True"
 Dim cn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connstr)
 Dim cmd As System.Data.OleDb.OleDbCommand
 cn.Open
 sql = "select Count(*) From Paging"
 cmd = New System.Data.OleDb.OleDbCommand(sql, cn)
 Dim RecordCount As Integer = CType(cmd.ExecuteScalar, Integer)
 sql = "select top 65535 * into [Excel 8.0;database=" + Server.MapPath(".") + "/ASPNET20Book.xls].[Sheet1] from Paging"
 cmd = New System.Data.OleDb.OleDbCommand(sql, cn)
 cmd.ExecuteNonQuery
 cn.Close
 cn.Dispose
 cn = Nothing
 End Sub

 

Visual Basic.NET访问数据方法
[日期：2007-07-20] 来源： 作者： [字体：大 中 小]

 

 VB.NET作为VB的升级语言，提供了比VB更加强大的功能。它是一种完全面向对象的编程语言。微软对其进行了很大的改进。ADO在Visual Basic.NET中不再直接支持。访问数据有了新的变化，比以前复杂多了。签于大家比较熟悉ADO，且ADO的确简单、方便、适用。固本人利用ADO编写的访问目前市面上最广泛使用的数据库。现在接合Visual Basic.NET强大的类开发功能，本人把ADO访问数据的方法编成组件，完全封装起来，以方便大家的使用。方法如下：

 启动Visual Studio.NET。在新建项目中选择Visual Basic项目，在模板中选择类库，在名称中输入类库名称如DataAccess.确定后，则进入类库开发环境中，把Class1换名为ADOAccess。

 在项目菜单中加入引用，选择COM页，找到 Microsoft ActionX Data Object 20 Library 或更高版本确定。COM是Microsoft为了区分现在的.net，兼容以前的开发方式而设置的，凡是.net之前的组成件都可在COM页中可找到。（Microsoft ActionX Data Object 20 Library是Microsoft提供的ADO组件。它极大地方便了数据库的访问，是开发数据库有关软件的最实用的工具之一）

 在类名上面写上Imports ADODB.Connection Imports ADODB.Recordset ,Imports ADODB.CursorLocationEnum, Imports System.DBNull 4条引用语句，这里分别是引用ADO，ADO的宏定义,空值函数的来源。

 在类中定义一局部连接对象变量。 Private mCnnDB As New ADODB.Connection()
然后定义连接Access数据库的过程. Access 数据库是Microsoft开发的本地数据库,用adUseClient指定。它通过Microsoft.Jet.OLEDB数据访问方式访问数据库，Microsoft.Jet.OLEDB又有多种版本，其中4.0是最高版本，它能访问ACCESS 2000，所以这里的数据提供者指定为Microsoft.Jet.OLEDB.4.0。指定了本地数据库和提供者后，就可打开一个数据库了，用open方法实现。完整的代码如下：

''作用: 连接Access数据库
''参数: DBName 数据库名
Public Sub ConnAccess(ByVal DBName As String)
Dim strDB As String
mCnnDB.CursorLocation = adUseClient
mCnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
mCnnDB.Open(DBName, "Admin")
End Sub

 ODBC是一种广泛使用的连接多种数据库的方法，有万能钥匙之功效，但它需要通过ODBC先建立一个DNS，这里不作详细说明.有了DNS就可访问所连接的数据库。访问ODBC时先指明提供者，提供者只能为Microsoft 命名的MSDASQL,然后通过连接字符串指定数据源，用户名和密码，在下面的ConnectionString中指定，最后用open打开。由于ODBC分有用户名和无用户名两者，我们必须分别实现，借助类的函数名重载功能，我们编写两个同名的过程，完整的代码如下：

''作用: 连接ODBC数据库(不需指定用户和密码 )
''参数：dsnName为ODBC名
Public Sub ConnODBC(ByVal dsnName As String)
mCnnDB.Provider = "MSDASQL"
mCnnDB.ConnectionString = "Data Source=''" & dsnName & "''"
mCnnDB.Open()
End Sub

''作用: 连接ODBC数据库(需指定用户和密码 )
''参数：dsnName ODBC名,UserID 用户名，UserPwd 用户密码
Public Sub ConnODBC(ByVal dsnName As String, ByVal UserID As String, ByVal UserPwd As String)
mCnnDB.Provider = "MSDASQL"
mCnnDB.ConnectionString = "Data Source=''" & dsnName & "''User ID=''" & _
UserID & "'';" & "Password=''" & UserPwd & "
mCnnDB.Open()
End Sub

 SQL Server数据库是Microsoft开发的一种广泛使用的后台数据库。访问SQL Server可以通过指明ODBC驱动程序为SQL Server来实现，即在连接字符串中要有driver={SQL Server},由于它是后台数据库，所以必须指明SQL Server所在的计算机名，通常把它称为服务器，下面的ServerName就说明这点，然后指明是连接哪个数据库。其它的类似上面的ODBC。SQL Server的用户分为WIN NT 和授权用户，WIN NT用户是不需要指定用户名和密码的超级用户，否则要指明用户名和密码，这责定于SQL Server数据库管理员，在此不作详细说明，完整的代码如下：

''作用: 连接SQL Server数据库
''参数：ServerName 服务器名,DBName 数据库名
Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String)
With mCnnDB
.ConnectionString = "uid=;pwd= ;driver={SQL Server};" & _
"server=" & ServerName & _
";database=" & DBName
.Open()
End With
End Sub
''作用: 连接SQL Server数据库
''参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String,ByVal UserID As String, ByVal UserPwd As String)
With mCnnDB
.ConnectionString = "uid=’” & UserID & “’;pwd=’” & UserPwd & _
”’;driver={SQL Server};" & "server=" & ServerName & _
";database=" & DBName
.Open()
End With
End Sub

 Oracle数据库是目前最有影响的一种广泛使用的后台数据库。访问Oracle先指明其提供者MSDAORA。Oracle与Sql Server不同的是它不是通过数据库来管理的，所以它不需指明数据库，但它连接时必须指明用户，即使是超级用户也如此，这是它的安全性能高于Sql Server的理现之一，所以我们只须编写一个过程。其它类似。完整的代码如下：

''作用: 连接Oracle数据库
''参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
Public Sub ConnOracle(ByVal ServerName As String, ByVal UserID As String, ByVal UserPwd As String)
With mCnnDB
.Provider = "MSDAORA"
.ConnectionString = "User ID=''" & UserID & "'';" & "Password=''" & UserPwd & _
"'';" & "Data Source=''" & ServerName & "''"
.Open()
End With
End Sub

 有了上面的连接数据库的方法，我们就直接可读写数据了。下面利用ADO扩充读写数据的函数。

 ADO在访问表时要指明其光标类型和锁类型，且指定不同其权限就不同，权限分为读写二种，这里我们编写的是有读写权限的通用的函数，所以我们指定光标CursorType为adOpenKeyset，锁为开锁adLockOptimistic，.net需指明其来源，这是为什么开始要有 “Imports ADODB.CursorLocationEnum”语句的原因。有了这些，就可通过执行查询语句来打开一个表。打开表后，我们判断表是否为空表，不是则移动记录至尾后再现移至记录头（这是为了可以访问其中每条记录，特别是用RecordCount求记录数时不至有时返回-1的关键），最后返回一个记录集，完整的代码如下：

''作用:连接表
''参数：TableName表名
''返回：记录集
Public Function OpenTable(ByVal TableName) As ADODB.Recordset
Dim strSql As String
Dim rec As ADODB.Recordset
rec = New ADODB.Recordset()
rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
strSql = "SELECT * FROM " & TableName
rec.Open(strSql, mCnnDB) ''打开记录集
If Not rec.EOF Then
rec.MoveLast()
rec.MoveFirst()
End If
OpenTable = rec
End Function

 下面是扩充上面函数的功能，可以跟据条件访问单个表。

Public Overloads Function OpenTable(ByVal TableName As String, ByVal strWhere As String) As ADODB.Recordset
Dim strSql As String
Dim rec As ADODB.Recordset
rec = New ADODB.Recordset()
rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
strSql = "SELECT * FROM " & TableName & " where " & strWhere
rec.Open(strSql, mCnnDB) ''打开记录集
If Not rec.EOF Then
rec.MoveLast()
rec.MoveFirst()
End If
Return rec
End Function

 我们继续扩充访问表的功能。有时要打开多个表，读写其中的数据，我们可以通过建立查询视图实现，其它类似上面的OpenTable，完整的代码如下：

''作用:连接多表
''参数：strSQL
''返回：记录集
Public Function ExecuteSQL(ByVal strSql As String) As ADODB.Recordset
Dim rec As New ADODB.Recordset()
rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
rec.Open(strSql, mCnnDB) ''打开记录集
ExecuteSQL = rec
End Function

 下面编写了一个用记录集填充AxMSFlexGrid网格的过程。其中函数RecordCount是我自己编写的求记录集中记录数据的函数。这里不能直接用ADO的RecordCount求得。如果记录集是空，则退出过程。否则求出记录集的记录数和字段数据用来确定AxMSFlexGrid网格的行列数据，然后读出记录集的数据直接填充到AxMSFlexGrid网格。要说明的是读出记录集的数据时要先判断是否为空值，由函数IsDBNull实现(函数IsDBNull来源于System.DBNull).最后记录集应该返回到记录首位，否则影响了原有的记录集，完整的代码如下：

''作用：用记录集的数据填充网格
''参数：MSGrid 网格对象，rec 记录集对象
Public Sub FillMsGrid(ByVal MSGrid As AxMSFlexGridLib.AxMSFlexGrid, ByVal rec As ADODB.Recordset)
Dim i, j, RecordNum As Integer
If rec.EOF Then Exit Sub
RecordNum = RecordCount(rec)
MSGrid.Rows = RecordNum + 1
MSGrid.Cols = rec.Fields.Count + 1
For i = 0 To RecordNum - 1
For j = 0 To rec.Fields.Count - 1
If IsDBNull(rec(j).value) Then
MSGrid.set_TextMatrix(i + 1, j + 1, "")
Else
MSGrid.set_TextMatrix(i + 1, j + 1, rec(j).value)
End If
Next
MSGrid.set_TextMatrix(i + 1, 0, i)
rec.MoveNext()
Next
rec.MoveFrist()
End Sub
''作用：取记录集的记录数
''参数：rec 记录集对象
''返回：记录集的记录数

Public Function RecordCount(ByVal rec As ADODB.Recordset) As Integer
Dim i As Integer
If rec.EOF Then
RecordCount = 0
Exit Function
End If
With rec
.MoveFirst()
Do While Not .EOF
i += 1
.MoveNext()
Loop
.MoveFirst()
End With
RecordCount = i
End Function

 以上代码编好后放在所定义的类中.下面的省略号代表上面的函数和过程。可直接生成为DLL组件。方法是在.net编辑环境下选择生成菜单中按生成就生成了DLL文件。然后，你可以直接调用该组件了。

Imports ADODB.Connection
Imports ADODB.Recordset
Imports ADODB.CursorLocationEnum
Imports System.DBNull ‘函数IsDBNull的来源
Class ADOAccess
Private mCnnDB As New ADODB.Connection()
…
End Class

 调用上面生成的组件方法如下：在Visual Basic.NET中建立一项目，在窗口Form1中加入一AxMSFlexGrid网格命名为MsGrid1,然后引用刚建立的Dll。方法是选择项目菜单的添加引用，选择项目页，按浏览找到其DLL文件确定后引用完成。在Form1中定义一ADOAccess对象，在Load事件中编写调用代码，分别测试其功能，代码如下：

Public Class Form1
Inherits System.Windows.Forms.Form
Dim DB As New ADOAccess()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim rec As New ADODB.Recordset()
''DB.ConnAccess("e:/test.mdb")
''DB.ConnSQLServer("wj-1058", "test")
''DB.ConnODBC("testODBC")
''DB.ConnOracle("wj-1059", "system", "manager")
''rec = DB.OpenTable("DEMO.customer")
rec = DB.OpenTable("doc_file")
DB.FillMsGrid(MSGrid1, rec)
End Sub

 说明：

 1.你的机上要有Access数据库文件（e:/test.mdb），MS Server数据库中有数据库test，doc_file 为其test中的表，Oracle数据库中有表DEMO.customer。
　
 2. wj-1058为MS Server服务器名，wj-1059为Oracle服务器名，"system", "manager"分别为用户名及口令。

 3. 一次仅连接一种数据库。以上程序在Visual Studio.NET中调试通过。

 总结：

 上面方法介绍了刚面世不久的Visual Basic.NET中有关数据库的开发，ADO在Visual Basic.NET中的应用，连接几种最实用的数据库，且介绍了编写组件的方法。利用该知识极大地方便了软件开发者访问数据的能力，提高了开发效率。

 

Visual Basic.NET中使用ADO访问数据库
[日期：2007-07-20] 来源： 作者： [字体：大 中 小]

 .NET中以 ADO.NET取代以前的ADO。用惯ADO的程序员怎样在.NET中怎样应用ADO呢？本人经过不断的摸索，发现在.NET中ADO一样可以用，并且很好用。现在结合Visual Basic.NET强大的类开发功能，本人将ADO开发成组件使用，效果非常理想。我使用的方法如下：

 启动Visual Studio.NET。在新建项目中选择Visual Basic项目，在模板中选择类库，在名称中输入类库名称如DataAccess.确定后，则进入类库开发环境中，把Class1换名为ADOAccess。

 在项目菜单中加入引用，选择COM页，找到 Microsoft ActionX Data Object 20 Library 或更高版本。COM选项是Microsoft为了区分现在的.NET，兼容以前的开发方式而设置的，凡是.NET之前的组件都可在COM页中可找到。

 在类名上面写上Imports ADODB.Connection； Imports ADODB.Recordset ；Imports ADODB.CursorLocationEnum； Imports System.DBNull 4条引用语句，这里分别是引用ADO，ADO的宏定义,设置空值函数的来源。

 在类中定义一局部连接对象变量。 Private mCnnDB As New ADODB.Connection()
然后定义连接Access数据库的过程. Access 数据库用adUseClient指定，通过Microsoft.Jet.OLEDB数据访问方式访问数据库，Microsoft.Jet.OLEDB又有多种版本，其中4.0是最高版本，它能访问ACCESS 2000，所以这里的数据提供者指定为Microsoft.Jet.OLEDB.4.0。指定了本地数据库和提供者后，就可打开一个数据库了，用open方法实现。完整的代码如下：

''作用: 连接Access数据库
''参数: DBName 数据库名
Public Sub ConnAccess(ByVal DBName As String)
Dim strDB As String
mCnnDB.CursorLocation = adUseClient
mCnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
mCnnDB.Open(DBName, "Admin")
End Sub

 在ODBC中设置DNS,在下面的ConnectionString中指定用户名和密码，最后用open方法打开。由于ODBC分有用户名和无用户名两者，我们必须分别实现，借助类的函数名重载功能，我们编写两个同名的过程，完整的代码如下：

''作用: 连接ODBC数据库(不需指定用户和密码 )
''参数：dsnName为ODBC名
Public Sub ConnODBC(ByVal dsnName As String)
mCnnDB.Provider = "MSDASQL"
mCnnDB.ConnectionString = "Data Source=''" & dsnName & "''"
mCnnDB.Open()
End Sub

''作用: 连接ODBC数据库(需指定用户和密码 )
''参数：dsnName ODBC名,UserID 用户名，UserPwd 用户密码
Public Sub ConnODBC(ByVal dsnName As String, ByVal UserID As String, ByVal UserPwd As String) mCnnDB.Provider = "MSDASQL"
mCnnDB.ConnectionString = "Data Source=''" & dsnName & "''User ID=''" & UserID & "'';" & _
"Password=''" & UserPwd & "
mCnnDB.Open()
End Sub

 SQL Server数据库是Microsoft开发的一种广泛使用的后台数据库。访问SQL Server可以通过指明ODBC驱动程序为SQL Server来实现，即在连接字符串中要有driver={SQL Server},由于它是后台数据库，所以必须指明SQL Server所在的计算机名，通常把它称为服务器，下面的ServerName就说明这点，然后指明是连接哪个数据库。其它的类似上面的ODBC。SQL Server的用户分为WIN NT 和授权用户，WIN NT用户是不需要指定用户名和密码的超级用户，否则要指明用户名和密码，这决定于SQL Server数据库管理员，在此不作详细说明，完整的代码如下：

''作用: 连接SQL Server数据库
''参数：ServerName 服务器名,DBName 数据库名
Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String)
With mCnnDB
.ConnectionString = "uid=;pwd= ;driver={SQL Server};" & _
"server=" & ServerName & _
";database=" & DBName
.Open()
End With
End Sub
''作用: 连接SQL Server数据库
''参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String,ByVal UserID As String, ByVal UserPwd As String)
With mCnnDB
.ConnectionString = "uid=''" & UserID & "'';pwd=''" & UserPwd &"'';driver={SQL Server};" & _
"server=" & ServerName & _
";database=" & DBName
.Open()
End With
End Sub
 

 Oracle数据库是目前最有影响的一种广泛使用的后台数据库。访问Oracle先指明其提供者MSDAORA。Oracle与Sql Server不同的是它不是通过数据库来管理的，所以它不需指明数据库，但它连接时必须指明用户，即使是超级用户也如此，这是它的安全性能高于Sql Server的理现之一，所以我们只须编写一个过程。其它类似。完整的代码如下：

''作用: 连接Oracle数据库
''参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
Public Sub ConnOracle(ByVal ServerName As String, ByVal UserID As String, ByVal UserPwd As String)
With mCnnDB
.Provider = "MSDAORA"
.ConnectionString = "User ID=''" & UserID & "'';" & _
"Password=''" & UserPwd & "'';" & _
"Data Source=''" & ServerName & "''"
.Open()
End With
End Sub

 连结数据库后，就可以利用ADO扩充读写数据的函数。

 ADO在访问表时要指明其光标类型和锁类型，且指定其读写权限，这里我们编写的是有读写权限的通用的函数，所以我们指定光标CursorType为adOpenKeyset，锁为adLockOptimistic，.NET需指明其来源，这是为什么开始要有 "Imports ADODB.CursorLocationEnum"语句的原因。有了这些，就可通过执行查询语句来打开一个表。打开表后，我们判断表是否为空表，不是则移动记录至尾后再现移至记录头（这是为了可以访问其中每条记录，特别是用RecordCount求记录数时不至于返回-1的关键），最后返回一个记录集，完整的代码如下：

''作用:连接表
''参数：TableName表名
''返回：记录集
Public Function OpenTable(ByVal TableName) As ADODB.Recordset
Dim strSql As String
Dim rec As ADODB.Recordset
rec = New ADODB.Recordset()
rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
strSql = "SELECT * FROM " & TableName
rec.Open(strSql, mCnnDB) ''打开记录集
If Not rec.EOF Then
rec.MoveLast()
rec.MoveFirst()
End If
OpenTable = rec
End Function

 下面是扩充上面函数的功能，可以跟据条件访问单个表。

Public Overloads Function OpenTable(ByVal TableName As String, ByVal strWhere As String) As ADODB.Recordset
Dim strSql As String
Dim rec As ADODB.Recordset
rec = New ADODB.Recordset()
rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
strSql = "SELECT * FROM " & TableName & " where " & strWhere
rec.Open(strSql, mCnnDB) ''打开记录集
If Not rec.EOF Then
rec.MoveLast()
rec.MoveFirst()
End If
Return rec
End Function

 继续扩充访问表的功能。有时要打开多个表，读写其中的数据，我们可以通过建立查询视图实现，其它类似上面的OpenTable，完整的代码如下：

''作用:连接多表
''参数：strSQL
''返回：记录集
Public Function ExecuteSQL(ByVal strSql As String) As ADODB.Recordset
Dim rec As New ADODB.Recordset()
rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
rec.Open(strSql, mCnnDB) ''打开记录集
ExecuteSQL = rec
End Function

 下面编写了一个用记录集填充AxMSFlexGrid网格的过程。其中函数RecordCount是我自己编写的求记录集中记录的函数。这里不能直接用ADO的RecordCount求得。如果记录集是空，则退出过程。否则求出记录集的记录数和字段来确定AxMSFlexGrid网格的行列数据，然后读出记录集的数据直接填充到AxMSFlexGrid网格。要说明的是读出记录集的数据时要先判断是否为空值，由函数IsDBNull实现(函数IsDBNull来源于System.DBNull).最后记录集应该返回到记录首位，否则影响了原有的记录集，完整的代码如下：

''作用：用记录集的数据填充网格
''参数：MSGrid 网格对象，rec 记录集对象
Public Sub FillMsGrid(ByVal MSGrid As AxMSFlexGridLib.AxMSFlexGrid, ByVal rec As ADODB.Recordset)
Dim i, j, RecordNum As Integer
If rec.EOF Then Exit Sub
RecordNum = RecordCount(rec)
MSGrid.Rows = RecordNum + 1
MSGrid.Cols = rec.Fields.Count + 1
For i = 0 To RecordNum - 1
For j = 0 To rec.Fields.Count - 1
If IsDBNull(rec(j).Value) Then
MSGrid.set_TextMatrix(i + 1, j + 1, "")
Else
MSGrid.set_TextMatrix(i + 1, j + 1, rec(j).Value)
End If
Next
MSGrid.set_TextMatrix(i + 1, 0, i)
rec.MoveNext()
Next
rec.MoveFrist()
End Sub
''作用：取记录集的记录数
''参数：rec 记录集对象
''返回：记录集的记录数

Public Function RecordCount(ByVal rec As ADODB.Recordset) As Integer
Dim i As Integer
If rec.EOF Then
RecordCount = 0
Exit Function
End If
With rec
.MoveFirst()
Do While Not .EOF
i += 1
.MoveNext()
Loop
.MoveFirst()
End With
RecordCount = i
End Function
 

 以上代码编好后放在所定义的类中.下面的省略号代表上面的函数和过程。可直接生成为DLL组件。方法是在.NET编辑环境下选择生成菜单中按生成就生成了DLL文件。然后，你可以直接调用该组件了。

Imports ADODB.Connection
Imports ADODB.Recordset
Imports ADODB.CursorLocationEnum
Imports System.DBNull ''函数IsDBNull的来源
Class ADOAccess
Private mCnnDB As New ADODB.Connection()
…
End Class

 调用上面生成的组件方法如下：在Visual Basic.NET中建立一项目，在窗口Form1中加入一AxMSFlexGrid网格命名为MsGrid1,布置如下

 然后引用刚建立的Dll。方法是选择项目菜单的添加引用，选择项目页，按浏览找到其DLL文件确定后引用完成。在Form1中定义一ADOAccess对象，在Load事件中编写调用代码，分别测试其功能，代码如下：

Public Class Form1
Inherits System.Windows.Forms.Form
Dim DB As New ADOAccess()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim rec As New ADODB.Recordset()
''DB.ConnAccess("e:/test.mdb")
''DB.ConnSQLServer("wj-1058", "test")
''DB.ConnODBC("testODBC")
''DB.ConnOracle("wj-1059", "system", "manager")
''rec = DB.OpenTable("DEMO.customer")
rec = DB.OpenTable("doc_file")
DB.FillMsGrid(MSGrid1, rec)
End Sub

 说明：

 1.你的机上要有Access数据库文件（e:/test.mdb），MS Server数据库中有数据库test，doc_file 为test中的表，Oracle数据库中有表DEMO.customer

 2. wj-1058为MS Server服务器名，wj-1059为Oracle服务器名，"system", "manager"分别为用户名及口令。

 3. 一次仅连接一种数据库。

 以上程序在Visual Studio.NET中调试通过。　

 总结：上面方法介绍了刚面世不久的Visual Basic.NET中有关数据库的开发，ADO在Visual Basic.NET中的应用，连接几种最实用的数据库，且介绍了编写组件的方法。
 

 

导入Excel电子表格数据到SQL Sever数据库的方法
[日期：2007-07-22] 来源： 作者： [字体：大 中 小]

完整代码如下：
 
 <%
 'On Error Resume Next
 '导入Excel电子表格数据到SQL Sever数据库 By Dicky 2004-12-27 16:41:12
 Function Open_Conn(SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName,SqlConn)
 '创建数据库连接对象并打开数据库连接
 Dim ConnStr
 'SQL Server数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用IP）
 ConnStr = "Provider=Sqloledb; User ID=" & SqlUsername & "; Password=" & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source=" & SqlLocalName & ";"
 Set SqlConn = Server.CreateObject("ADODB.Connection")
 SqlConn.Open ConnStr
 If Err Then
 　 Err.Clear
 　 Set SqlConn = Nothing
 　 Response.Write "数据连接错误!"
 　 Response.End
 End If
 End Function
 
 Function Close_Conn(SqlConn)
 '关闭数据库连接并清除数据库连接对象
 If IsObject(SqlConn) Then
 　 SqlConn.Close
 　 Set SqlConn = Nothing
 End If
 End Function
 
 Call Open_Conn("Shat_EDG","","sa","(local)",SqlConn) '打开本地SQL Server数据库连接
 Call Open_Conn("Shat_EDG","","sa","ssh03",SqlConn1) '打开远程SQL Server数据库连接
 
 Function Get_EMP_CnName(NTACCNT)
 '根据用户NT帐号得到用户中文名
 Dim Sql1,Rs1
 Sql1 = "Select EMP_CNAME From RF_EMPLOYEE Where EMP_NTACCNT='"&NTACCNT&"'"
 Set Rs1 = Server.CreateObject("Adodb.RecordSet")
 Rs1.Open Sql1,SqlConn1,1,1
 If Rs1.Eof Then
 　 Get_EMP_CnName = ""
 Else
 　 Get_EMP_CnName = Rs1("EMP_CNAME")
 End If
 Rs1.Close
 Set Rs1 = Nothing
 End Function %>
 <html>
 <head>
 <title>导入Excel电子表格数据到SQL Sever数据库</title>
 <body bgcolor="#ACD9AF">
 <center><b>导入Excel电子表格数据到SQL Sever数据库</b></center>
 <FORM METHOD="POST" name="form1">
 請選擇數據源（本地库）:
 <SELECT NAME="Table" title="请选择需要导入数据的表">
 <option></option>
 <% Dim RsSqlDatabaseTable
 Set RsSqlDatabaseTable = SqlConn.OpenSchema(20)
 Do While Not RsSqlDatabaseTable.Eof %>
 <option<%If Trim(Request("Table"))=RsSqlDatabaseTable(2) Then Response.Write " selected"%>><%=RsSqlDatabaseTable(2)%></option>
 <% RsSqlDatabaseTable.MoveNext:Loop
 Set RsSqlDatabaseTable = Nothing %>
 </SELECT><input type=submit name=submit value="開始導出">
 <br>請選擇目標表（远程库）:
 <SELECT NAME="Table1" title="请选择需要导入数据的表">
 <option></option>
 <% Dim RsSqlDatabaseTable1
 Set RsSqlDatabaseTable1 = SqlConn1.OpenSchema(20)
 Do While Not RsSqlDatabaseTable1.Eof %>
 <option<%If Trim(Request("Table1"))=RsSqlDatabaseTable1(2) Then Response.Write " selected"%>><%=RsSqlDatabaseTable1(2)%></option>
 <% RsSqlDatabaseTable1.MoveNext:Loop
 Set RsSqlDatabaseTable1 = Nothing %>
 </SELECT>
 </FORM>
 <font color=blue>導出過程中請不要刷新頁面！</font><br>
 <%
 If Trim(Request("Table1")) <> "" Then
 Dim Sql,Rs
 Sql = "Select * From Sheet1$"　'Sheet1$是我们实现用SQL Server自身数据转换功能得到的表名
 Set Rs = SqlConn.Execute(Sql)
 If Rs.Eof And Rs.Bof Then
 　 Response.write "没有找到您需要的数据!!<br>"
 Else
 　 Do While Not Rs.Eof
 　 SqlInsert = "Insert Into "&Trim(Request("Table1"))&" (EDG_Project_Name,EDG_Project_No,EDG_Project_VM,EDG_Project_VM_CnName,EDG_Project_M,EDG_Project_M_CnName,EDG_Project_Director,EDG_Project_Director_CnName) Values ('"&Trim(Rs("工程名称"))&"','"&Trim(Rs("工程编号"))&"','"&Trim(Rs("项目副理"))&"','"&Trim(Rs("项目副理"))&"("&Get_EMP_CnName(Trim(Rs("项目副理")))&")"&"','"&Trim(Rs("项目经理"))&"','"&Trim(Rs("项目经理"))&"("&Get_EMP_CnName(Trim(Rs("项目经理")))&")"&"','"&Trim(Rs("项目总监"))&"','"&Trim(Rs("项目总监"))&"("&Get_EMP_CnName(Trim(Rs("项目总监")))&")"&"')"
 '　Response.Write SqlInsert
 　 '此处插入的值根据实际数据库结构进行调整
 　 SqlConn1.Execute(SqlInsert)'插入Excel表格裏所有東東
 　 Rs.MoveNext
 　 Loop
 　 Response.Write "<font color=red>恭喜，成功導出数据！^_^</font><br>"
 End If
 
 Rs.Close
 Set Rs = Nothing
 End If
 
 Call Close_Conn(SqlConn) '关闭Excel数据库连接
 Call Close_Conn(SqlConn1) '关闭SQL Server数据库连接 %>
 </body>
 </html>

 

<%@ Page Language="VB" Debug="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
 Dim TimeString As String = DateTime.Now.Ticks.ToString()
 Dim a As UInt32 = UInt32.Parse(TimeString.Substring(TimeString.Length - 8, 8))
 Dim b As Int32 = BitConverter.ToInt32(BitConverter.GetBytes(a), 0)
 Dim rnd As Random = New Random(b)
 Dim intRandomNumber As Integer = rnd.Next
 Response.Write(intRandomNumber)
 Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|aspxWeb.mdb;Persist Security Info=True"
 Dim sql As String = "select top 10 Title,objectGuid from Document Order By Rnd(" + (-1 * intRandomNumber).ToString() + "*id)"
 Dim cn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnectionString)
 cn.Open()
 Dim cmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(sql, cn)
 Dim dr As System.Data.OleDb.OleDbDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
 GridView1.DataSource = dr
 GridView1.DataBind()
 dr.Close()
 cmd.Dispose()
 cn.Dispose()
 cn = Nothing
 End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
 <title>随机读取Access数据库记录</title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
 <asp:HyperLinkField DataNavigateUrlFields="objectGuid" HeaderText="文章" DataTextField="Title"
 DataNavigateUrlFormatString="http://dotnet.aspx.cc/article/{0}/read.aspx" />
</Columns>
 </asp:GridView>
 </div>
 </form>
</body>
</html>

 

Partial Class Default8
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

'查询记录的方法　
'定义()
Dim MyConn As Data.SqlClient.SqlConnection
Dim MyComm As Data.SqlClient.SqlCommand

MyConn = New Data.SqlClient.SqlConnection("server=localhost;uid=sa;pwd=;database=cz2008")

Dim sqlstr As String
sqlstr = "Select * from T_bmddm where bmddm like " + "'%" + TextBox1.Text + "%'"
Page.Response.Write(sqlstr)

MyComm = New Data.SqlClient.SqlCommand(sqlstr, MyConn)
MyConn.Open()

If MyConn.State = 1 Then
Label1.Text = "The Connection State is: " & MyConn.State & " - Connection Succeeded"
Else
Label1.Text = "The Connection State is: " & MyConn.State & " - Connection Failed"
End If

Dim reader As Data.SqlClient.SqlDataReader = MyComm.ExecuteReader
'Authors.DataSource = reader

' Page.Response.Write(MyComm.ExecuteReader.Item(0))
Authors.DataSource = MyComm.ExecuteReader
Authors.DataBind()

Page.Response.Write(MyComm.Connection.DataSource)
End Sub

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
'删除记录的方法
Dim MyConn As Data.SqlClient.SqlConnection
Dim MyComm As Data.SqlClient.SqlCommand
MyConn = New Data.SqlClient.SqlConnection("server=localhost;uid=sa;pwd=;database=cz2008")
Dim sqlstr As String
sqlstr = "delete from T_bmddmwhere bmddm =" + " '" + TextBox1.Text + "'"
MyComm = New Data.SqlClient.SqlCommand(sqlstr, MyConn)
MyConn.Open()
If MyConn.State = 1 Then
Label1.Text = "The Connection State1 is: " & MyConn.State & " - Connection Succeeded"
Else
Label1.Text = "The Connection State1 is: " & MyConn.State & " - Connection Failed"
End If
MyComm.CommandText = sqlstr
MyComm.ExecuteNonQuery()
MsgBox("信息删除成功！", 0, "提示")
End Sub

Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
'实用ｓｅｓｓｉｏｎ查询记录的方法
'通过Page.Session临时记录变量
Dim MyConn As Data.SqlClient.SqlConnection
Dim MyComm As Data.SqlClient.SqlCommand

MyConn = New Data.SqlClient.SqlConnection("server=localhost;uid=sa;pwd=;database=cz2008")

Dim sqlstr As String
Dim aa As String
Page.Session("aa") = TextBox1.Text
Page.Response.Write(Page.Session("aa"))
sqlstr = "Select * from T_bmddm where bmddm like " + "'%" + TextBox1.Text + "%'"
Page.Response.Write(sqlstr)

MyComm = New Data.SqlClient.SqlCommand(sqlstr, MyConn)
MyConn.Open()

If MyConn.State = 1 Then
Label1.Text = "The Connection State is: " & MyConn.State & " - Connection Succeeded"
Else
Label1.Text = "The Connection State is: " & MyConn.State & " - Connection Failed"
End If
Authors.DataSource = MyComm.ExecuteReader
Authors.DataBind()

End Sub
Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click

'插入记录的方法
Dim sql As String
Dim MyConn As Data.SqlClient.SqlConnection
Dim MyComm As Data.SqlClient.SqlCommand
MyConn = New Data.SqlClient.SqlConnection("server=localhost;uid=sa;pwd=;database=cz2008")
Dim tBmddm, tbmd, txzqhdm, taddr, ttel, tlxr, tbmdcc, tzslbdm, tpassword, tnote, ttag, tbmdkl, tfirst_sn, tlast_sn As String

tBmddm = Bmddmtxt.Text
tbmd = bmdtxt.Text
txzqhdm = xzqhdmtxt.Text
taddr = addrTxt.Text
ttel = telTxt.Text
tlxr = lxrTxt.Text
tbmdcc = bmdccTxt.Text
tzslbdm = zslbdmTxt.Text
tpassword = passwordTxt.Text
tnote = noteTxt.Text
ttag = tagTxt.Text
tbmdkl = bmdklTxt.Text
tfirst_sn = first_snTxt.Text
tlast_sn = last_snTxt.Text

sql = "insert into T_bmddm(Bmddm,bmd,xzqhdm,addr,tel,lxr,bmdcc,zslbdm,password,note,tag,bmdkl,first_sn,last_sn) values (" + tBmddm + "," + tbmd + "," + txzqhdm + "," + taddr + "," + ttel + "," + tlxr + "," + tbmdcc + "," + tzslbdm + "," + tpassword + "," + tnote + "," + ttag + "," + tbmdkl + "," + tfirst_sn + "," + tlast_sn + ")"
MyComm = New Data.SqlClient.SqlCommand(sql, MyConn)
MyConn.Open()
MyComm.ExecuteNonQuery()
MyConn.Close()
MsgBox("信息添加成功！", 0, "提示")

End Sub

Protected Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
'修改记录的方法

Dim sql As String
Dim tBmddm, tbmd, txzqhdm As String

tBmddm = Bmddmtxt.Text
tbmd = bmdtxt.Text
txzqhdm = xzqhdmtxt.Text
Dim MyConn As Data.SqlClient.SqlConnection
Dim MyComm As Data.SqlClient.SqlCommand
MyConn = New Data.SqlClient.SqlConnection("server=localhost;uid=sa;pwd=;database=cz2008")
sql = "Update T_bmddm Set bmd=" + tbmd + " Where bmddm=" + tBmddm
MyComm = New Data.SqlClient.SqlCommand(sql, MyConn)
MyComm = New Data.SqlClient.SqlCommand(sql, MyConn)
MyConn.Open()
MyComm.ExecuteNonQuery()
MyConn.Close()
MsgBox("信息修改成功！", 0, "提示")
End Sub
End Class