SQL values not sent to table (C#/ASP.NET)

Question

I am trying to set up an page to replicate an ATM using C#, ASP.NET and MSSQL (MSSQLEXPRESS if that matters). One part of it is a "new user" page to "sign up" for the "service". The problem is that when I test the page, I get an error message I set up to detect invalid credit card numbers, regardless of the number input. I believe the problem is in either my click event code, my C# query code or in my stored procedure to the event. I think the solution for this is most likely a simple one, but perhaps a pair of fresh eyes will see the problem.

Any help or suggestions would be greatly appreciated.

Code for button click:

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);
    }

Which uses the changeUserInfo method from my SqlQueries class:

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;
    }

Stored procedure "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

I would change the code to utilize Parameters.AddWithValue and let the server resolve / handle the data type for starters personally.. I would also declare a Data Type in your stored procedure as TimeStamp and pass that value in by using C# DateTime.Now functionality casted as a TimeStamp. vs trying to Declare and Parse out the current Date. Look up DateTime.Today method for example — MethodMan
– MethodMan, Commented Apr 15, 2014 at 17:55
I get an error message?? - please show us in detail what error you get in what line of code or add a screenshot of your errorMessage/page. — Pilgerstorfer Franz
– Pilgerstorfer Franz, Commented Apr 15, 2014 at 18:00
Apologies, it's the "Invalid credit card number" message from the button click event. — user3537034
– user3537034, Commented Apr 15, 2014 at 18:04
to get values as timestamp try one of the 2 following bits of code ` var testTimeStamp = DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss"); var testTimeStamp2 = DateTime.Now.ToString("yyyyMMddHHmmssffff");` — MethodMan
– MethodMan, Commented Apr 15, 2014 at 18:08

Pilgerstorfer Franz · Accepted Answer · 2014-04-16 07:28:51Z

1

In my opinion the problem is on selecting the return value from within your stored procedure. I did a quick example in C# with a simple stored procedure.

// 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();

My demo stored procedure

create PROCEDURE demoProcedure      
AS
BEGIN
    return 1;
END
GO

Found this example at Technet. I hope you can adapt my example to your code! If I did misunderstood your question or anything within my code is wrong - please let me know!

EDIT

While thinking about your problem I noted the following phrase on technet article

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

So when you would alter your procedure to select a value instead of returning it you could adapt your code to:

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();

with a simple procedure

create PROCEDURE demoProcedure      
AS
BEGIN
    select 1;
END

EDIT 2

Your stored procedure returns 0 in case of success and -1 in case of a failure. But within your btnClick event your select stmt checks for 1 = success and 0 = failure. Please try changing your code to

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 ...
}

edited Apr 16, 2014 at 7:28

answered Apr 15, 2014 at 18:21

Pilgerstorfer Franz

8,3473 gold badges46 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pilgerstorfer Franz Over a year ago

can you verify that return intReturnValue; within SqlQueries.changeUserInfo(..) returns 0?

Pilgerstorfer Franz Over a year ago

Think I got it! Your stored procedure returns 0 in case of success and -1 in case of a failure. But within your btnClick event your select stmt checks for 1 = success and 0 = failure - see edited answer!

Collectives™ on Stack Overflow

SQL values not sent to table (C#/ASP.NET)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related