How can I deduct integer value from database?

Question

0.00/5 (No votes)

See more:

This is my Datatable name CreditCard

CardID numeric(18, 0) Unchecked
cardName nvarchar(50) Unchecked
balance int Checked
Email_Address nvarchar(50) Unchecked

This is my Stored Procedured

SQL

ALTER PROCEDURE dbo.FindCreditCard

AS
	SELECT * FROM CreditCard
	RETURN

SQL

ALTER PROCEDURE dbo.CreditDeduct
@CardID numeric(18,0),
@balance int
AS
	UPDATE CreditCard SET CardID = @CardID WHERE balance =@balance 
	RETURN

This is my Class.

C#

public void FindCredit(decimal ccnum, int bal) //Gets balance after clicking Checkout button
     {
         db_connection.Open();

         int deductedCredit = 0;

         SqlCommand sc = new SqlCommand("FindCreditCard", db_connection);
         sc.CommandType = CommandType.StoredProcedure;
         SqlDataReader sdr = sc.ExecuteReader();

         while (sdr.Read())
         {
             if (Convert.ToDecimal(sdr["CardID"].ToString()) == ccnum)
             {

                 deductedCredit = Convert.ToInt32(sdr["balance"]) - bal;
             }
         }

         db_connection.Close();

         deductCredit(ccnum, deductedCredit);
     }
     public void deductCredit(decimal ccnum, int bals) //Deducts balance at items table after clicking checkout button
     {
         db_connection.Open();

         SqlCommand sc = new SqlCommand("CreditDeduct", db_connection);
         sc.CommandType = CommandType.StoredProcedure;
         sc.Parameters.Add("@CardID", SqlDbType.Decimal).Value = ccnum;
         sc.Parameters.Add("@balance", SqlDbType.Int).Value = bals;

         sc.ExecuteNonQuery();
         db_connection.Close();
     }

and this is the button.

C#

protected void imgbtnSubmit_Click1(object sender, ImageClickEventArgs e)
   {

       decimal ccnum;

       int bal;

               ccnum = Convert.ToDecimal(txtCCardNum.Text);
               bal = Convert.ToInt32(tbxDeposit.Text);
               myData.FindCredit(ccnum, bal);

   }

Help me guys I can't deduct the balance in my datatable CreditCard or if you just a other idea on how to deduct value from database table. Thank for your answer.

Posted 9-Sep-14 16:42pm

XCode2010

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ChauhanAjay · Accepted Answer · 2014-09-09T16:47:00

Solution 1

Change your update statement in your stored procedure CreditDeduct from

SQL

UPDATE CreditCard SET CardID = @CardID WHERE balance =@balance

To

SQL

UPDATE CreditCard SET balance =@balance WHERE CardID = @CardID

Hope the above statement helps in solving your problem.

Posted 9-Sep-14 16:47pm

ChauhanAjay

Comments

XCode2010 9-Sep-14 22:49pm

Wtf! I don't see that. Haha. Sorry man and thanks ;)

Wendelius · Accepted Answer · 2014-09-09T17:35:00

The name of your procedure is CreditDeduct but it actually sets a new balance which is provided using parameter.

In my opinion you should modify the procedure to

SQL

ALTER PROCEDURE dbo.CreditDeduct
@CardID numeric(18,0),
@deduction int
AS
    UPDATE CreditCard SET balance = balance - @deduction WHERE CardID = @CardID
    RETURN

This way the balance would always be set to a correct value based on the current situation of the balance. Now you have a risk that two programs read the balance and modify it based on "old" information about the balance. The problem itself is known as lost update.

For more information, see Concurrency control[^]