vb.net-连接数据库实例

Imports System.Data.OleDb
Imports System.Data

Public Class Class1

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 'sql字符串
Public cmd As OleDbCommandBuilder 'sql命令建立者

'======================================================
#Region " Windows 窗体设计器生成的代码 "

'Form 重写 Dispose，以清理组件列表。
<System.Diagnostics.DebuggerNonUserCode()> _
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub

'Windows 窗体设计器所必需的
Private components As System.ComponentModel.IContainer

'注意: 以下过程是 Windows 窗体设计器所必需的
'可以使用 Windows 窗体设计器修改它。
'不要使用代码编辑器修改它。
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.TxtSID = New System.Windows.Forms.Label
Me.Label2 = New System.Windows.Forms.Label
Me.TxtName = New System.Windows.Forms.TextBox
Me.ComboSex = New System.Windows.Forms.ComboBox
Me.Label1 = New System.Windows.Forms.Label
Me.TxtBornDate = New System.Windows.Forms.TextBox
Me.Label3 = New System.Windows.Forms.Label
Me.Label4 = New System.Windows.Forms.Label
Me.TxtClassno = New System.Windows.Forms.TextBox
Me.Label5 = New System.Windows.Forms.Label
Me.TxtRuDate = New System.Windows.Forms.TextBox
Me.Label6 = New System.Windows.Forms.Label
Me.TxtTel = New System.Windows.Forms.TextBox
Me.TxtAddress = New System.Windows.Forms.TextBox
Me.Label7 = New System.Windows.Forms.Label
Me.TxtComment = New System.Windows.Forms.TextBox
Me.Label8 = New System.Windows.Forms.Label
Me.BtFirst = New System.Windows.Forms.Button
Me.BtPrev = New System.Windows.Forms.Button
Me.BtNext = New System.Windows.Forms.Button
Me.BtLastt = New System.Windows.Forms.Button
Me.BtDelete = New System.Windows.Forms.Button
Me.BtModify = New System.Windows.Forms.Button
Me.BtUpdate = New System.Windows.Forms.Button
Me.BtCancel = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(80,26)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(100,21)
Me.TextBox1.TabIndex = 0
'
'TxtSID
'
Me.TxtSID.AutoSize = True
Me.TxtSID.Location = New System.Drawing.Point(27,29)
Me.TxtSID.Name = "TxtSID"
Me.TxtSID.Size = New System.Drawing.Size(41,12)
Me.TxtSID.TabIndex = 1
Me.TxtSID.Text = "TxtSID"
'
'Label2
'
Me.Label2.AutoSize = True
Me.Label2.Location = New System.Drawing.Point(27,71)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(47,12)
Me.Label2.TabIndex = 3
Me.Label2.Text = "TxtName"
'
'TxtName
'
Me.TxtName.Location = New System.Drawing.Point(80,68)
Me.TxtName.Name = "TxtName"
Me.TxtName.Size = New System.Drawing.Size(100,21)
Me.TxtName.TabIndex = 2
'
'ComboSex
'
Me.ComboSex.FormattingEnabled = True
Me.ComboSex.Location = New System.Drawing.Point(80,114)
Me.ComboSex.Name = "ComboSex"
Me.ComboSex.Size = New System.Drawing.Size(121,20)
Me.ComboSex.TabIndex = 4
'
'Label1
'
Me.Label1.AutoSize = True
Me.Label1.Location = New System.Drawing.Point(21,117)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(53,12)
Me.Label1.TabIndex = 5
Me.Label1.Text = "ComboSex"
'
'TxtBornDate
'
Me.TxtBornDate.Location = New System.Drawing.Point(80,160)
Me.TxtBornDate.Name = "TxtBornDate"
Me.TxtBornDate.Size = New System.Drawing.Size(100,21)
Me.TxtBornDate.TabIndex = 6
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(3,160)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(71,12)
Me.Label3.TabIndex = 7
Me.Label3.Text = "TxtBornDate"
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(3,208)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(65,12)
Me.Label4.TabIndex = 9
Me.Label4.Text = "TxtClassno"
'
'TxtClassno
'
Me.TxtClassno.Location = New System.Drawing.Point(80,205)
Me.TxtClassno.Name = "TxtClassno"
Me.TxtClassno.Size = New System.Drawing.Size(100,21)
Me.TxtClassno.TabIndex = 8
'
'Label5
'
Me.Label5.AutoSize = True
Me.Label5.Location = New System.Drawing.Point(9,246)
Me.Label5.Name = "Label5"
Me.Label5.Size = New System.Drawing.Size(59,12)
Me.Label5.TabIndex = 11
Me.Label5.Text = "TxtRuDate"
'
'TxtRuDate
'
Me.TxtRuDate.Location = New System.Drawing.Point(80,243)
Me.TxtRuDate.Name = "TxtRuDate"
Me.TxtRuDate.Size = New System.Drawing.Size(100,21)
Me.TxtRuDate.TabIndex = 10
'
'Label6
'
Me.Label6.AutoSize = True
Me.Label6.Location = New System.Drawing.Point(21,289)
Me.Label6.Name = "Label6"
Me.Label6.Size = New System.Drawing.Size(41,12)
Me.Label6.TabIndex = 12
Me.Label6.Text = "TxtTel"
'
'TxtTel
'
Me.TxtTel.Location = New System.Drawing.Point(80,286)
Me.TxtTel.Name = "TxtTel"
Me.TxtTel.Size = New System.Drawing.Size(100,21)
Me.TxtTel.TabIndex = 13
'
'TxtAddress
'
Me.TxtAddress.Location = New System.Drawing.Point(80,327)
Me.TxtAddress.Name = "TxtAddress"
Me.TxtAddress.Size = New System.Drawing.Size(100,21)
Me.TxtAddress.TabIndex = 15
'
'Label7
'
Me.Label7.AutoSize = True
Me.Label7.Location = New System.Drawing.Point(9,327)
Me.Label7.Name = "Label7"
Me.Label7.Size = New System.Drawing.Size(65,12)
Me.Label7.TabIndex = 14
Me.Label7.Text = "TxtAddress"
'
'TxtComment
'
Me.TxtComment.Location = New System.Drawing.Point(80,371)
Me.TxtComment.Name = "TxtComment"
Me.TxtComment.Size = New System.Drawing.Size(100,21)
Me.TxtComment.TabIndex = 17
'
'Label8
'
Me.Label8.AutoSize = True
Me.Label8.Location = New System.Drawing.Point(9,374)
Me.Label8.Name = "Label8"
Me.Label8.Size = New System.Drawing.Size(65,12)
Me.Label8.TabIndex = 16
Me.Label8.Text = "TxtComment"
'
'BtFirst
'
Me.BtFirst.Location = New System.Drawing.Point(221,20)
Me.BtFirst.Name = "BtFirst"
Me.BtFirst.Size = New System.Drawing.Size(82,30)
Me.BtFirst.TabIndex = 18
Me.BtFirst.Text = "BtFirst"
Me.BtFirst.UseVisualStyleBackColor = True
'
'BtPrev
'
Me.BtPrev.Location = New System.Drawing.Point(221,65)
Me.BtPrev.Name = "BtPrev"
Me.BtPrev.Size = New System.Drawing.Size(82,30)
Me.BtPrev.TabIndex = 19
Me.BtPrev.Text = "BtPrev"
Me.BtPrev.UseVisualStyleBackColor = True
'
'BtNext
'
Me.BtNext.Location = New System.Drawing.Point(221,114)
Me.BtNext.Name = "BtNext"
Me.BtNext.Size = New System.Drawing.Size(82,30)
Me.BtNext.TabIndex = 20
Me.BtNext.Text = "BtNext"
Me.BtNext.UseVisualStyleBackColor = True
'
'BtLastt
'
Me.BtLastt.Location = New System.Drawing.Point(221,160)
Me.BtLastt.Name = "BtLastt"
Me.BtLastt.Size = New System.Drawing.Size(82,30)
Me.BtLastt.TabIndex = 21
Me.BtLastt.Text = "BtLastt"
Me.BtLastt.UseVisualStyleBackColor = True
'
'BtDelete
'
Me.BtDelete.Location = New System.Drawing.Point(221,208)
Me.BtDelete.Name = "BtDelete"
Me.BtDelete.Size = New System.Drawing.Size(82,30)
Me.BtDelete.TabIndex = 22
Me.BtDelete.Text = "BtDelete"
Me.BtDelete.UseVisualStyleBackColor = True
'
'BtModify
'
Me.BtModify.Location = New System.Drawing.Point(221,258)
Me.BtModify.Name = "BtModify"
Me.BtModify.Size = New System.Drawing.Size(82,30)
Me.BtModify.TabIndex = 23
Me.BtModify.Text = "BtModify"
Me.BtModify.UseVisualStyleBackColor = True
'
'BtUpdate
'
Me.BtUpdate.Location = New System.Drawing.Point(221,304)
Me.BtUpdate.Name = "BtUpdate"
Me.BtUpdate.Size = New System.Drawing.Size(82,30)
Me.BtUpdate.TabIndex = 24
Me.BtUpdate.Text = "BtUpdate"
Me.BtUpdate.UseVisualStyleBackColor = True
'
'BtCancel
'
Me.BtCancel.Location = New System.Drawing.Point(221,352)
Me.BtCancel.Name = "BtCancel"
Me.BtCancel.Size = New System.Drawing.Size(82,30)
Me.BtCancel.TabIndex = 25
Me.BtCancel.Text = "BtCancel"
Me.BtCancel.UseVisualStyleBackColor = True
'
'BtLast
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!,12.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(324,418)
Me.Controls.Add(Me.BtCancel)
Me.Controls.Add(Me.BtUpdate)
Me.Controls.Add(Me.BtModify)
Me.Controls.Add(Me.BtDelete)
Me.Controls.Add(Me.BtLastt)
Me.Controls.Add(Me.BtNext)
Me.Controls.Add(Me.BtPrev)
Me.Controls.Add(Me.BtFirst)
Me.Controls.Add(Me.TxtComment)
Me.Controls.Add(Me.Label8)
Me.Controls.Add(Me.TxtAddress)
Me.Controls.Add(Me.Label7)
Me.Controls.Add(Me.TxtTel)
Me.Controls.Add(Me.Label6)
Me.Controls.Add(Me.Label5)
Me.Controls.Add(Me.TxtRuDate)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.TxtClassno)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.TxtBornDate)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.ComboSex)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.TxtName)
Me.Controls.Add(Me.TxtSID)
Me.Controls.Add(Me.TextBox1)
Me.Name = "BtLast"
Me.Text = "BtLast"
Me.ResumeLayout(False)
Me.PerformLayout()

