CODEWITHSUNDEEP
c#visual-studio-2010
Mark Desp
Mark Desp

Reputation: 55

C# update database error

try
{
    sqlCommandWithdraw.Connection.Open();
    sqlCommandWithdraw.Parameters["@cardNumber"].Value = Class1.cardNumber;
    readdata = sqlCommandWithdraw.ExecuteReader();

    while (readdata.Read())
    {
        balanceDB = decimal.Parse(readdata["balance"].ToString());
    }

    decimal withdrawAmm = Convert.ToDecimal(textWithdraw.Text);
    balanceDB = balanceDB - withdrawAmm;
    sqlCommandWithdraw.Connection.Close();

    sqlCommandUpdate.Connection.Open();
    sqlCommandUpdate.Parameters["@cardNumber"].Value = Class1.cardNumber;
    sqlCommandUpdate.Parameters["@balanceDB"].Value = Class1.cardNumber;
    readdata = sqlCommandUpdate.ExecuteReader();
    MessageBox.Show(balanceDB +" Successfully Withdrawn");
}

I'm working on code for an ATM machine I'm a bit stuff on the withdraw it looks fine but doesn't seem to change the balance to reflect the withdrawals in the database

My commands go like this (update)

update dbo.Accounts
set balance = @balanceDB
from dbo.ATMCards 
INNER JOIN dbo.Accounts ON dbo.ATMCards.accountID = dbo.Accounts.accountID
where (dbo.ATMCards.cardNumber = @cardNumber)

and this is my command to select the data

select dbo.Accounts.balance
from dbo.ATMCards 
INNER JOIN dbo.Accounts ON dbo.ATMCards.accountID = dbo.Accounts.accountID
where (dbo.ATMCards.cardNumber = @cardNumber)

Seems to run just fine added the message box to check it thanks for any help appreciate it!

Upvotes: 2

Views: 269

Answers (3)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You are passing the credit card number to the parameter @balanceDB - this is the first mistake. Second, you do not use ExecuteReader to perform updates - use ExecuteNonQuery instead.

EDIT
I'll do some clean-ups for you:

try
{
    try
    {
        sqlCommandWithdraw.Connection.Open();
        sqlCommandWithdraw.Parameters["@cardNumber"].Value = Class1.cardNumber;

        // Make sure to dispose of the reader, which also closes the reader, which
        // is important, because you can't perform any other selects on a connection
        // with an open reader!
        using (SqlDataReader reader = sqlCommandWithdraw.ExecuteReader())
        {
            // You will only get one line - also, your code also only evaluates
            // one result, so we can do the following:
            if (reader.Read())
            {
                balanceDB = decimal.Parse(readdata["balance"].ToString());
            }
        }
    }
    finally
    {
        sqlCommandWithdraw.Connection.Close();
    }

    decimal withdrawAmm = Convert.ToDecimal(textWithdraw.Text);
    balanceDB = balanceDB - withdrawAmm;

    try
    {
        sqlCommandUpdate.Connection.Open();
        sqlCommandUpdate.Parameters["@cardNumber"].Value = Class1.cardNumber;
        sqlCommandUpdate.Parameters["@balanceDB"].Value = balanceDB;

        sqlCommandUpdate.ExecuteNonQuery();
        MessageBox.Show(balanceDB +" Successfully Withdrawn");
    }
    finally
    {
        sqlCommandUpdate.Connection.Close();
    }

}

Upvotes: 5

Liath
Liath

Reputation: 10191

This line looks suspicious to me:

sqlCommandUpdate.Parameters["@balanceDB"].Value = Class1.cardNumber;

Should that be Class1.balance?

Upvotes: 1

Shyju
Shyju

Reputation: 218732

You are passing a wrong value for the @balanceDB parameter. It should be balance amount. But you are passing the Card Number.

 sqlCommandUpdate.Parameters["@balanceDB"].Value = Class1.cardNumber;

should be changed to 

 sqlCommandUpdate.Parameters["@balanceDB"].Value = balanceDB ;

Upvotes: 1

Related Questions