USE [Apartmentmanagementsystem]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Rento]
@rent_id int,
@rent_fee varchar (255),
@late_fee varchar (255),
@due_date varchar (255),
@service_charge varchar (255),
@lease_id int,
@pay_id int,
@pay_date varchar (255),
@pay_amount varchar (255),
@receipt_no varchar (255)
as
if @rent_id=0
begin
insert into Rent ( rent_fee,late_fee,due_date,service_charge,lease_id) values (@rent_fee,@late_fee,@due_date,@service_charge,@lease_id)
select @rent_id=SCOPE_IDENTITY()
insert into Payment (pay_date,pay_amount,receipt_no,rent_id) values(@pay_date,@pay_amount,@receipt_no,@rent_id)
end
C# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ApartmentManagement
{
public partial class Rent : System.Web.UI.Page
{
String CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (!String.IsNullOrEmpty(Request.QueryString["id"]))
{
int rent_id = Convert.ToInt32(Request.QueryString["id"]);
using (SqlConnection constring = new SqlConnection(CS))
{
constring.Open();
SqlDataAdapter sqda = new SqlDataAdapter("userview2", constring);
sqda.SelectCommand.CommandType = CommandType.StoredProcedure;
sqda.SelectCommand.Parameters.AddWithValue("@rent_id", rent_id);
DataTable dtbl = new DataTable();
sqda.Fill(dtbl);
hfrent_id.Value = rent_id.ToString();
if (dtbl.Rows.Count > 0)
{
txtRentfee.Text = dtbl.Rows[0][1].ToString();
txtLfee.Text = dtbl.Rows[0][2].ToString();
txtDdate.Text = dtbl.Rows[0][3].ToString();
txtScharge.Text = dtbl.Rows[0][4].ToString();
txtPdate.Text = dtbl.Rows[0][8].ToString();
txtPamount.Text = dtbl.Rows[0][9].ToString();
txtReceipt.Text = dtbl.Rows[0][10].ToString();
}
}
}
}
}
protected void btnSave4_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection constring = new SqlConnection(CS))
{
constring.Open();
SqlCommand sqlcmd = new SqlCommand("Rento", constring);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@rent_id", Convert.ToInt32(hfrent_id.Value == "" ? "0" : hfrent_id.Value));
sqlcmd.Parameters.AddWithValue("@rent_fee", txtRentfee.Text.Trim());
sqlcmd.Parameters.AddWithValue("@late_fee", txtLfee.Text.Trim());
sqlcmd.Parameters.AddWithValue("@due_date", txtDdate.Text.Trim());
sqlcmd.Parameters.AddWithValue("@service_charge", txtScharge.Text.Trim());
sqlcmd.Parameters.AddWithValue("@lease_id", 0);
sqlcmd.Parameters.AddWithValue("@pay_id", 0);
sqlcmd.Parameters.AddWithValue("@pay_date", txtPdate.Text.Trim());
sqlcmd.Parameters.AddWithValue("@pay_amount", txtPamount.Text.Trim());
sqlcmd.Parameters.AddWithValue("@receipt_No", txtReceipt.Text.Trim());
sqlcmd.ExecuteNonQuery();
Response.Write("window.alert('not saved');");
}
}
catch (Exception ex)
{
Response.Write("window.alert('not saved');");
}
}
protected void btnUpdate4_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection constring = new SqlConnection(CS))
{
constring.Open();
SqlCommand sqlcmd = new SqlCommand("Rento1", constring);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@rent_id", Convert.ToInt32(hfrent_id.Value == "" ? "0" : hfrent_id.Value));
sqlcmd.Parameters.AddWithValue("@rent_fee", txtRentfee.Text.Trim());
sqlcmd.Parameters.AddWithValue("@late_fee", txtLfee.Text.Trim());
sqlcmd.Parameters.AddWithValue("@due_date", txtDdate.Text.Trim());
sqlcmd.Parameters.AddWithValue("@service_charge", txtScharge.Text.Trim());
sqlcmd.Parameters.AddWithValue("@lease_id", 0);
sqlcmd.Parameters.AddWithValue("@pay_id", 0);
sqlcmd.Parameters.AddWithValue("@pay_date", txtPdate.Text.Trim());
sqlcmd.Parameters.AddWithValue("@pay_amount", txtPamount.Text.Trim());
sqlcmd.Parameters.AddWithValue("@receipt_No", txtReceipt.Text.Trim());
sqlcmd.ExecuteNonQuery();
Response.Write("window.alert('saved');");
}
}
catch (Exception ex)
{
Response.Write("window.alert('not saved');");
}
}
}
}
problem is that it is not inserting.why?
how do i make it such that lase_id in first row of Lease table will be lease id in first row of Rent table.
and Payment table is not picking rent_id either
here are tables
CREATE TABLE Lease
(
lease_id int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
initial_date varchar(255),
end_date varchar(255),
deposit varchar(255),
tenant_id int FOREIGN KEY REFERENCES Tenant(tenant_id),
Created datetime2(3) NOT NULL constraint DF_Lease_Created default (Sysdatetime()),
)
CREATE TABLE Rent
(
rent_id int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
rent_fee varchar(255) ,
late_fee varchar(255),
due_date varchar(255),
service_charge varchar(255),
lease_id int FOREIGN KEY REFERENCES Lease(lease_id),
Created datetime2(3) NOT NULL constraint DF_Rent_Created default (Sysdatetime()),
)
CREATE TABLE Payment
(
pay_id int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
pay_date varchar(255),
pay_amount varchar(255),
receipt_no varchar(255),
rent_id int FOREIGN KEY REFERENCES Rent(rent_id),
Created datetime2(3) NOT NULL constraint DF_Payment_Created default (Sysdatetime()),
)`enter code here`
What I have tried:
i have tried playing around with procedure Rento to no avail
What I have tried:
i tried removing the lease_id and see if it would insert but so far no