来自存储过程的更新工作，但不能从UI工作

Question

我的Submit Button操作:

protected void submit_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=172.25.192.80;Initial Catalog=DB01HMS001;User ID=pj01hms001;Password=tcshyd");
        con.Open();

        SqlCommand cmd = new SqlCommand("usp_team5_customerupdate", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Title", Convert.ToString(DropDownList1.SelectedValue));
        cmd.Parameters.AddWithValue("@Lastname",lstname.Text);
        cmd.Parameters.AddWithValue("@StreetAddress", streetadd.Text);
        cmd.Parameters.AddWithValue("@EmailAddress", Eaddress.Text);
        cmd.Parameters.AddWithValue("@State", txtstate.Text);
        cmd.Parameters.AddWithValue("@TownorCity",towncity.Text);
        cmd.Parameters.AddWithValue("@Zipcode", Convert.ToInt32(txtzipcode.Text));
        cmd.Parameters.AddWithValue("@MobileNumber",Convert.ToInt64(mblenum.Text));
        cmd.Parameters.AddWithValue("@PhoneNumber", Convert.ToInt64(phnenum.Text));
        cmd.Parameters.AddWithValue("@CompanyName", cname.Text);
        cmd.Parameters.AddWithValue("@OfficeAddress", Oaddress.Text);
        cmd.Parameters.AddWithValue("@custid", Convert.ToInt64(Session["cusid"]));
        Response.Write(Convert.ToString(Session["cusid"]));
        int a = cmd.ExecuteNonQuery();
        if (a == 1) {
            Response.Write("Success");
        }
        else
        {
            Response.Write("Failure");
        }
        con.Close();
    }

My Table Schema:

CREATE TABLE [dbo].[team5_customerprofile] (
[CustomerProfileID]       INT           IDENTITY (1001, 1) NOT NULL,
[CustomerID]              AS            (CONVERT([bigint],CONVERT([varchar],[CustomerProfileID],0)+CONVERT([varchar](15),getdate(),(112)),0)),
[Password]                VARCHAR (20)  NULL,
[Title]                   VARCHAR (20)  NULL,
[Firstname]               VARCHAR (20)  NULL,
[Lastname]                VARCHAR (20)  NULL,
[DateOfBirth]             DATE          NULL,
[Gender]                  BIT           NULL,
[StreetAddress]           VARCHAR (100) NULL,
[Nationality]             VARCHAR (20)  NULL,
[State]                   VARCHAR (30)  NULL,
[TownorCity]              VARCHAR (50)  NULL,
[Zipcode]                 INT           NULL,
[MobileNumber]            BIGINT        NULL,
[AlternatePhone]          BIGINT        NULL,
[PhoneNumber]             BIGINT        NULL,
[EmailAddress]            VARCHAR (30)  NULL,
[CompanyName]             VARCHAR (50)  NULL,
[OfficeAddress]           VARCHAR (100) NULL,
[TotalBonusMilesAcheived] INT           DEFAULT ((0)) NULL,
CHECK ([Title]='Mrs.' OR [Title]='Miss.' OR [Title]='Mr.'),
CHECK ([Nationality]='Indian'),
CHECK (len([Zipcode])=(6))
);

我编写了一个存储过程，如下所示，它在外部执行时更新表，但是当我试图通过UI输入值并按Submit时，它不更新表。

CREATE procedure usp_team5_customerupdate
   (
   @Title varchar(20),
   @Lastname varchar(20),
   @StreetAddress varchar(100),
   @State varchar(30),
   @TownorCity varchar(50),
   @Zipcode int,
   @MobileNumber bigint,
   @PhoneNumber bigint,
   @EmailAddress varchar(30),
   @CompanyName varchar(50),
   @OfficeAddress varchar(100),
   @custid nvarchar(20)
   )
   as     
   begin
   update team5_customerprofile
   set
   Title=@Title,Lastname=@Lastname,     
   StreetAddress=@StreetAddress,
   [State]=@State,
   TownorCity=@TownorCity,     
   Zipcode=@Zipcode,
   MobileNumber=@MobileNumber,
   PhoneNumber=@PhoneNumber,     
   EmailAddress=@EmailAddress,
   CompanyName=@CompanyName,     
   OfficeAddress=@OfficeAddress
   where
   CustomerID=@custid
   end

