如何在VB.NET或.aspx页面绑定动态对象数据并实现成绩编辑保存到数据库

1. 固定科目实现方案（适合科目固定不变的场景） #

1.1 前端.aspx页面代码 #

<form id="form1" runat="server">
    <div>
        <p>英语：<asp:TextBox ID="txtEnglish" runat="server"></asp:TextBox></p>
        <p>数学：<asp:TextBox ID="txtMath" runat="server"></asp:TextBox></p>
        <p>科学：<asp:TextBox ID="txtScience" runat="server"></asp:TextBox></p>
        <p>历史：<asp:TextBox ID="txtHistory" runat="server"></asp:TextBox></p>
        <asp:Button ID="btnSave" runat="server" Text="保存修改" OnClick="btnSave_Click" />
        <asp:Label ID="lblMsg" runat="server" ForeColor="Green"></asp:Label>
    </div>
</form>

1.2 后端VB.NET代码实现 #

首先引入需要的命名空间：

Imports System.Data.SqlClient
Imports System.Configuration

页面加载绑定初始成绩

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ' 连接字符串建议存放在Web.config的connectionStrings节点，此处替换为你自己的配置名
        Dim connStr As String = ConfigurationManager.ConnectionStrings("你的连接配置名").ConnectionString
        Using conn As New SqlConnection(connStr)
            conn.Open()
            Dim sql As String = "SELECT Subject, Marks FROM SubjectMarks"
            Using cmd As New SqlCommand(sql, conn)
                Using dr As SqlDataReader = cmd.ExecuteReader()
                    While dr.Read()
                        Dim subject As String = dr("Subject").ToString()
                        Dim marks As String = dr("Marks").ToString()
                        Select Case subject
                            Case "英语" : txtEnglish.Text = marks
                            Case "数学" : txtMath.Text = marks
                            Case "科学" : txtScience.Text = marks
                            Case "历史" : txtHistory.Text = marks
                        End Select
                    End While
                End Using
            End Using
        End Using
    End If
End Sub

注意必须加If Not IsPostBack判断，否则每次点击保存回发时，会用数据库原始数据覆盖用户修改的内容。

保存修改的按钮点击事件

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
    ' 输入合法性校验
    Dim checkResult = CheckMarksValid()
    If checkResult <> "" Then
        lblMsg.Text = checkResult
        lblMsg.ForeColor = Drawing.Color.Red
        Return
    End If

    Dim connStr As String = ConfigurationManager.ConnectionStrings("你的连接配置名").ConnectionString
    Using conn As New SqlConnection(connStr)
        conn.Open()
        ' 开启事务保证所有科目更新同时成功或失败，避免数据不一致
        Using tran As SqlTransaction = conn.BeginTransaction()
            Try
                Dim sqlUpdate As String = "UPDATE SubjectMarks SET Marks = @Marks WHERE Subject = @Subject"
                Using cmd As New SqlCommand(sqlUpdate, conn, tran)
                    ' 参数化查询避免SQL注入风险
                    cmd.Parameters.Add("@Marks", SqlDbType.Int)
                    cmd.Parameters.Add("@Subject", SqlDbType.NVarChar, 20)

                    cmd.Parameters("@Marks").Value = Integer.Parse(txtEnglish.Text)
                    cmd.Parameters("@Subject").Value = "英语"
                    cmd.ExecuteNonQuery()

                    cmd.Parameters("@Marks").Value = Integer.Parse(txtMath.Text)
                    cmd.Parameters("@Subject").Value = "数学"
                    cmd.ExecuteNonQuery()

                    cmd.Parameters("@Marks").Value = Integer.Parse(txtScience.Text)
                    cmd.Parameters("@Subject").Value = "科学"
                    cmd.ExecuteNonQuery()

                    cmd.Parameters("@Marks").Value = Integer.Parse(txtHistory.Text)
                    cmd.Parameters("@Subject").Value = "历史"
                    cmd.ExecuteNonQuery()
                End Using
                tran.Commit()
                lblMsg.Text = "成绩保存成功"
                lblMsg.ForeColor = Drawing.Color.Green
            Catch ex As Exception
                tran.Rollback()
                lblMsg.Text = "保存失败：" & ex.Message
                lblMsg.ForeColor = Drawing.Color.Red
            End Try
        End Using
    End Using
End Sub

' 分数校验公共方法
Private Function CheckMarksValid() As String
    If Not Integer.TryParse(txtEnglish.Text, Nothing) OrElse Integer.Parse(txtEnglish.Text) < 0 OrElse Integer.Parse(txtEnglish.Text) > 100 Then
        Return "英语分数请输入0-100的有效数字"
    End If
    If Not Integer.TryParse(txtMath.Text, Nothing) OrElse Integer.Parse(txtMath.Text) < 0 OrElse Integer.Parse(txtMath.Text) > 100 Then
        Return "数学分数请输入0-100的有效数字"
    End If
    If Not Integer.TryParse(txtScience.Text, Nothing) OrElse Integer.Parse(txtScience.Text) < 0 OrElse Integer.Parse(txtScience.Text) > 100 Then
        Return "科学分数请输入0-100的有效数字"
    End If
    If Not Integer.TryParse(txtHistory.Text, Nothing) OrElse Integer.Parse(txtHistory.Text) < 0 OrElse Integer.Parse(txtHistory.Text) > 100 Then
        Return "历史分数请输入0-100的有效数字"
    End If
    Return ""
End Function

2. 动态科目实现方案（适合后续会新增科目的场景） #

无需在前端写死TextBox，用Repeater控件动态生成即可：

2.1 前端代码 #

<asp:Repeater ID="rptSubjects" runat="server">
    <ItemTemplate>
        <p>
            <asp:HiddenField ID="hfSubject" runat="server" Value='<%# Eval("Subject") %>' />
            <%# Eval("Subject") %>：<asp:TextBox ID="txtMarks" runat="server" Text='<%# Eval("Marks") %>'></asp:TextBox>
        </p>
    </ItemTemplate>
</asp:Repeater>
<asp:Button ID="btnSaveDynamic" runat="server" Text="保存成绩" OnClick="btnSaveDynamic_Click" />
<asp:Label ID="lblMsg" runat="server"></asp:Label>

2.2 后端绑定逻辑 #

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim connStr As String = ConfigurationManager.ConnectionStrings("你的连接配置名").ConnectionString
        Using conn As New SqlConnection(connStr)
            Dim sda As New SqlDataAdapter("SELECT Subject, Marks FROM SubjectMarks", conn)
            Dim dt As New DataTable()
            sda.Fill(dt)
            rptSubjects.DataSource = dt
            rptSubjects.DataBind()
        End Using
    End If
End Sub

2.3 保存逻辑 #

Protected Sub btnSaveDynamic_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim connStr As String = ConfigurationManager.ConnectionStrings("你的连接配置名").ConnectionString
    Using conn As New SqlConnection(connStr)
        conn.Open()
        Using tran As SqlTransaction = conn.BeginTransaction()
            Try
                Dim sqlUpdate As String = "UPDATE SubjectMarks SET Marks = @Marks WHERE Subject = @Subject"
                Using cmd As New SqlCommand(sqlUpdate, conn, tran)
                    cmd.Parameters.Add("@Marks", SqlDbType.Int)
                    cmd.Parameters.Add("@Subject", SqlDbType.NVarChar, 20)
                    ' 遍历所有动态生成的行
                    For Each item As RepeaterItem In rptSubjects.Items
                        Dim hfSubject As HiddenField = CType(item.FindControl("hfSubject"), HiddenField)
                        Dim txtMarks As TextBox = CType(item.FindControl("txtMarks"), TextBox)
                        ' 校验
                        If Not Integer.TryParse(txtMarks.Text, Nothing) OrElse Integer.Parse(txtMarks.Text) < 0 OrElse Integer.Parse(txtMarks.Text) > 100 Then
                            Throw New Exception(hfSubject.Value & "分数请输入0-100的有效数字")
                        End If
                        cmd.Parameters("@Marks").Value = Integer.Parse(txtMarks.Text)
                        cmd.Parameters("@Subject").Value = hfSubject.Value
                        cmd.ExecuteNonQuery()
                    Next
                End Using
                tran.Commit()
                lblMsg.Text = "保存成功"
            Catch ex As Exception
                tran.Rollback()
                lblMsg.Text = "保存失败：" & ex.Message
            End Try
        End Using
    End Using
End Sub

内容的提问来源于stack exchange，提问作者Sourav Mahajan