Click here to Skip to main content
15,123,118 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 16-Jun-18 0:09am
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

   
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
OriginalGriff 16-Jun-18 7:17am
   
Stop expecting people to do it for you - this isn't complicated. A tiny amount of thinking and you would have got this an hour ago.
INSERT INTO MyTable (MyColumn) VALUES (MyValue)

Now, where in that do you think it should go?
Member 11239384 16-Jun-18 8:15am
   
thanks worked
OriginalGriff 16-Jun-18 8:19am
   
Excellent! :thumbsup:
See how easy it is?
Member 11239384 16-Jun-18 8:57am
   
yeah but follow up it has inserted null into column lease_id

should i have done something else on the parent side?
OriginalGriff 16-Jun-18 10:01am
   
Well...that's your code - you pass it through as one of the parameters...or rather you don't...
Member 11239384 16-Jun-18 10:28am
   
USE [Apartmentmanagementsystem]
GO
/****** Object: StoredProcedure [dbo].[Rento] Script Date: 6/16/2018 5:25:03 PM ******/
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

begin

insert into Rent ( rent_fee,late_fee,due_date,service_charge,lease_id) values (@rent_fee,@late_fee,@due_date,@service_charge,@@IDENTITY)
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

please show me how to do it correctly.pleeeeease
OriginalGriff 16-Jun-18 10:38am
   
Why the heck won't you think about it? You're not dim, you just insist on guessing and hoping instead of actual thinking...and don't whine - it may work on your mother but the rest of the world is less impressed...

INSERT INTO MasterTable (MyColumn) VALUES ("Hello");
INSERT INTO ForeignKeyTable (FKID, Desc) VALUES(@@IDENTITY, "A widget");


How difficult was that?

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