End Sub

Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents TxtSID As System.Windows.Forms.Label
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents TxtName As System.Windows.Forms.TextBox
Friend WithEvents ComboSex As System.Windows.Forms.ComboBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents TxtBornDate As System.Windows.Forms.TextBox
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents TxtClassno As System.Windows.Forms.TextBox
Friend WithEvents Label5 As System.Windows.Forms.Label
Friend WithEvents TxtRuDate As System.Windows.Forms.TextBox
Friend WithEvents Label6 As System.Windows.Forms.Label
Friend WithEvents TxtTel As System.Windows.Forms.TextBox
Friend WithEvents TxtAddress As System.Windows.Forms.TextBox
Friend WithEvents Label7 As System.Windows.Forms.Label
Friend WithEvents TxtComment As System.Windows.Forms.TextBox
Friend WithEvents Label8 As System.Windows.Forms.Label
Friend WithEvents BtFirst As System.Windows.Forms.Button
Friend WithEvents BtPrev As System.Windows.Forms.Button
Friend WithEvents BtNext As System.Windows.Forms.Button
Friend WithEvents BtLastt As System.Windows.Forms.Button
Friend WithEvents BtDelete As System.Windows.Forms.Button
Friend WithEvents BtModify As System.Windows.Forms.Button
Friend WithEvents BtUpdate As System.Windows.Forms.Button
Friend WithEvents BtCancel As System.Windows.Forms.Button