Answer 1

Create procedure usp_team5_customerupdate        
(  
@Title varchar(20),  
@Lastname varchar(20),  
@StreetAddress varchar(100),  
@State varchar(30),  
@TownorCity varchar(50),  
@Zipcode int =0,  
@MobileNumber bigint ,  
@PhoneNumber bigint,  
@EmailAddress varchar(30),  
@CompanyName varchar(50),  
@OfficeAddress varchar(100),  
@custid nvarchar(20)  
)  
AS  
BEGIN  
IF Exists(SELECT * FROM team5_customerprofile WHERE CustomerID=@custid)  
BEGIN  
   UPDATE team5_customerprofile  
SET  
Title=@Title,Lastname=@Lastname,  
StreetAddress=@StreetAddress,  
[State]=@State,  
TownorCity=@TownorCity,  
Zipcode=@Zipcode,  
MobileNumber=@MobileNumber,  
PhoneNumber=@PhoneNumber,  
EmailAddress=@EmailAddress,  
CompanyName=@CompanyName,  
OfficeAddress=@OfficeAddress  
WHERE CustomerID=@custid  
END
   ELSE  
     BEGIN  
          PRINT 'Invalid Customer Id '''+@custid+''''  
     END  
END

Answer 2

您编写的代码是正确的，存储的过程也是正确的检查连接字符串并运行SQL-Profiler检查所有参数，如果有任何转换问题检查所有…

尝试改变这个过程一次，如果你得到任何错误，而执行这段代码让我知道…

 CREATE procedure usp_team5_customerupdate
 @Title varchar(20),
 @Lastname varchar(20),
 @StreetAddress varchar(100),
 @State varchar(30),
 @TownorCity varchar(50),
 @Zipcode int,
 @MobileNumber bigint,
 @PhoneNumber bigint,
 @EmailAddress varchar(30),
 @CompanyName varchar(50),
 @OfficeAddress varchar(100),
 @custid nvarchar(20)
 as     
 begin
 IF Exists(SELECT * FROM team5_customerprofile WHERE CustomerID=@custid)  
 BEGIN  
 update team5_customerprofile
 set
 Title=@Title,Lastname=@Lastname,     
 StreetAddress=@StreetAddress,
 State=@State,
 TownorCity=@TownorCity,     
 Zipcode=@Zipcode,
 MobileNumber=@MobileNumber,
 PhoneNumber=@PhoneNumber,     
 EmailAddress=@EmailAddress,
 CompanyName=@CompanyName,     
 OfficeAddress=@OfficeAddress
 where
 CustomerID=@custid
 end
 else
 print 'Error'
 end
 end

Answer 3

我没有看到与查询字符串一起传递的参数，即

SqlCommand cmd = new SqlCommand("usp_team5_customerupdate", con);

尝试传递如下参数:

SqlCommand cmd = new SqlCommand("usp_team5_customerupdate @Title,@Lastname..(So on)", con);

Answer 4

更改存储过程中CustId的数据类型以匹配列数据类型BigInt

 @custid nvarchar(20)

同样在你的代码中，只传递CustId而不进行任何转换。

最后，您可以放置调试器并替换检查代码中每个变量的值，并在SQL查询窗口中使用它，并运行以下命令来调试错误。

DECLARE @qry NVARCHAR(4000);
SET @qry = '
        UPDATE team5_customerprofile
        set
        Title=''' + @Title + '''
       where
       CustomerID=' + @custid + '
     end';
PRINT @qry;

您可以包含其他列，以便在运行此命令时可以检查问题在哪里。