//vb实现数据库的连接，修改，删除，插入（ADO.Net） - 云代码 http://yuncode.net/code/c_508403a91ab6b23

Public Class 全部学生信息
  
     Dim conn As New SqlConnection
     Dim rs As New SqlDataAdapter
     'Dim dt As New DataTable
     Dim ds As New DataSet
  
     Public Function connect_db() As SqlConnection
         Dim constr As String
         constr = "Server=PC-201105311336\SQLEXPRESS;uid=sa;pwd=123456;database=jiaxiaoai"
         'rs.Fill(dataset, "student")
         conn = New SqlConnection(constr)
         conn.Open()
         connect_db = conn
     End Function
     Private Sub 全部学生信息_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load'窗口初始化函数
         Dim sql As String
         Dim dt As New DataTable
         sql = "select * from student"
         conn = connect_db()
         rs = New SqlDataAdapter(sql, conn)
         conn.Close()
         rs.Fill(dt) '填充表
         DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
         DataGridView1.Columns(0).HeaderText = "学号" '设置列名
         DataGridView1.Columns(1).HeaderText = "姓名"
         DataGridView1.Columns(2).HeaderText = "年龄"
         DataGridView1.Columns(3).HeaderText = "成绩"
         rownum.Text = Str(DataGridView1.RowCount) '获取行数
         xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
         nametext.Text = DataGridView1.Rows(0).Cells(1).Value
         If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
             agetext.Text = ""
         Else
             agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
         End If
         If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
             scoretext.Text = ""
         Else
             scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
         End If
  
     End Sub
  
  
     Private Sub selectbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handlesselectbtn.Click
         Dim sql As String
         Dim dt As New DataTable
         sql = "select * from student where name like 'l%'"
         conn = connect_db()
         rs = New SqlDataAdapter(sql, conn)
         conn.Close()
         rs.Fill(dt) '填充表
         DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
         DataGridView1.Columns(0).HeaderText = "学号" '设置列名
         DataGridView1.Columns(1).HeaderText = "姓名"
         DataGridView1.Columns(2).HeaderText = "年龄"
         DataGridView1.Columns(3).HeaderText = "成绩"
         rownum.Text = Str(DataGridView1.RowCount) '获取行数
         xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
         nametext.Text = DataGridView1.Rows(0).Cells(1).Value
         If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
             agetext.Text = ""
         Else
             agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
         End If
         If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
             scoretext.Text = ""
         Else
             scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
         End If
     End Sub
  
  
     Private Sub DataGridView1_CellMouseClick(ByVal sender As System.Object, ByVal e AsSystem.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick '鼠标点击单元格触发事件
         'DataGridView1.CurrentRow.Index获取当前单元格的行号
         xuehaotext.Text = DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value '获取datagridview中的某个值
         nametext.Text = DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(1).Value
         If (DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value = 0) Then
             agetext.Text = ""
         Else
             agetext.Text = Str(DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value)
         End If
         If (DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value = 0) Then
             scoretext.Text = ""
         Else
             scoretext.Text = Str(DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value)
         End If
     End Sub
  
     Private Sub updatebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handlesupdatebtn.Click
         Dim sql As String
         Dim row As String
         Dim dc As New SqlCommand
         Dim age
         If agetext.Text = "" Then
             age = 0
         Else
             age = Val(agetext.Text)
         End If
         sql = "update student set sid='" & xuehaotext.Text & "', name='" & nametext.Text & "',age='" & age & "',score='" & scoretext.Text & "' where sid='" & DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value & "'"
         conn = connect_db()
         dc = New SqlCommand(sql, conn)
         row = dc.ExecuteNonQuery()
         conn.Close()
         If (row > 0) Then
             MsgBox("修改成功")
         Else
             MsgBox("修改失败")
         End If
         DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value = xuehaotext.Text
         DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(1).Value = nametext.Text
         DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value = agetext.Text
         DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value = scoretext.Text
     End Sub
  
     Private Sub insertbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handlesinsertbtn.Click
         Dim sql As String
         Dim row As String
         Dim dc As New SqlCommand
         Dim age
         If agetext.Text = "" Then
             age = 0
         Else
             age = Val(agetext.Text)
         End If
         sql = "insert into student values('" & xuehaotext.Text & "', '" & nametext.Text & "','" & age &"','" & scoretext.Text & "')"
         conn = connect_db()
         dc = New SqlCommand(sql, conn)
         row = dc.ExecuteNonQuery()
         'conn.Close()
         If (row > 0) Then
             MsgBox("插入成功")
         Else
             MsgBox("插入失败")
         End If
  
         Dim dt As New DataTable
         sql = "select * from student"
         'conn = connect_db()
         rs = New SqlDataAdapter(sql, conn)
         conn.Close()
         rs.Fill(dt) '填充表
         DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
         DataGridView1.Columns(0).HeaderText = "学号" '设置列名
         DataGridView1.Columns(1).HeaderText = "姓名"
         DataGridView1.Columns(2).HeaderText = "年龄"
         DataGridView1.Columns(3).HeaderText = "成绩"
         rownum.Text = Str(DataGridView1.RowCount) '获取行数
         xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
         nametext.Text = DataGridView1.Rows(0).Cells(1).Value
         If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
             agetext.Text = ""
         Else
             agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
         End If
         If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
             scoretext.Text = ""
         Else
             scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
         End If
     End Sub
  
     Private Sub deletebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handlesdeletebtn.Click
         Dim sql As String
         Dim row As String
         Dim dc As New SqlCommand
         Dim age
         If agetext.Text = "" Then
             age = 0
         Else
             age = Val(agetext.Text)
         End If
         sql = "delete student where sid='" & DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value & "'and name='" & DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(1).Value & "'"
         conn = connect_db()
         dc = New SqlCommand(sql, conn)
         row = dc.ExecuteNonQuery()
         'conn.Close()
         If (row > 0) Then
             MsgBox("删除成功")
         Else
             MsgBox("删除失败")
         End If
         
         Dim dt As New DataTable
         sql = "select * from student"
         conn = connect_db()
         rs = New SqlDataAdapter(sql, conn)
         conn.Close()
         rs.Fill(dt) '填充表
         DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
         DataGridView1.Columns(0).HeaderText = "学号" '设置列名
         DataGridView1.Columns(1).HeaderText = "姓名"
         DataGridView1.Columns(2).HeaderText = "年龄"
         DataGridView1.Columns(3).HeaderText = "成绩"
         rownum.Text = Str(DataGridView1.RowCount) '获取行数
         xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
         nametext.Text = DataGridView1.Rows(0).Cells(1).Value
         If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
             agetext.Text = ""
         Else
             agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
         End If
         If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
             scoretext.Text = ""
         Else
             scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
         End If
  
     End Sub
 End Class