#End Region
'======================================================

Private Sub Class1_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"
SearchSQL = "select * from Student"
ExecuteSQL(SearchSQL,tablename) '打开数据库,取得结果集的第一行
ShowDate() '在窗体的textbox中显示数据

End Sub

Private Sub ShowDate()
'在窗体的textbox中显示数据
myrow = mytable.Rows.Item(rownumber) '取得第n行数据
TxtSID.Text = myrow.Item(0).ToString
TxtName.Text = myrow.Item(1).ToString
ComboSex.Text = myrow.Item(2).ToString
TxtBornDate.Text = myrow.Item(3).ToString
TxtClassno.Text = myrow.Item(4).ToString
TxtTel.Text = myrow.Item(5).ToString
TxtRuDate.Text = myrow.Item(6).ToString
TxtAddress.Text = myrow.Item(7).ToString
TxtComment.Text = myrow.Item(8).ToString

End Sub

Public Function ExecuteSQL(ByVal SQL As String,ByVal table As String)
Try
'建立ADODataSetCommand对象
'数据库查询函数
ADOcmd = New OleDbDataAdapter(SQL,"Provider=SQLOLEDB;SERVER=XP-201206180939;uid=sa;pwd=5201314;database=Student_Info")

'建立ADODataSetCommand对象
ADOcmd.Fill(ds,table) '取得表单
mytable = ds.Tables.Item(0) '取得名为table的表
rownumber = 0 '设置成第一行
myrow = mytable.Rows.Item(rownumber) '取得第一行数据
Return True
Catch
MsgBox(Err.Description)
Return False
End Try
End Function

