How to add sql query in .cs file ?

• <div>i have two tables account_mstr and trans_mstr and i want to insert the subtraction of  two columns curbal - amt of two different tables and want to store the result in another column balance of table trans_mstr .</div> <div>i found the solution of sql server which is in link mentioned below:</div> <div></div> <div>http://forums.asp.net/t/1706799.aspx/1?using+JOINS+add+columns+of+two+different+tables+in+sql+server+2008</div> <div></div> <div>But now the problem is i want to add this query in .cs file in ASP.NET and m unable to solve this.</div> <div>below i have given the coding of .cs file of trans_mstr</div> <div></div> <div>

  using System;
  using System.Collections;
  using System.Configuration;
  using System.Data;
  using System.Linq;
  using System.Web;
  using System.Web.Security;
  using System.Web.UI;
  using System.Web.UI.HtmlControls;
  using System.Web.UI.WebControls;
  using System.Web.UI.WebControls.WebParts;
  using System.Xml.Linq;
  using System.Data.SqlClient;
  public partial class users_trans_details : System.Web.UI.Page
  {
      
  
      protected void Page_Load(object sender, EventArgs e)
      {
  
      }
      protected void Button1_Click(object sender, EventArgs e)
      {
          DateTime dtCurrTime = DateTime.Today; // TO iNSERT A DATE. textbox 4
          string d = dtCurrTime.ToString();
         
  
  
          string strImageName = TextBox1.Text.ToString(); // To enter the value of text Box
          string dt1 = TextBox5.Text.ToString();
          string type1 = DropDownList1.SelectedValue;
          string particular1 = TextBox2.Text.ToString();
          string dr_cr1 = DropDownList2.SelectedValue;
          string amt1 = TextBox3.Text.ToString();
          string balance1 = TextBox4.Text.ToString();
  
          SqlConnection con = new SqlConnection();
          con.ConnectionString = (@"Data Source=PANKIL-PC\pankil;Initial Catalog=sky;Integrated Security=True");
  
          SqlCommand cmd = new SqlCommand();
          cmd.CommandText = "INSERT INTO trans_mstr(accnt_no,dt,type,particular,dr_cr,amt,balance)" +
                            " VALUES (@accnt_no,@dt,@type,@particular,@dr_cr,@amt,@(update trans_mstr set balance(select curbal from account_mstr where account_mstr.accnt_no=trans_mstr.accnt_no) - amt))";
  
  
          cmd.CommandType = CommandType.Text;
          cmd.Connection = con;
  
          SqlParameter accnt_no = new SqlParameter("@accnt_no", SqlDbType.Int);
          accnt_no.Value = strImageName.ToString();
          cmd.Parameters.Add(accnt_no);
  
          SqlParameter dt = new SqlParameter("@dt", SqlDbType.Date);
          dt.Value = dt1.ToString();
          cmd.Parameters.Add(dt);
  
          SqlParameter type = new SqlParameter("@type", SqlDbType.VarChar, 1);
          type.Value = type1.ToString();
          cmd.Parameters.Add(type);
  
          SqlParameter particular = new SqlParameter("@particular", SqlDbType.VarChar, 30);
          particular.Value = particular1.ToString();
          cmd.Parameters.Add(particular);
  
          SqlParameter dr_cr = new SqlParameter("@dr_cr", SqlDbType.VarChar, 1);
          dr_cr.Value = dr_cr1.ToString();
          cmd.Parameters.Add(dr_cr);
  
          SqlParameter amt = new SqlParameter("@amt", SqlDbType.Char,10);
          amt.Value = amt1.ToString();
          cmd.Parameters.Add(amt);
  
          SqlParameter balance = new SqlParameter("@(update trans_mstr set balance=(select curbal from account_mstr where account_mstr.accnt_no=trans_mstr.accnt_no) - amt)", SqlDbType.Char, 10);
          balance.Value = balance1.ToString();
          cmd.Parameters.Add(balance);
  
          con.Open();
          int result = cmd.ExecuteNonQuery();
          con.Close();
      }
  }

  </div> <div></div> <div></div> <div></div> <div>please help me.</div> <div>thnx</div> <div></div> <div></div>
  Regadrs
  Pankil

