vb实现数据库的连接，修改，删除，插入（ADO.Net）代码,VB.Net代码库,德仔网

代码语言
代码分类
【VB.Net】 vb实现数据库的连接，修改，删除，插入（ADO.Net）
作者:Dezai.CN / 发布于2013/6/18/ 1469

	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) Handles selectbtn.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 As System.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) Handles updatebtn.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) Handles insertbtn.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) Handles deletebtn.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
试试其它关键字
　数据库的连接　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)