⑴ vb.net資料庫操作
參考一下下面這段代碼就可以了。
Imports System.Data
'引入資料庫操作類命名空間
Imports System.Data.OleDb
'引入ADO.NET操作命名空間
Public Class FrmModifystInfo
Inherits System.Windows.Forms.Form
Public ADOcmd As OleDbDataAdapter
Public ds As DataSet = New DataSet()
'建立DataSet對象
Public mytable As Data.DataTable
'建立表單對象
Public myrow As Data.DataRow
'建立數據行對象
Public rownumber As Integer
'定義一個整型變數來存放當前行數
Public SearchSQL As String
Public cmd As OleDbCommandBuilder
'======================================================
#Region " Windows 窗體設計器生成的代碼 "
#End Region
'======================================================
Private Sub FrmModifystInfo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'窗體的載入
TxtSID.Enabled = False
TxtName.Enabled = False
ComboSex.Enabled = False
TxtBornDate.Enabled = False
TxtClassno.Enabled = False
TxtRuDate.Enabled = False
TxtTel.Enabled = False
TxtAddress.Enabled = False
TxtComment.Enabled = False '設置信息為只讀
Dim tablename As String = "student_Info "
SearchSQL = "select * from student_Info "
ExecuteSQL(SearchSQL, tablename) '打開資料庫
ShowData() '顯示記錄
End Sub
Private Sub ShowData()
'在窗口中的textbox中顯示數據
myrow = mytable.Rows.Item(rownumber)
TxtSID.Text = myrow.Item(0).ToString
TxtName.Text = myrow.Item(1).ToString
ComboSex.Text = myrow.Item(2).ToString
TxtBornDate.Text = Format(myrow.Item(3), "yyyy-MM-dd ")
TxtClassno.Text = myrow.Item(4).ToString
TxtTel.Text = myrow.Item(5).ToString
TxtRuDate.Text = Format(CDate(myrow.Item(6)), "yyyy-MM-dd ")
TxtAddress.Text = myrow.Item(7).ToString
TxtComment.Text = myrow.Item(8).ToString
End Sub
Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click
'指向第一條數據
rownumber = 0
ShowData()
End Sub
Private Sub BtPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtPrev.Click
'指向上一條數據
BtNext.Enabled = True
rownumber = rownumber - 1
If rownumber < 0 Then
rownumber = 0 '如果到達記錄的首部，行號設為零
BtPrev.Enabled = False
End If
ShowData()
End Sub
Private Sub BtNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtNext.Click
'指向上一條數據
BtPrev.Enabled = True
rownumber = rownumber + 1
If rownumber > mytable.Rows.Count - 1 Then
rownumber = mytable.Rows.Count - 1 '判斷是否到達最後一條數據
BtNext.Enabled = False
End If
ShowData()
End Sub
Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click
'指向最後一條數據
rownumber = mytable.Rows.Count - 1
ShowData()
End Sub
Private Sub BtDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtDelete.Click
mytable.Rows.Item(rownumber).Delete() '刪除記錄
If MsgBox( "確定要刪除改記錄嗎？ ", MsgBoxStyle.OKCancel + vbExclamation, "警告 ") = MsgBoxResult.OK Then
cmd = New OleDbCommandBuilder(ADOcmd)
'使用自動生成的SQL語句
ADOcmd.Update(ds, "student_Info ")
BtNext.PerformClick()
End If
End Sub
Private Sub BtModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtModify.Click
TxtSID.Enabled = False '關鍵欄位只讀
TxtName.Enabled = True '可讀寫
ComboSex.Enabled = True
TxtBornDate.Enabled = True
TxtClassno.Enabled = True
TxtRuDate.Enabled = True
TxtTel.Enabled = True
TxtAddress.Enabled = True
TxtComment.Enabled = True
End Sub
Private Sub BtUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtUpdate.Click
If Not Testtxt(TxtName.Text) Then
MsgBox( "請輸入姓名！ ", vbOKOnly + vbExclamation, "警告 ")
TxtName.Focus()
Exit Sub
End If
If Not Testtxt(ComboSex.Text) Then
MsgBox( "請選擇性別！ ", vbOKOnly + vbExclamation, "警告 ")
ComboSex.Focus()
Exit Sub
End If
If Not Testtxt(TxtClassno.Text) Then
MsgBox( "請選擇班號！ ", vbOKOnly + vbExclamation, "警告 ")
TxtClassno.Focus()
Exit Sub
End If
If Not Testtxt(TxtTel.Text) Then
MsgBox( "請輸入聯系電話！ ", vbOKOnly + vbExclamation, "警告 ")
TxtTel.Focus()
Exit Sub
End If
If Not Testtxt(TxtAddress.Text) Then
MsgBox( "請輸入家庭住址！ ", vbOKOnly + vbExclamation, "警告 ")
TxtAddress.Focus()
Exit Sub
End If
If Not IsNumeric(Trim(TxtSID.Text)) Then
MsgBox( "請輸入數字學號！ ", vbOKOnly + vbExclamation, "警告 ")
Exit Sub
TxtSID.Focus()
End If
If Not IsDate(TxtBornDate.Text) Then
MsgBox( "出生時間應輸入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")
Exit Sub
TxtBornDate.Focus()
End If
If Not IsDate(TxtRuDate.Text) Then
MsgBox( "入校時間應輸入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")
TxtRuDate.Focus()
Exit Sub
End If
myrow.Item(0) = Trim(TxtSID.Text)
myrow.Item(1) = Trim(TxtName.Text)
myrow.Item(2) = Trim(ComboSex.Text)
myrow.Item(3) = Trim(TxtBornDate.Text)
myrow.Item(4) = Trim(TxtClassno.Text)
myrow.Item(5) = Trim(TxtTel.Text)
myrow.Item(6) = Trim(TxtRuDate.Text)
myrow.Item(7) = Trim(TxtAddress.Text)
myrow.Item(8) = Trim(TxtComment.Text)
mytable.GetChanges()
cmd = New OleDbCommandBuilder(ADOcmd)
'使用自動生成的SQL語句
ADOcmd.Update(ds, "student_Info ")
'對資料庫進行更新
MsgBox( "修改學籍信息成功！ ", vbOKOnly + vbExclamation, "警告 ")
TxtName.Enabled = False
ComboSex.Enabled = False
TxtBornDate.Enabled = False
TxtClassno.Enabled = False
TxtRuDate.Enabled = False
TxtTel.Enabled = False
TxtAddress.Enabled = False
TxtComment.Enabled = False '重新設置信息為只讀
End Sub
Private Sub BtCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtCancel.Click
TxtSID.Enabled = False
TxtName.Enabled = False
ComboSex.Enabled = False
TxtBornDate.Enabled = False
TxtClassno.Enabled = False
TxtRuDate.Enabled = False
TxtTel.Enabled = False
TxtAddress.Enabled = False
TxtComment.Enabled = False
End Sub
Public Function ExecuteSQL(ByVal SQL As String, ByVal table As String)
Try
'建立ADODataSetCommand對象
'資料庫查詢函數
ADOcmd = New OleDbDataAdapter(SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\student.mdb ")
'建立ADODataSetCommand對象
ADOcmd.Fill(ds, table) '取得表單
mytable = ds.Tables.Item(0) '取得名為table的表
rownumber = 0 '設置為第一行
myrow = mytable.Rows.Item(rownumber)
'取得第一行數據
Catch
MsgBox(Err.Description)
End Try
End Function
End Class
⑵ vb.net 怎麼操作資料庫
Private stroledbprovider As String = "System.Data.OleDb" '大小寫 -------更改此處可連接不同類型的資料庫
'連接資料庫的信息，更改連接不同資料庫信息-------"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=datapath;Persist Security Info=false"
Private stroledbconn As String = "Provider=SQLOLEDB;Data Source=localhost,10000;Initial Catalog=haofefe;user id=sa ; password=123" 'Integrated Security=SSPI"
'*********************************************************************
'************生成Dbproviderfactory,idbconnection,idbcommand,and idatareader********
Dim cnfactory As IDbConnection
Dim drcustsreader As IDataReader
Dim cmfactory As IDbCommand
Dim dpfactory As DbProviderFactory
Public login As Boolean = False
Private Sub createconn()
Try
dpfactory = System.Data.Common.DbProviderFactories.GetFactory(stroledbprovider)
cnfactory = dpfactory.CreateConnection
cnfactory.ConnectionString = stroledbconn
cmfactory = cnfactory.CreateCommand
cmfactory.CommandType = CommandType.Text
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
'*********************************************************
'利用生成的連接
'****************查詢數據**************
Public Function getsources(ByVal strcomm As String) As DataTable
Dim i As Integer
Try
Call createconn() '調用生成實例
cmfactory.CommandText = strcomm
getsources = New DataTable
cnfactory.Open()
drcustsreader = cmfactory.ExecuteReader(CommandBehavior.KeyInfo)
With drcustsreader
For i = 0 To .FieldCount - 1
getsources.Columns.Add(.GetName(i))
Next
While .Read
Dim objcells(.FieldCount - 1) As Object
.GetValues(objcells)
getsources.Rows.Add(objcells)
End While
End With
drcustsreader.Close()
'getsources.Load(drcustsreader)
Return getsources
cnfactory.Close()
Catch ex As Exception
cnfactory.Close()
Return New Data.DataTable
MsgBox(ex.ToString)
End Try
End Function
'**********************************
'-------------------------------------------------------------------------------------------------------------
'*******************查看已連接信息******************
Public Sub connectionstatistics(ByVal conn As SqlConnection)
Dim htstats As Hashtable
Try
htstats = CType(conn.RetrieveStatistics, Hashtable)
Dim strstats As String
strstats = "ServerVersion: " + conn.ServerVersion.ToString + ControlChars.CrLf
Dim ostat As Object
Dim strstat As String
For Each ostat In htstats.Keys
strstat = ostat.ToString
If InStr(strstat, "Time") > 0 Then
strstats = strstats + strstat + "=" + Microsoft.VisualBasic.Format(CLng(htstats(strstat)) / 1000, "#,##0.000") + " secs" + vbCrLf
Else
strstats = strstats + strstat + "=" + htstats(strstat).ToString + ControlChars.Cr + ControlChars.Lf
End If
Next
MsgBox(strstats, MsgBoxStyle.Information, "Connection Statistics")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub