ASP.NET点击表单提交按钮将数据插入SQL Server数据库的实现方法

1 调整前端表单控件 #

你当前使用的普通HTML输入控件默认无法被后台直接读取取值，需要补充runat="server"属性或替换为ASP.NET服务端控件，修改后参考代码如下：

<div class="trucenter">
    <form runat="server">
        <label for="username" style="color: white;">*Username:</label>
        <input type="text" id="txtUsername" runat="server" name="username" required>

        <label for="pwd" style="color: white;">*Password:</label>
        <input type="password" id="txtPwd" runat="server" name="password" required>

        <label for="img" style="color: white;">Profile Picture:</label>
        <asp:FileUpload ID="fuPfp" runat="server" accept="image/*" style="color: white;" />

        <label for="Country" style="color: white;">*Country:</label>
        <input type="text" id="txtCountry" runat="server" name="country" required>

        <label class="label" for="leaguechoose" style="color: white;">League:</label>
        <asp:DropDownList ID="cboLeague" runat="server" Width="150px" AppendDataBoundItems="true">
            <asp:ListItem Text="请选择联赛" Value="" />
        </asp:DropDownList>

        <div style="padding: 10px">
            <asp:Button ID="SubmitID" OnClick="SubmitID_Click" Text="Submit" runat="server" />
        </div>
    </form>
</div>

2 填充联赛下拉框选项 #

在后台Page_Load事件中绑定联赛表数据，注意仅在页面首次加载时绑定，避免回发时覆盖用户已选内容：

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ' 绑定联赛下拉框
        Using conn As New SqlConnection(My.Settings.DATABASE)
            Using cmd As New SqlCommand("SELECT LeagueId, LeagueName FROM League", conn)
                conn.Open()
                Dim dt As New DataTable()
                dt.Load(cmd.ExecuteReader())
                cboLeague.DataSource = dt
                ' 显示文本和取值字段根据你的League表实际字段调整
                cboLeague.DataTextField = "LeagueName"
                cboLeague.DataValueField = "LeagueId"
                cboLeague.DataBind()
            End Using
        End Using
    End If
End Sub

3 完成提交插入逻辑 #

建议先修改Player表的PlayerId字段，添加IDENTITY(1,1)属性设为自增主键，不需要手动传值即可自动生成，避免并发冲突。提交事件参考代码如下：

Protected Sub SubmitID_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitID.Click
    ' 基础非空校验
    If String.IsNullOrWhiteSpace(txtUsername.Text) OrElse String.IsNullOrWhiteSpace(txtPwd.Text) OrElse String.IsNullOrWhiteSpace(txtCountry.Text) Then
        ' 可自行添加必填项未填提示逻辑
        Return
    End If

    ' 处理上传的头像文件
    Dim profilePic As Byte() = Nothing
    If fuPfp.HasFile Then
        profilePic = fuPfp.FileBytes
    End If

    ' 处理联赛ID，未选择则存NULL
    Dim leagueId As Object = DBNull.Value
    If Not String.IsNullOrWhiteSpace(cboLeague.SelectedValue) Then
        leagueId = Convert.ToInt32(cboLeague.SelectedValue)
    End If

    Using conn As New SqlConnection(My.Settings.DATABASE)
        ' 使用参数化SQL，避免SQL注入风险
        Dim insertSql As String = "INSERT INTO Player (Username, Password, ProfilePicture, Country, LeagueId, DateJoined) VALUES (@Username, @Password, @ProfilePicture, @Country, @LeagueId, @DateJoined)"
        Using cmdSQL As New SqlCommand(insertSql, conn)
            ' 字段类型和长度和数据库表结构对齐
            cmdSQL.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = txtUsername.Text.Trim()
            ' 注意：生产环境绝对不能存储明文密码，实际使用时请先做哈希加盐加密再存储
            cmdSQL.Parameters.Add("@Password", SqlDbType.NVarChar, 20).Value = txtPwd.Text.Trim()
            cmdSQL.Parameters.Add("@ProfilePicture", SqlDbType.Image).Value = If(profilePic IsNot Nothing, profilePic, DBNull.Value)
            cmdSQL.Parameters.Add("@Country", SqlDbType.NVarChar, 20).Value = txtCountry.Text.Trim()
            cmdSQL.Parameters.Add("@LeagueId", SqlDbType.Int).Value = leagueId
            cmdSQL.Parameters.Add("@DateJoined", SqlDbType.Date).Value = DateTime.Now.Date

            conn.Open()
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using
    ' 插入成功后可自行添加跳转、提示等逻辑
End Sub

额外注意事项 #

• 密码存储必须做哈希加盐处理，禁止直接存明文，避免数据泄露后用户账号被盗
• 可补充ASP.NET验证控件实现前后端双重校验，提升业务稳定性
• SQL Server的IMAGE类型属于即将废弃的类型，后续迭代可考虑换成VARBINARY(MAX)存储图片

内容的提问来源于stack exchange，提问作者Luke Williamson