在vb中将数据插入sqlclient

Question

我需要帮助。我尝试使用以下代码将数据添加到数据库中。但是数据无法保存到数据库中。这意味着在我点击"保存"按钮后，数据库中没有发生任何事情

请检查我的编码。tq

Imports System.Data.SqlClient
Public Class frmAddNewStaffAdmin
 Dim con As SqlClient.SqlConnection
 Dim dbSource As String
 Dim cmd As SqlClient.SqlCommand
 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    Try
        If Len(Trim(cboRole.Text)) = 0 Then
            MessageBox.Show("Please select user type", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            cboRole.Focus()
            Return
        ElseIf Len(Trim(txtStaffID.Text)) = 0 Then
            MessageBox.Show("Please enter Staff ID", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStaffID.Focus()
            Return
        ElseIf Len(Trim(txtStaffName.Text)) = 0 Then
            MessageBox.Show("Please enter Staff Name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStaffName.Focus()
            Return
        ElseIf Len(Trim(txtUsername.Text)) = 0 Then
            MessageBox.Show("Please enter Username", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtUsername.Focus()
            Return
        ElseIf Len(Trim(txtPassword.Text)) = 0 Then
            MessageBox.Show("Please enter Password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtPassword.Focus()
            Return
        End If
        dbSource = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|HMS.mdf;Integrated Security=True;User Instance=True"
        con = New SqlConnection(dbSource)
        con.Open()
        Dim sql As String = "INSERT INTO [User] (Staff_ID, Staff_Role, Staff_Name, Username, Password) VALUES ('" & txtStaffID.Text & "', '" & cboRole.Text & "', '" & txtStaffName.Text & "', '" & txtUsername.Text & "', '" & txtPassword.Text & "' ) "
        cmd = New SqlCommand(sql)
        cmd.Connection = con
        cmd.ExecuteReader()
        MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
        con.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
 End Sub
End Class

Answer 1

您的代码中有两个错误

更改

Dim sql As String = "INSERT INTO [User] (Staff_ID, Staff_Role, Staff_Name, Username, Password) VALUES ('" & txtStaffID.Text & "', '" & cboRole.Text & "', '" & txtStaffName.Text & "', '" & txtUsername.Text & "', '" & txtPassword.Text & "' ) "

至

Dim sql As String = "INSERT INTO [User] (Staff_ID, Staff_Role, Staff_Name, Username, [Password]) VALUES ('" & txtStaffID.Text & "', '" & cboRole.Text & "', '" & txtStaffName.Text & "', '" & txtUsername.Text & "', '" & txtPassword.Text & "' ) "

编辑：

使用类似的查询

    Dim sql As String = "INSERT INTO [User] (Staff_ID, Staff_Role, Staff_Name, Username, [Password]) VALUES (@StaffID,@Role,@StaffName,@Username,@Password) "
   Cmd.Parameters.AddWithValue("@Staff_ID", txtStaffID.Text)
   Cmd.Parameters.AddWithValue("@Role", cboRole.Text)
   Cmd.Parameters.AddWithValue("@StaffName", txtStaffName.Text)
   Cmd.Parameters.AddWithValue("@Username", txtUsername.Text )
   Cmd.Parameters.AddWithValue("@Password", txtPassword.Text)

1. 将Password更改为[Password]，因为Password是保留关键字。

2. 将cmd.ExecuteReader()更改为cmd.ExecuteNonQuery()

ExecuteNonQuery与ExecuteReader的区别

执行非查询

ExecuteNonQuery方法将返回受INSERT、DELETE或UPDATE操作。此ExecuteNonQuery方法将仅用于插入、更新和删除、创建和设置声明。(阅读有关ExecuteNonQuery的更多信息(

SqlCommand.ExecuteNonQuery MSDN文档

执行阅读器

在执行使用命令对象的SQL查询或存储过程。这个是仅向前检索记录，用于读取表从第一个到最后一个的值。(阅读更多关于ExecuteReader的信息(

SqlCommand.ExecuteReader MSDN文档

Answer 2

使用cmd.ExecuteNonQuery()而不是cmd.ExecuteReader()