同济大学数据库作业lab10

同济大学

《数据库技术及应用》

实验报告

实验报告题目：

对象访问数据库二

姓名：学号：

年级：专业：

指导教师：

日期：2014 年10 月27 日

一．实验目的

1.学习用SQLcommand对象实现对数据库的访问

2.熟练掌握访问数据库的多种方法

二．实验内容

（实验题目+运行界面截图+实现代码）

1.完成学生成绩录入的功能界面设计

Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim mysql As String = "select snum from student"

Dim myconn As New SqlConnection("database=university;data

source=.\SQLEXPRESS;integrated security=true")

Dim myadapter As New SqlDataAdapter(mysql, myconn)

Dim mydataset As New DataSet

myadapter.Fill(mydataset, "take")

ComboBox1.DataSource = mydataset.Tables(0)

ComboBox1.DisplayMember = "snum"

End Sub

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

Dim mysql2 As String = "select secnum from sc where snum='" & ComboBox1.Text & "'"

Dim myconn As New SqlConnection("database=university;data

source=.\SQLEXPRESS;integrated security=true")

Dim myadapter2 As New SqlDataAdapter(mysql2, myconn)

Dim mydataset2 As New DataSet

myadapter2.Fill(mydataset2, "take2")

ComboBox2.DataSource = mydataset2.Tables(0)

ComboBox2.DisplayMember = "secnum"

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim myconn As New SqlConnection("database=university;data

source=.\SQLEXPRESS;integrated security=true")

Dim mysql3 As String = "insert into sc values ('" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & ComboBox2.Text & "'," & Val(TextBox1.Text) & ")"

Dim mycmd As New SqlCommand(mysql3, myconn)

myconn.Open()

Try

mycmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

myconn.Close()

End Sub

End Class

2.完成学生查询系统的功能界面设计

Imports System.Data.SqlClient

Public Class Form2

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim mysql As String = "select snum from student"

Dim myconn As New SqlConnection("database=university;data

source=.\SQLEXPRESS;integrated security=true")

Dim myadapter As New SqlDataAdapter(mysql, myconn)

Dim mydataset As New DataSet

myadapter.Fill(mydataset, "take")

ComboBox1.DataSource = mydataset.Tables(0)

ComboBox1.DisplayMember = "snum"

End Sub

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

Dim mysql2 As String = "select student.sname,count(secnum) as s1,avg(score) as s2 from student,sc where student.snum=sc.snum group by student.sname"

Dim myconn As New SqlConnection("database=university;data

source=.\SQLEXPRESS;integrated security=true")