Hi all, I need help in validating the destination account no while transferring the balance from one account to another account. I have written the code but not getting exact output. Here is my code,its bit lengthy pls read out this code and point me out where is my mistake.I have written this inside the session handling.
protected void btnTransfer_Click(object sender, EventArgs e)
{
// This function allows transfer of funds from an account held by the logged in Customer to any other account in the bank.
// This function adjusts the balanace for both source and destination account and records the transaction in Transactions table.
Double dblAmount;
int iSource, iDest;
int iSourceBal, iDestBal;
Double.TryParse(txtAmount.Text, out dblAmount);
int.TryParse(ddSource.SelectedValue, out iSource);
int.TryParse(txtDest.Text, out iDest);
if (ValidateAccounts())
{
getBalance();
if (dStatus.Equals("A"))
{
int.TryParse(sBalance, out iSourceBal);
int.TryParse(dBalance, out iDestBal);
if (ValidateBalance(dblAmount))
{
SqlCommand cmdSource, cmdDest;
SqlCommand cmdSourceUpdate, cmdDestUpdate;
SqlTransaction transferTrans;
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
conn.Open();
transferTrans = conn.BeginTransaction();
try
{
String sInsertTrans = "INSERT INTO [Transactions] ([AccountID],[TransactionType],[Amount],[DOT],[UserID]," + Environment.NewLine +
@"[SourceOrDestAccountId],[TransferFlag],[Balance]) VALUES (@AccountID,@TransactionType,@Amount,@DOT,Convert(uniqueidentifier,@UserID)," + Environment.NewLine +
@"@SourceOrDestAccountId,@TransferFlag,@Balance)";
cmdSource = new SqlCommand(sInsertTrans, conn, transferTrans);
cmdSource.Parameters.AddWithValue("@AccountID", iSource);
cmdSource.Parameters.AddWithValue("@TransactionType", "Debit");
cmdSource.Parameters.AddWithValue("@Amount", dblAmount);
cmdSource.Parameters.AddWithValue("@DOT", System.DateTime.Now);
cmdSource.Parameters.AddWithValue("@UserID", Session["UserId"]);
cmdSource.Parameters.AddWithValue("@SourceOrDestAccountId", iDest);
cmdSource.Parameters.AddWithValue("@TransferFlag", 'S');
cmdSource.Parameters.AddWithValue("@Balance", iSourceBal - dblAmount);
cmdDest = new SqlCommand(sInsertTrans, conn, transferTrans);
cmdDest.Parameters.AddWithValue("@AccountID", iDest);
cmdDest.Parameters.AddWithValue("@TransactionType", "Credit");
cmdDest.Parameters.AddWithValue("@Amount", txtAmount.Text);
cmdDest.Parameters.AddWithValue("@DOT", System.DateTime.Now);
cmdDest.Parameters.AddWithValue("@UserID", Session["UserId"]);
cmdDest.Parameters.AddWithValue("@SourceOrDestAccountId", iSource);
cmdDest.Parameters.AddWithValue("@TransferFlag", "D");
cmdDest.Parameters.AddWithValue("@Balance", iDestBal + dblAmount);
String sUpdateSourceTrans = "UPDATE Customer_Account SET Balance=Balance - @Amount WHERE AccountId= @AccountId";
String sUpdateDestTrans = "UPDATE Customer_Account SET Balance=Balance + @Amount WHERE AccountId= @AccountId";
cmdSourceUpdate = new SqlCommand(sUpdateSourceTrans, conn, transferTrans);
cmdSourceUpdate.Parameters.AddWithValue("@Amount", dblAmount);
cmdSourceUpdate.Parameters.AddWithValue("@AccountId", iSource);
cmdDestUpdate = new SqlCommand(sUpdateDestTrans, conn, transferTrans);
cmdDestUpdate.Parameters.AddWithValue("@Amount", dblAmount);
cmdDestUpdate.Parameters.AddWithValue("@AccountId", iDest);
cmdSource.ExecuteNonQuery();
cmdDest.ExecuteNonQuery();
cmdSourceUpdate.ExecuteNonQuery();
cmdDestUpdate.ExecuteNonQuery();
transferTrans.Commit();
conn.Close();
lblMessage.Text = "The amount has been transferred.";
}
catch (SqlException ex)
{
transferTrans.Rollback();
Response.Write("Sorry, there is an error." + ex.Message);
}
}
else
{
lblMessage.Text = "<font color='Red'>* Your account does not have sufficient balance. Fund transfer cannot be done.</font>";
}
}
else
{
lblMessage.Text = "<font color='Red'>* The Destination account is unavailable or deactivated. Fund transfer cannot be done.</font>";
}
}
else
{
lblMessage.Text = "<font color='Red'> * Source and Destination accounts cannot be same.</font>";
}
}
protected bool ValidateBalance(Double dbAmountEntered)
{
// This function validates the Source account balance to check if there is sufficient balance to be transferred.
Double dblAvailableAmount;
SqlConnection conn1;
conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
sqlSource = "SELECT (c.Balance-a.MinBalance) As AvailableBalance FROM Account AS a, Customer_Account As c " + Environment.NewLine +
@" WHERE c.AccountCode=a.AccountCode AND c.AccountId='" + ddSource.SelectedValue + "'";
sda = new SqlDataAdapter(sqlSource, conn1);
sda.Fill(ds, "Customer_Account");
Double.TryParse(ds.Tables[0].Columns["Balance"].ToString(), out dblAvailableAmount);
if (dblAvailableAmount >= dbAmountEntered)
{
conn1.Close();
return true;
}
else
{
conn1.Close();
return false;
}
}
protected bool ValidateAccounts()
{
// This function validates if the source and destination accounts are same or not.
if (ddSource.SelectedValue.Equals(txtDest.Text))
{
return false;
}
else
{
return true;
}
}
protected void getBalance()
{
// This function fetches the balance amount for both source and destination accounts.
// The function also checks if the destination account is activated or not.
SqlConnection conn1;
DataSet ds1 = new DataSet("ConnectionString");
SqlDataAdapter sda1 = new SqlDataAdapter();
try
{
conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
conn1.Open();
String sqlBalance = "SELECT Balance FROM Customer_Account WHERE AccountId='" + ddSource.SelectedValue + "'"; ;
sda1 = new SqlDataAdapter(sqlBalance, conn1);
sda1.Fill(ds1, "Customer_Account");
if (ds1.Tables[0].Rows.Count > 0)
{
sBalance = ds1.Tables[0].Columns["Balance"].ToString();
}
sqlBalance = "";
sqlBalance = "SELECT Balance,Status FROM Customer_Account WHERE AccountId='" + txtDest.Text + "'"; ;
sda1 = new SqlDataAdapter(sqlBalance, conn1);
ds1.Clear();
sda1.Fill(ds1, "Customer_Account");
if (ds1.Tables[0].Rows.Count > 0)
{
dBalance = ds1.Tables[0].Columns["Balance"].ToString();
dStatus = ds1.Tables[0].Columns["Status"].ToString();
}
else
{
dStatus = "NA";
}
conn1.Close();
}
catch (SqlException)
{
Response.Write("Sorry, there is an error.");
}
}
Here is my Customer_Account Table AccountID,UserID,Status,Balance,AccountCode All the status are A While i am running this application my loop is always going to else condition: * The Destination account is unavailable or deactivated. Fund transfer cannot be done.
pls somebody help me where is my logical error.
Thanks, Masum