html - 在 SQL Server 中使用存储过程从数据库中获取数据

Question

我正在设计一个网络应用程序。我已经创建了表和列。我试图使用 asp.net 插入用户详细信息。但是在 SQL Server 中不显示用户详细信息，幸运的是没有错误。谁能告诉我这是什么原因？

这是btnSave__Click的代码:

Private Sub btnSave_Click(sender As Object, e As EventArgs) HandlesbtnSave.Click
    Dim recepient As String = CheckChanges()
    Dim cn As New SqlConnection

    cn.ConnectionString = conStrDRS

    Using cn
        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand("UpdateRegistration", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Title", txtTitle.Text)

        cmd.Parameters.AddWithValue("@CounterParty", txtCounterParty.Text)
        cmd.Parameters.AddWithValue("@Company", ddlCompany.SelectedValue)
        cmd.Parameters.AddWithValue("@Custodian", ddlCustodian.SelectedValue)
        cmd.Parameters.AddWithValue("@CreatedOn", DateTime.ParseExact(YourDate, "ddd MMM dd HH:mm:ss KKKK yyyy", CultureInfo.InvariantCulture))
        cmd.Parameters.AddWithValue("@OwnerDepartment", ddlOwnerDepartment.SelectedValue)
        cmd.Parameters.AddWithValue("@CreatedBy", getUserName(hUserID.Value, "name"))
        cmd.Parameters.AddWithValue("@Status", txtStatus.Text)
        cmd.Parameters.AddWithValue("@Remarks", txtRemarks.Text)
        cmd.Parameters.AddWithValue("@SecurityLevel", ddlSecurityLevel.SelectedValue)

cm.ExecuteNonQuery() 结束使用 cn.关闭() 结束子

这是我的存储过程:

ALTER PROCEDURE [dbo].[UpdateRegistration]
   (@Title nvarchar(500),
    @CounterParty nvarchar(200),
    @Company nvarchar(10),
    @Custodian varchar(25),
    @OwnerDepartment  varchar(100),
    @Remarks nvarchar(1000),
    @Status varchar(2),
    @SecurityLevel varchar(2),
    @CreatedBy varchar(25),
    @CreatedOn datetime)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Registration(Title, Counterparty, Company,
                                 Custodian, OwnerDepartment, Remarks,
                                 Status, SecurityLevel, CreatedBy, CreatedOn)
    VALUES (@Title, @Counterparty, @Company,
            @Custodian, @OwnerDepartment, @Remarks,
            @Status, @SecurityLevel, @CreatedBy, @CreatedOn)
END

提前致谢!

Answer 1

必须执行Sql命令

您缺少 cmd.ExecuteNonQuery();

 Private Sub btnSave_Click(sender As Object, e As EventArgs) HandlesbtnSave.Click
        Dim recepient As String = CheckChanges()
         Dim cn As New SqlConnection
        cn.ConnectionString = conStrDRS
        Using cn
            cn.Open()
            Dim cmd As SqlCommand = New SqlCommand("UpdateRegistration", cn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Title", txtTitle.Text)
            cmd.Parameters.AddWithValue("@ReferenceNo", txtReferenceNo.Text)
            cmd.Parameters.AddWithValue("@CounterParty", txtCounterParty.Text)
            cmd.Parameters.AddWithValue("@Company", ddlCompany.SelectedValue)
            cmd.Parameters.AddWithValue("@Custodian", ddlCustodian.SelectedValue)
            cmd.Parameters.AddWithValue("@CreatedOn", DateTime.ParseExact(YourDate, "ddd MMM dd HH:mm:ss KKKK yyyy", CultureInfo.InvariantCulture))
            cmd.Parameters.AddWithValue("@OwnerDepartment", ddlOwnerDepartment.SelectedValue)

   string user = getUserName(hUserID.Value, "name");
   user == null ? cmd.Parameters.AddWithValue("@CreatedBy",DBNull.Value):
                 cmd.Parameters.AddWithValue("@CreatedBy",user);
            cmd.Parameters.AddWithValue("@Status", txtStatus.Text)
            cmd.Parameters.AddWithValue("@Remarks", txtRemarks.Text)
            cmd.Parameters.AddWithValue("@SecurityLevel", ddlSecurityLevel.SelectedValue)
            cmd.ExecuteNonQuery(); //Execute the stored procedure
        End Using
        cn.Close()
    End Sub