在vb.net的datagridview中更改MS Access数据库中选定记录的值 [英] change value of selected records from ms access database in datagridview in vb.net

问题描述

我正在做vb.net中的考勤管理系统项目，我正在其中添加休假功能。我有一个表格，老师可以将出勤日期添加到数据库中，还有一个休假表格，老师可以在其中添加学生学号、课程和他将休假的日期。此休假数据库应用于将孩子的状态更改为%1。

This is leave table

This will change status of all the records

This is to change individual records

源代码

导入System.Data.OleDb

公开课出勤率

Dim con As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:UsersAttendance.accdb")

Dim con1 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:UsersStudents.accdb")

Dim con2 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:UsersAMSUsers.accdb")

Private Sub Attendance_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    con.Open()
    Dim cmd As New OleDbCommand("Select attend_date From " + ComboBox1.SelectedValue + " where attend_date = @date1", con)
    cmd.Parameters.AddWithValue("date1", DateTimePicker1.Value.Date)
        Dim myreader As OleDbDataReader = cmd.ExecuteReader
    If myreader.Read() Then
        con.Close()
        MessageBox.Show("Date and course Inserted Before")
    Else
        con.Close()
        For Each row As DataGridViewRow In DataGridView1.Rows
            Dim cmd2 As New OleDbCommand("Insert into " + ComboBox1.SelectedValue + " (stud_roll,stud_name,course_code,attend_date,status) Values(@roll,@name,@course,@date1,@status1)", con)
            cmd2.Parameters.AddWithValue("roll", row.Cells("StudRollDataGridViewTextBoxColumn").Value)
            cmd2.Parameters.AddWithValue("name", row.Cells("StudNameDataGridViewTextBoxColumn").Value)
            cmd2.Parameters.AddWithValue("course", ComboBox1.SelectedValue)
            cmd2.Parameters.AddWithValue("date1", DateTimePicker1.Value.ToShortDateString)
            cmd2.Parameters.AddWithValue("status1", row.Cells("status").Value)
            con.Open()
            cmd2.ExecuteNonQuery()
            con.Close()
        Next
        MessageBox.Show("Records inserted successfully")
        Dim i As Integer
        Dim count As Integer
        For i = 0 To DataGridView1.Rows.Count - 1
            If DataGridView1.Rows(i).Cells(2).Value = "P" Then
                count += 1
            End If
        Next
        Label3.Text = count
    End If
End Sub

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    con1.Open()
    Dim tblname = DirectCast(ComboBox1.SelectedItem, DataRowView).Row.Field(Of String)("course_code")
    Dim cmd3 As New OleDbCommand("Select stud_roll, stud_name From [" & tblname & "]", con1)
    Dim da As New OleDbDataAdapter
    da.SelectCommand = cmd3
    Dim dt As New DataTable
    dt.Clear()
    da.Fill(dt)
    DataGridView1.DataSource = dt
    con1.Close()
End Sub

Private Sub Panel2_Paint(sender As Object, e As PaintEventArgs) Handles Panel2.Click

End Sub

Private Sub Panel4_Click(sender As Object, e As PaintEventArgs) Handles Panel4.Click

End Sub

Private Sub Label7_Click(sender As Object, e As EventArgs) Handles Label7.Click
    completeattendace.Show()
End Sub

Private Sub Label8_Click(sender As Object, e As EventArgs) Handles Label8.Click
    GetAttendance.Show()
End Sub

Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged      //this is from where the staus will change in all reacords
    con.Open()
    Dim cmd As New OleDbCommand("Select roll_no from leave where #" & DateTimePicker1.Value.Date & "# between from_date and to_date", con) // the command I'm trying to develop so that the roll no of the student is selected and used to compare and change status from "P" to "L" if the date of attendance is present between from_date and to_date
    Dim i As Integer
    If ComboBox2.SelectedItem = "--All Present--" Then
        For i = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows(i).Cells(2).Value = "P"
        Next
    End If
    If ComboBox2.SelectedItem = "--All Absent--" Then
        For i = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows(i).Cells(2).Value = "A"
        Next
    End If
    If ComboBox2.SelectedItem = "--Holiday--" Then
        For i = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows(i).Cells(2).Value = "H"
        Next
    End If
    If ComboBox2.SelectedItem = "--Sunday--" Then
        For i = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows(i).Cells(2).Value = "S"
        Next
    End If
End Sub

Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
    con2.Open()
    Dim cmd As New OleDbCommand("Select course_code From courses Where semester= " + ComboBox3.SelectedItem + "", con2)
    Dim da As New OleDbDataAdapter
    da.SelectCommand = cmd
    Dim dt As New DataTable
    dt.Clear()
    da.Fill(dt)
    ComboBox1.DataSource = dt
    ComboBox1.DisplayMember = "course_code"
    ComboBox1.ValueMember = "course_code"
    con2.Close()
End Sub

Private Sub Label9_Click(sender As Object, e As EventArgs) Handles Label9.Click

End Sub

Private Sub Label10_Click(sender As Object, e As EventArgs)

End Sub

结束类

有没有什么命令或东西可以让我检查数据库中的学生学号是否在我要添加的课程中，只有当考勤日期(DatetimePicker1)介于数据库中的休假结束日期和开始日期之间时，才能将该学生的状态更改为&Q；L&Q；，直到休假日期结束。请谁帮帮我，我的大学教授不知道怎么做，但想让我做。

sql

您没有公开数据库结构(如Stackoverflw中对推荐答案问题的要求)，因此我的答案显示在一些泛型表名和字段名上。而且，我不太清楚您的问题。

要查找具有特定课程roll_number的所有学生(假定表Course、Students和StudentErols)：

SELECT ST.LastName, ST.FirstName
FROM Couses as CRS
LEFT JOIN StudentEntrols as SE on SE.CourseID = CRS.ID
LEFT JOIN Students as ST ON ST.ID = SE.StudentID 
WHERE CRS.ID = " & Me.CourseID & ";

若要检查特定学生是否在特定课程中有roll_number，请查看您是否从此修改后的查询中获得任何结果：

SELECT ST.LastName, ST.FirstName
FROM Couses as CRS
LEFT JOIN StudentEntrols as SE on SE.CourseID = CRS.ID
LEFT JOIN Students as ST ON ST.ID = SE.StudentID 
WHERE CRS.ID = " & Me.CourseID & "
  AND ST.ID = " & Me.StudentID & "

要创建已注册特定课程的所有学生的出勤率(假定表为课程、学生和出勤率)，请执行以下操作：

INSERT INTO Attendance as ATT (StudentID, CourseID)
SELECT ST.ID, CRS.ID
FROM Courses as CRS
LEFT JOIN StudentEntrols as SE on SE.CourseID = CRS.ID
LEFT JOIN Students as ST ON ST.ID = SE.StudentID 
WHERE CRS.ID = " & Me.CourseID & ";

您可以将其修改为您的数据库结构并添加参数(考虑到它只是一个访问，可能由单个员工运行，这些参数可能会过度杀伤力)。

这篇关于在vb.net的datagridview中更改MS Access数据库中选定记录的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！