存储过程不更新表而没有错误 [英] Stored procedure not updating table with no errors

问题描述

我试图让这个存储过程用提供的信息更新我的表。它在调试时没有引起任何错误，实际的程序在SQL服务器管理中运行正常，但是当我运行它并检查数据库时没有任何更新。



这里是我的VB代码：

I am trying to get this stored procedure to update my table with the provided information. It is causing no errors when debugging, the actual procedure works fine in SQL server management, but when I run through it and check the database nothing has been updated.

Here is my VB code behind:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ConsentAuthCode As String = Request.QueryString("rVal")
        Dim EnteredName As String = textSig.Text
        Dim EnteredEmail As String = textEmail.Text
        Dim IPaddress As String = Request.UserHostAddress()

        Dim sqlConnection1 As New SqlConnection("Data Source=(localdb)\v11.0;Initial Catalog=tempdb;Integrated Security=True")
        Dim cmd As New SqlCommand
        Dim rowsAffected As Integer

        If rbAgreement.SelectedValue = 1 And Page.IsValid Then

            cmd.CommandText = "Update_StudentConsent"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@ConsentAuthCode", ConsentAuthCode)
            cmd.Parameters.AddWithValue("@StudentEnteredName", EnteredName)
            cmd.Parameters.AddWithValue("@StudentEnteredEmail", EnteredEmail)
            cmd.Parameters.AddWithValue("@IPaddress", IPaddress)

            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            rowsAffected = cmd.ExecuteNonQuery()

            sqlConnection1.Close()

            Label1.Text = "Success"

        Else
            Label1.Text = "try again"
        End If
    End Sub

以下是程序：

And here is the procedure:

CREATE PROCEDURE [dbo].[Update_StudentConsent]

	@ConsentAuthCode UNIQUEIDENTIFIER,
	@StudentEnteredName VARCHAR(100),
	@StudentEnteredEmail VARCHAR(100),
	@IPaddress VARCHAR(20)
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE StudentConsentData
	SET ConsentProvided = 1,
		ConsentProvidedDate = GETDATE(),
		OptedOut = 0,
		OptedOutDate = Null,
		StudentEnteredName = @StudentEnteredName,
		StudentEnteredEmail = @StudentEnteredEmail,
		IPaddress = @IPaddress
	WHERE ConsentAuthCode = @ConsentAuthCode

我已经通过调试但似乎无法找到为什么存储过程没有更新数据库？

I've run through it debugging but can't seem to find why the stored procedure isn't updating the database?

推荐答案

我想问题是 @ConsentAuthCode 参数传递。

表中的 DataType 是 UNIQUEIDENTIFIER ，但是你传递 ConsentAuthCode 这是一个字符串。



您应该传递一个 Guid 而不是 String 。



示例C＃代码就像...

I guess the problem is with @ConsentAuthCode parameter passing.
The DataType in Table is UNIQUEIDENTIFIER, but you are passing ConsentAuthCode which is a String.

You should pass one Guid instead of String.

The example C# code would be like...

cmd.Parameters.Add("@ConsentAuthCode", SqlDbType.UniqueIdentifier).Value = new Guid(ConsentAuthCode);

这篇关于存储过程不更新表而没有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！