-1

I have following tables:

SELECT [Cus_ID]
  ,[Name]
  ,[Email]
  ,[Password]
  ,[CNIC]
  ,[Account_No]
  ,[Tel_No]
  ,[Mob_No]
  ,[Website]
  ,[Billing_Address]
  FROM [NewImport_DB].[dbo].[Customer]

SELECT [Pay_ID]
  ,[Is_Purchased]
  ,[User_ID]
  ,[Total_Amount]
  ,[Paid_Amount]
  ,[Remaining_Amount]
  ,[Date]
  FROM [NewImport_DB].[dbo].[Payments]

SELECT [Bank_ID]
  ,[User_ID]
  ,[Account_No]
  ,[Amount]
  ,[Date]
  FROM [NewImport_DB].[dbo].[Bank_Acount]

SELECT [Py_ID]
  ,[User_ID]
  ,[Bank_Name]
  ,[Account_No]
  ,[Date]
  FROM [NewImport_DB].[dbo].[Transfer_Pay]

I have a transaction page, where i'm doing a simple insertion but with with two conditions.

1). If Account_No entered is invalid then it will show us js message that invalid account_no.

2). 2nd is if the total_amount (which i'm getting from payments table) is less than SUM(Amount) from Bank_Acount table then it must do the insertion process else it shows me the 2nd inner if message that You have less balance in your Bank Account, Please Deposit the money to purchase again!.

Here is my whole code i have tried, the main problem i guessed is, i have used a break point on the second inner if condition. Both the queries Query: 02 and Query: 03whenever i'm executing it in sql server 2008 R2, they are working 100% correct, but in Visual Studio on debugging mode, it doesn't shows me the values they have. Here is my code.. Please help me. Thank you. 

int d;
        if (Session["UserCheck"] != null)
        {
            d = Convert.ToInt32(Session["CustomerID"]);
        }
        else
        {
            return;
        }

        // Query: 01
        // Getting Account_No from Customers and matching with enterd Account_No in txtAcntNo.Text
        SqlCommand cmd1 = new SqlCommand("select COUNT(Coalesce(Account_No, '0')) FROM [NewImport_DB].[dbo].[Customer] where Account_No = '" + txtAcntNo.Text + "' AND Cus_ID = '"+d+"' ", con);
        con.Open();
        var acc = cmd1.ExecuteScalar().ToString();
        con.Close();

        // Query: 02
        // Getting Total Amount from User Cart/Order_Details
        SqlCommand cmd3 = new SqlCommand("SELECT [Total_Amount] FROM [NewImport_DB].[dbo].[Payments] WHERE Date IN (SELECT max(Date) FROM Payments)", con);
        con.Open();
        var T_Ordr_Amount = (Int32)cmd1.ExecuteScalar();   // Contains Total_Amount from User Cart/Order_Details
        con.Close();

        // Query: 03
        // Getting SUM(Amount) from Bank_Acount Table
        SqlCommand cmd2 = new SqlCommand("select SUM(Amount) AS T_Amount FROM [NewImport_DB].[dbo].[Bank_Acount] where Account_No = '" + txtAcntNo.Text + "' AND User_ID = '" + d + "' ", con);
        con.Open();
        var bnd_acc = (Int32)cmd1.ExecuteScalar();   // Contains SUM(Amount) from Bank_Acount Table
        con.Close();

        if (acc == "1")      // if  Account_No is matched
        {
            if (bnd_acc > T_Ordr_Amount)      // if SUM(Amount) in Bank_tbl > Total_Amount from Customer_Order_Details
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO [NewImport_DB].[dbo].[Transfer_Pay]([User_ID] ,[Bank_Name], [Account_No] ,[Date]) VALUES ('" + d + "' ,'" + txtBnkame.Text + "', '" + txtAcntNo.Text + "', GETDATE())";
                cmd.ExecuteNonQuery();
                con.Close();
                Response.Redirect("/shipments.aspx");
            }
            else
            {
                Response.Write("<script>alert('You have less balance in your Bank Account, Please Deposit the money to purchase again!')</script>");
            }

        }
        else
        {
            Response.Write("<script>alert('You entered invalid Account No!')</script>");
        }

Below are the images... 1

SQL Queries Output in ssms 2

3

enter image description here

Bank Table data 5

Hamza Javed
  • 69
  • 1
  • 2
  • 12
  • 2
    This wall of mess is way too much info. Please see: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – C-Pound Guru Oct 29 '15 at 21:04
  • 2
    not to be rude but debugging your code can really save you a lot of headaches it's quite obvious that you are not executing one or more of the query's take your time and make sure that you are executing all query's properly – MethodMan Oct 29 '15 at 21:07
  • So what should i have to do now... – Hamza Javed Oct 29 '15 at 21:18

1 Answers1

4

Look at these lines 

SqlCommand cmd3 = new SqlCommand("SELECT [Total_Amount] FROM .....", con);
con.Open();
var T_Ordr_Amount = (Int32)cmd1.ExecuteScalar();

and 

SqlCommand cmd2 = new SqlCommand("select SUM(Amount) AS T_Amount FROM ...", con);
con.Open();
var bnd_acc = (Int32)cmd1.ExecuteScalar();

You are always executing cmd1 instead of cmd2 and cmd3

Apart from this you have a massive problem of security.... NEVER use a string concatenation with the user input to build your sql queries. You are a very easy target for Sql Injections. Use always a parameterized query

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286