Imports system.Data
Imports System.Data.SqlClient
Public Class Form1
Dim objconnection As New SqlConnection("server=HUO-YA-LI-PC;database=database;Integrated Security=True")
'Provider=SQLOLEDB.1;Password=jaya2008;Persist Security Info=True;User ID=sa;Initial Catalog=yaoidian;Data Source=HUO-YA-LI-PC
Dim objdataadapter As New SqlDataAdapter("select Sno,Sname,Ssex,Sbirthday,Sid,Scollege,Sclass,Sindate from studentinfo", objconnection)
Dim objdataset As DataSet
Dim objdataview As DataView
Dim objcurrencymanager As CurrencyManager
Private Sub filldatasetandview()
objdataset = New DataSet()
objdataadapter.Fill(objdataset, "studentinfo")
objdataview = New DataView(objdataset.Tables("studentinfo"))
objcurrencymanager = CType(Me.BindingContext(objdataview), CurrencyManager)
End Sub
Private Sub bindfields()
txtsno.DataBindings.Clear()
txtsname.DataBindings.Clear()
txtssex.DataBindings.Clear()
txtsbirthday.DataBindings.Clear()
txtsid.DataBindings.Clear()
txtscollege.DataBindings.Clear()
txtsclass.DataBindings.Clear()
txtsindate.DataBindings.Clear()
txtsno.DataBindings.Add("text", objdataview, "Sno")
txtsname.DataBindings.Add("text", objdataview, "Sname")
txtssex.DataBindings.Add("text", objdataview, "Ssex")
txtsbirthday.DataBindings.Add("text", objdataview, "Sbirthday")
txtsid.DataBindings.Add("text", objdataview, "Sid")
txtscollege.DataBindings.Add("text", objdataview, "Scollege")
txtsclass.DataBindings.Add("text", objdataview, "Sclass")
txtsindate.DataBindings.Add("text", objdataview, "Sindate")
ToolStripStatusLabel1.Text = "ready"
End Sub
Private Sub showposition()
If txtsbirthday.Text <> "" And txtsindate.Text <> "" Then
Try
txtsbirthday.Text = Format(CType(txtsbirthday.Text, Date), "MM/dd/yyyy")
txtsindate.Text = Format(CType(txtsindate.Text, Date), "MM/dd/yyyy")
Catch ex As Exception
txtsbirthday.Text = ""
txtsindate.Text = ""
txtsbirthday.Text = Format(CType(txtsbirthday.Text, Date), "MM/dd/yyyy")
txtsindate.Text = Format(CType(txtsindate.Text, Date), "MM/dd/yyyy")
End Try
txtRecordPosition.Text = objcurrencymanager.Position + 1 & " of " & objcurrencymanager.Count()
txtsno.Enabled = objdataview(objcurrencymanager.Position).Row.RowState = DataRowState.Added
Else
MessageBox.Show("记录为空!无法执行转换。", "错误!")
End If
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cbofield.Items.Add("Sno")
cbofield.Items.Add("Sname")
cbofield.Items.Add("Ssex")
cbofield.Items.Add("Sbirthday")
cbofield.Items.Add("Sid")
cbofield.Items.Add("Scollege")
cbofield.Items.Add("Sclass")
cbofield.Items.Add("Sindate")
cbofield.SelectedIndex = 0
filldatasetandview()
bindfields()
showposition()
End Sub
Private Sub btnmovefirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmovefirst.Click
objcurrencymanager.Position = 0
showposition()
End Sub
Private Sub btnmoveprevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmoveprevious.Click
objcurrencymanager.Position -= 1
showposition()
End Sub
Private Sub btnmovenext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmovenext.Click
objcurrencymanager.Position += 1
showposition()
End Sub
Private Sub btnmovelast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmovelast.Click
objcurrencymanager.Position = objcurrencymanager.Count - 1
showposition()
End Sub
Private Sub btnPerformSort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPerformSort.Click
Select Case cbofield.SelectedIndex
Case 0
objdataview.Sort = "Sno"
Case 1
objdataview.Sort = "Sname"
Case 2
objdataview.Sort = "Ssex"
Case 3
objdataview.Sort = "Sbirthday"
Case 4
objdataview.Sort = "Sid"
Case 5
objdataview.Sort = "Scollege"
Case 6
objdataview.Sort = "Sclass"
Case 7
objdataview.Sort = "Sindate"
End Select
btnmovefirst_Click(Nothing, Nothing)
ToolStripStatusLabel1.Text = "Records Sorted"
End Sub
Private Sub btnperformsearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnperformsearch.Click
Dim intposition As Integer
Select Case cbofield.SelectedIndex
Case 0
objdataview.Sort = "Sno"
Case 1
objdataview.Sort = "Sname"
Case 2
objdataview.Sort = "Ssex"
Case 3
objdataview.Sort = "Sbirthday"
Case 4
objdataview.Sort = "Sid"
Case 5
objdataview.Sort = "Scollege"
Case 6
objdataview.Sort = "Sclass"
Case 7
objdataview.Sort = "Sindate"
End Select
If cbofield.SelectedIndex = 3 Or cbofield.SelectedIndex = 7 Then
intposition = objdataview.Find(CType(txtSearchCriteria.Text, Date))
Else
intposition = objdataview.Find(txtSearchCriteria.Text)
End If
If intposition = -1 Then
ToolStripStatusLabel1.Text = "Record Not Found"
Else
ToolStripStatusLabel1.Text = "Record Found"
objcurrencymanager.Position = intposition
End If
showposition()
End Sub
Private Sub btnnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnew.Click
objcurrencymanager.Position = (objcurrencymanager.Count + 1)
txtsno.Text = ""
txtsname.Text = ""
txtssex.Text = ""
txtsbirthday.Text = ""
txtsid.Text = ""
txtscollege.Text = ""
txtsclass.Text = ""
txtsindate.Text = ""
txtsno.Enabled = True
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim objcommand As SqlCommand = New SqlCommand()
Dim intposition As Integer
objconnection.Open()
objcommand.Connection = objconnection
intposition = objcurrencymanager.Position
If txtsno.Text = "" Then
MessageBox.Show("Sno不能为空", " 错误!")
Else
objcommand.CommandText = "insert into studentinfo(Sno,Sname,Ssex,Sbirthday,Sid,Scollege,Sclass,Sindate)values(@Sno,@Sname,@Ssex,@Sbirthday,@Sid,@Scollege,@Sclass,@Sindate) "
objcommand.Parameters.AddWithValue("@Sno", txtsno.Text)
objcommand.Parameters.AddWithValue("@Sname", txtsname.Text)
objcommand.Parameters.AddWithValue("@Ssex", txtssex.Text)
objcommand.Parameters.AddWithValue("@Sbirthday", txtsbirthday.Text)
objcommand.Parameters.AddWithValue("@Sid", txtsid.Text)
objcommand.Parameters.AddWithValue("@Scollege", txtscollege.Text)
objcommand.Parameters.AddWithValue("@Sclass", txtsclass.Text)
objcommand.Parameters.AddWithValue("@Sindate", txtsindate.Text)
Try
objcommand.ExecuteNonQuery()
Catch sqlexceptionerr As Exception
MessageBox.Show(sqlexceptionerr.Message)
End Try
objconnection.Close()
filldatasetandview()
bindfields()
showposition()
objcurrencymanager.Position = intposition
ToolStripStatusLabel1.Text = "Record Added"
End If
End Sub
Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
Dim intposition As Integer
Dim objcommand As SqlCommand = New SqlCommand()
intposition = objcurrencymanager.Position
objcommand.Connection = objconnection
objcommand.CommandText = "update studentinfo Set Sname = @Sname,Ssex = @Ssex,Sbirthday = @Sbirthday,Sid = @Sid,Scollege = @Scollege,Sclass = @Sclass,Sindate = @Sindate where Sno=@Sno"
objcommand.CommandType = CommandType.Text
objcommand.Parameters.AddWithValue("@Sno", txtsno.Text)
objcommand.Parameters.AddWithValue("@Sname", txtsname.Text)
objcommand.Parameters.AddWithValue("@Ssex", txtssex.Text)
objcommand.Parameters.AddWithValue("@Sbirthday", txtsbirthday.Text)
objcommand.Parameters.AddWithValue("@Sid", txtsid.Text)
objcommand.Parameters.AddWithValue("@Scollege", txtscollege.Text)
objcommand.Parameters.AddWithValue("@Sclass", txtsclass.Text)
objcommand.Parameters.AddWithValue("@Sindate", txtsindate.Text)
objconnection.Open()
objcommand.ExecuteNonQuery()
objconnection.Close()
filldatasetandview()
bindfields()
showposition()
objcurrencymanager.Position = intposition
ToolStripStatusLabel1.Text = "Record Updated"
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
Dim intposition As Integer
Dim objcommand As SqlCommand = New SqlCommand
intposition = objcurrencymanager.Count - 1
If intposition < 0 Then
intposition = 0
MessageBox.Show("无记录可删!", "错误!")
End If
objcommand.Connection = objconnection
objcommand.CommandText = "delete from studentinfo where Sno=@Sno"
objcommand.Parameters.AddWithValue("@Sno", BindingContext(objdataview).Current("Sno"))
objconnection.Open()
objcommand.ExecuteNonQuery()
objconnection.Close()
filldatasetandview()
bindfields()
showposition()
Me.BindingContext(objdataview).Position = intposition
ToolStripStatusLabel1.Text = "Record Deleted"
End Sub
End Class