Private Sub BtFirst_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles BtFirst.Click
'指向第一条数据
rownumber = 0
ShowDate()

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 '如果达到记录的首部，行号设为0
BtPrev.Enabled = False
End If
ShowDate()
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
ShowDate()
End Sub

Private Sub BtLastt_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles BtLastt.Click
'指向最后一条数据
rownumber = mytable.Rows.Count - 1
ShowDate()

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")
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("请选择性别！ ","警告 ")
' ComboSex.Focus()
' Exit Sub
'End If
'If Not Testtxt(TxtClassno.Text) Then
' MsgBox("请选择班号！ ","警告 ")
' TxtClassno.Focus()
' Exit Sub
'End If

'If Not Testtxt(TxtTel.Text) Then
' MsgBox("请输入联系电话！ ","警告 ")
' TxtTel.Focus()
' Exit Sub
'End If
'If Not Testtxt(TxtAddress.Text) Then
' MsgBox("请输入家庭住址！ ","警告 ")
' TxtAddress.Focus()
' Exit Sub
'End If
'If Not IsNumeric(Trim(TxtSID.Text)) Then
' MsgBox("请输入数字学号！ ","警告 ")
' Exit Sub
' TxtSID.Focus()
'End If
If Not IsDate(TxtBornDate.Text) Then
MsgBox("出生时间应输入日期格式（yyyy-mm-dd）！ ","警告 ")
Exit Sub
TxtBornDate.Focus()
End If
If Not IsDate(TxtRuDate.Text) Then
MsgBox("入校时间应输入日期格式（yyyy-mm-dd）！ ","警告 ")
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)
ADOcmd.Update(ds,"Student")
MsgBox("修改学籍信息成功！ ","警告 ")
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

End Class