Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
USE [Apartmentmanagementsystem]
GO
/****** Object: StoredProcedure [dbo].[Rento] Script Date: 6/16/2018 10:43:15 AM ******/
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
Posted
Updated 15-Jun-18 23:09pm
Comments
Mike V Baker 16-Jun-18 17:28pm    
Your Rent table has the foreign key lease_id that references the Lease table. I see that you're getting the rent_id from SCOPE_INDENTITY(). This isn't working because you're passing in 0 for the lease_id so the reference fails and doesn't insert the rent record. That's why it can't select the SCOPE_IDENTITY() (or the @@IDENTITY).
BTW - can interactively test a stored procedure in MS Sql Server Management Studio. Right click on the SP and choose to execute. Put in the values you're passing in from the program and run it. You'll see error messages in the Messages window.

1 solution

 
Share this answer
 
Comments
Member 11239384 16-Jun-18 5:31am    
ok so how do i implement this in the current context please give me an example of stored procedure above done correctly.I am stuck.
OriginalGriff 16-Jun-18 5:43am    
Read the link, and it's pretty obvious. It even includes an example!
Member 11239384 16-Jun-18 6:02am    
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
select lease_id from Lease where lease_id=@@identity
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

this is not working
OriginalGriff 16-Jun-18 6:12am    
Stop guessing. Start thinking.
Does that even --look-- like valid SQL INSERT syntax?
Member 11239384 16-Jun-18 7:06am    
help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900