SQL值未发送到表(C#/ASP.NET)
本文关键字：ASP NET SQL | 更新日期: 2023-09-27 17:58:23
我正在尝试设置一个页面，以便使用C#、ASP.NET和MSSQL（如果重要的话，请使用MSSQLEXPRESS）复制ATM。其中一部分是"新用户"页面，用于"注册"服务"。问题是，当我测试页面时，我会收到一条错误消息，我设置该消息是为了检测无效的信用卡号，而不管输入了什么号码。我认为问题出在我的点击事件代码、C#查询代码或事件的存储过程中。我认为解决这个问题的办法很可能很简单，但也许有一双新的眼睛会看到这个问题。
如有任何帮助或建议，我们将不胜感激。
按钮点击代码：
protected void btnSubmit_Click(object sender, EventArgs e)
{
string display;
long cardnum;
string strcard = Request.Params.Get("__CREDITCARD");
if (long.TryParse(strcard, out cardnum))
{
string first = Request.Params.Get("__FIRSTNAME");
string middle = Request.Params.Get("__MIDDLENAME");
string last = Request.Params.Get("__LASTNAME");
string email = Request.Params.Get("__EMAIL");
string address = Request.Params.Get("__ADDRESS");
string username = Request.Params.Get("__USERNAME");
string password = Request.Params.Get("__PASSWORD");
int retcode = SqlQueries.changeUserInfo(cardnum, username, password, first, middle, last, email, address, out display);
switch (retcode)
{
case 1:
display = "Credit card number can only contain digits";
Alert.show(Page, this.GetType(), "Input Error", display);
UserDetails.Username = username;
UserDetails.Password = password;
Response.Redirect("HomePage.aspx");
return;
case 0:
display = "Invalid credit card number";
break;
}
}
else
{
display = "Credit card number can only contain digits";
}
Alert.show(Page, this.GetType(), "Input Error", display);
}
它使用我的SqlQueries类中的changeUserInfo方法：
public static int changeUserInfo(long cardNum, string username, string password, string strFirstName, string strMiddleName, string strLastName, string strEmail, string strAddress, out string strError)
{
//SQL connection
SqlConnection objConn = new SqlConnection(strconnectionSting);
objConn.Open();
int intReturnValue = -1;
strError = string.Empty;
//If connection is open
if (objConn != null && objConn.State == ConnectionState.Open)
{
//Call to stored procedure: qprtnum_UpdatePartNumber
SqlCommand cmd = new SqlCommand("updateUserInfo", objConn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.Add(new SqlParameter("@CardNum", SqlDbType.Decimal, 150));
cmd.Parameters["@CardNum"].Precision = 18;
cmd.Parameters["@CardNum"].Scale = 0;
cmd.Parameters["@CardNum"].Value = cardNum;
cmd.Parameters.Add(new SqlParameter("@Username", SqlDbType.NVarChar, 50));
cmd.Parameters["@Username"].Value = username;
cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar, 50));
cmd.Parameters["@Password"].Value = password;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 150));
cmd.Parameters["@FirstName"].Value = strFirstName;
cmd.Parameters.Add(new SqlParameter("@MiddleName", SqlDbType.NVarChar, 150));
cmd.Parameters["@MiddleName"].Value = strMiddleName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 150));
cmd.Parameters["@LastName"].Value = strLastName;
cmd.Parameters.Add(new SqlParameter("@EmailAddress", SqlDbType.NVarChar, 50));
cmd.Parameters["@EmailAddress"].Value = strEmail;
cmd.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 150));
cmd.Parameters["@Address"].Value = strEmail;
//Return Value
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
cmd.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
intReturnValue = (int)cmd.Parameters["@ReturnValue"].Value;
strError = string.Empty;
}
catch (SqlException err)
{
intReturnValue = -1;
strError = err.Message;
}
catch (Exception ex)
{
intReturnValue = -1;
strError = ex.Message;
}
finally
{
objConn.Close();
}
}
else
{
//Error
intReturnValue = -1;
strError = "Error";
}
return intReturnValue;
}
存储过程"updateUserInfo"：
USE [ATM]
GO
/****** Object: StoredProcedure [dbo].[updateUserInfo] Script Date: 4/15/2014 1:43:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[updateUserInfo]
@CardNum AS decimal,
@Username AS nvarchar(50),
@Password AS nvarchar(50),
@FirstName AS nvarchar(150),
@MiddleName AS nvarchar(150),
@LastName AS nvarchar(150),
@EmailAddress nvarchar(50),
@Address nvarchar(150)
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ReturnValue AS INT;
--Set current date
DECLARE @Date AS numeric(18,0);
DECLARE @Hours int;
DECLARE @Minutes int;
DECLARE @Seconds int;
DECLARE @Milliseconds INT;
DECLARE @CurDate as VARCHAR(50);
SET @Hours = DATEPART(hh, GETDATE())
SET @Minutes = DATEPART(mi, GETDATE())
SET @Seconds = DATEPART(ss, GETDATE())
SELECT @CurDate = CONVERT(VARCHAR(35),GETDATE(),112)
SET @CurDate = @CurDate + CONVERT(VARCHAR(5), @Hours) + CONVERT(VARCHAR(5), @Minutes) + CONVERT(VARCHAR(5),@Seconds)
SELECT @Date = CONVERT(decimal(18,0), @CurDate)
-- Insert statements for procedure here
enter code here
UPDATE dbo.tblClient
SET [cliCardNum] = @CardNum
,[cliFirstName] = @FirstName
,[cliMiddleName] = @MiddleName
,[cliLastName] = @LastName
,[cliEmailaddress] = @EmailAddress
,[cliAddress] = @Address
,[TimeStamp] = @Date
,Enabled = 1
WHERE cliUsername=@Username AND cliPassword=@Password
SET @ReturnValue=0;
COMMIT TRANSACTION;
RETURN @ReturnValue
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
SET @ReturnValue=-1;
Print @ReturnValue
RETURN @ReturnValue;
END CATCH
在我看来，问题在于从存储过程中选择返回值。我在C#中用一个简单的存储过程做了一个快速的例子。
// demo code to select the return value
string conStr = @"data source=*****; initial catalog=demoDb; integrated security=true";
SqlConnection con = new SqlConnection(conStr);
SqlCommand com = new SqlCommand("declare @return_status int; exec @return_status = demoProcedure; select @return_status", con);
con.Open();
Console.WriteLine(com.ExecuteScalar());
con.Close();
我的演示存储过程
create PROCEDURE demoProcedure
AS
BEGIN
return 1;
END
GO
在Technet上找到此示例。我希望你能根据你的代码调整我的示例！如果我误解了你的问题，或者我的代码中有任何错误，请告诉我！
编辑
在思考你的问题时，我注意到科技网文章上的以下短语
无条件地从查询或过程中退出。RETURN立即返回并且是完整的，并且可以在任何点使用以退出过程，批处理或语句块。RETURN后面的语句不是已执行。
因此，当您将程序更改为选择一个值而不是返回它时，您可以将代码调整为：
string conStr = @"data source=***; initial catalog=demoDb; integrated security=true";
SqlConnection con = new SqlConnection(conStr);
SqlCommand com = new SqlCommand("demoProcedure", con);
com.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
Console.WriteLine(com.ExecuteScalar());
con.Close();
用一个简单的程序
create PROCEDURE demoProcedure
AS
BEGIN
select 1;
END
编辑2
如果成功，存储过程将返回0；如果失败，则返回-1。但在b单击事件中，您选择的stmt检查1=成功和0=失败。请尝试将您的代码更改为
protected void btnSubmit_Click(object sender, EventArgs e)
{
string display;
long cardnum;
string strcard = Request.Params.Get("__CREDITCARD");
if (long.TryParse(strcard, out cardnum))
{
// your code goes here ...
switch (retcode)
{
case 0: // changed to reflect returnValue from stored Procedure
// your code goes here ...
return;
case -1: // changed to reflect returnValue from stored Procedure
display = "Invalid credit card number";
break;
}
}
// your code goes here ...
}