• 
  

  Write a stored procedure to insert the data in to the trans_mstr with below insert statement.

  INSERT INTO trans_mstr(accnt_no,dt,type,particular,dr_cr,amt,balance) VALUES (@accnt_no,@dt,@type,@particular,@dr_cr,@amt, 0)

  Following is the update statement for updating the balance.

  update trans_mstr set balance = (select curbal from account_mstr where account_mstr.accnt_no=@accnt_no) - amt)

  Some thing like below Stored procedure.

  CREATE PROCEDURE [dbo].[InsertAndUpdate]
  (
          @accnt_no as int,
          @dt as datetime,
          @type as varchar(1),
          @particular as varchar(30),
          @dr_cr as varchar(1),
          @amt as char(10)
  )
  AS
          INSERT INTO trans_mstr(accnt_no,dt,type,particular,dr_cr,amt,balance) 
          VALUES (@accnt_no,@dt,@type,@particular,@dr_cr,@amt, 0)
          
          update trans_mstr set balance = (select curbal from account_mstr where account_mstr.accnt_no=@accnt_no) - amt)
  
  RETURN

  
  And the command would be as follows:

  SqlCommand cmd = new SqlCommand();
          cmd.CommandText = "[InsertAndUpdate]";
  
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Connection = con;
  
  
• 
  
  
  

  thnx for ur reply. I have tried ur solution. I have done the coding like this-

  using System;
  using System.Collections;
  using System.Configuration;
  using System.Data;
  using System.Linq;
  using System.Web;
  using System.Web.Security;
  using System.Web.UI;
  using System.Web.UI.HtmlControls;
  using System.Web.UI.WebControls;
  using System.Web.UI.WebControls.WebParts;
  using System.Xml.Linq;
  using System.Data.SqlClient;
  public partial class users_trans_details : System.Web.UI.Page
  {
      
  
      protected void Page_Load(object sender, EventArgs e)
      {
  
      }
      protected void Button1_Click(object sender, EventArgs e)
      {
          DateTime dtCurrTime = DateTime.Today; // TO iNSERT A DATE. textbox 4
          string d = dtCurrTime.ToString();
         
  
  
          string strImageName = TextBox1.Text.ToString(); // To enter the value of text Box
          string dt1 = TextBox5.Text.ToString();
          string type1 = DropDownList1.SelectedValue;
          string particular1 = TextBox2.Text.ToString();
          string dr_cr1 = DropDownList2.SelectedValue;
          string amt1 = TextBox3.Text.ToString();
          string balance1 = TextBox4.Text.ToString();
  
          SqlConnection con = new SqlConnection();
          con.ConnectionString = (@"Data Source=PANKIL-PC\pankil;Initial Catalog=sky;Integrated Security=True");
  
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "insertupdate";
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Connection = con;
  
          cmd.CommandType = CommandType.Text;
          cmd.Connection = con;
  
          SqlParameter accnt_no = new SqlParameter("@accnt_no", SqlDbType.Int);
          accnt_no.Value = strImageName.ToString();
          cmd.Parameters.Add(accnt_no);
  
          SqlParameter dt = new SqlParameter("@dt", SqlDbType.Date);
          dt.Value = dt1.ToString();
          cmd.Parameters.Add(dt);
  
          SqlParameter type = new SqlParameter("@type", SqlDbType.VarChar, 1);
          type.Value = type1.ToString();
          cmd.Parameters.Add(type);
  
          SqlParameter particular = new SqlParameter("@particular", SqlDbType.VarChar, 30);
          particular.Value = particular1.ToString();
          cmd.Parameters.Add(particular);
  
          SqlParameter dr_cr = new SqlParameter("@dr_cr", SqlDbType.VarChar, 1);
          dr_cr.Value = dr_cr1.ToString();
          cmd.Parameters.Add(dr_cr);
  
          SqlParameter amt = new SqlParameter("@amt", SqlDbType.Int);
          amt.Value = amt1.ToString();
          cmd.Parameters.Add(amt);
  
          SqlParameter balance = new SqlParameter("@balance",SqlDbType.Int);
          balance.Value = balance1.ToString();
          cmd.Parameters.Add(balance);
  
          con.Open();
          int result = cmd.ExecuteNonQuery();
          con.Close();
      }
  }