All important Query
select * from sys.tables
select * from fees
delete from fees where fee_id=2
select * from STUDENT_STATUS
select s.std_id,sa.SA_ID,f.fee_id,s.std_name,sa.sa_year,f.dayx+'/'+f.monthx as PaidDate,f.fee_amount from student s inner join STUDENT_STATUS sa on s.std_id=sa.SA_ST_ID inner join fees f on sa.SA_ID=f.SA_FK_ID where sa.sa_year='2017'
select count(fee_id) from fees where fee_fk_st_id=2 and monthx='9'
delete from STUDENT_STATUS where SA_ID=3
alter table fees
drop column fee_date
modified Storeprocedure
USE [schoolm]
GO
/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/28/2017 12:03:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[insert_tblfees]
(
@fee_amount float ,
@dayx nvarchar(2),
@monthx nvarchar(2),
@fee_fk_st_id int,
@SA_FK_ID INT
)
as
begin
insert into fees(fee_amount,dayx,monthx,fee_fk_st_id,SA_FK_ID)
values(@fee_amount,@dayx,@monthx,@fee_fk_st_id,@SA_FK_ID)
end
Modified Fees insertion method
public void insert_FEES(student_status sa,string id)
{
SqlConnection conn = new SqlConnection(connstring);
try
{
SqlCommand cmd = new SqlCommand("insert_tblfees", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@fee_amount", SqlDbType.Float).Value = sa.class_fees;
cmd.Parameters.Add("@dayx", SqlDbType.NVarChar).Value = System.DateTime.Now.Day.ToString();
cmd.Parameters.Add("@monthx", SqlDbType.NVarChar).Value = System.DateTime.Now.Month.ToString();
cmd.Parameters.Add("@fee_fk_st_id", SqlDbType.Int).Value =id ;
cmd.Parameters.Add("@SA_FK_ID", SqlDbType.Int).Value = sa.status_student_id;
conn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("DATA record has been inserted successfully.....");
}
catch (Exception)
{
MessageBox.Show("data is not inserted !!!");
}
finally
{
conn.Close();
}
} //method end...........
Fees Submission events code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApplication4
{
public partial class feessubmission : Form
{
viewclass vc = new viewclass();
string q;
private string connstring = ConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;
public feessubmission()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(connstring);
string sql = "select s.std_id,s.std_name,sa.SA_ID,SA_Class_id,sa.sa_year,c.classname,c.fees from student s inner join STUDENT_STATUS sa on sa.SA_ST_ID=s.std_id inner join classes c on c.class_id=sa.SA_Class_id where sa.sa_year='"+System.DateTime.Now.Year+"' and s.std_id="+textBox1.Text;
try
{
label2.Text = " ";
label3.Text = " ";
label4.Text = " ";
label5.Text = " ";
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
label2.Text =reader.GetValue(1).ToString();
label3.Text = reader.GetValue(5).ToString();
label4.Text =reader.GetValue(6).ToString();
label5.Text = reader.GetValue(2).ToString();
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("No RECORDS WERE FOUND!");
}
returnclass rc = new returnclass();
string pathquerry = rc.scalarReturn("select img_path from student_img where img_fk=" + textBox1.Text);
if (pathquerry == " ")
{
pictureBox1.Image = Image.FromFile(@"C:\Users\salman\documents\visual studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\profile.jpg");
}
else
{
pictureBox1.Image = Image.FromFile(pathquerry);
}
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
string s= rc.scalarReturn("select count(fee_id) from fees where fee_fk_st_id="+textBox1.Text+" and monthx='"+System.DateTime.Now.Month.ToString()+"'");
if (s.Equals("0"))
{
button2.Enabled = true;
label10.Text = "Unpaid!";
label10.ForeColor = System.Drawing.Color.Red;
}
else
{
label10.Text = "paid!";
button2.Enabled = false;
label10.ForeColor = System.Drawing.Color.Green;
}
}
private void button2_Click(object sender, EventArgs e)
{
student_status sa = new student_status();
sa.status_student_id =Convert.ToInt32( label5.Text);
sa.class_fees = label4.Text;
sa.class_name = label3.Text;
insert i = new insert();
i.insert_FEES(sa, textBox1.Text);
q = "select s.std_id,sa.SA_ID,f.fee_id,s.std_name,sa.sa_year,f.dayx+'/'+f.monthx as PaidDate,f.fee_amount from student s inner join STUDENT_STATUS sa on s.std_id=sa.SA_ST_ID inner join fees f on sa.SA_ID=f.SA_FK_ID where sa.sa_year='" + System.DateTime.Now.Year.ToString() + "'";
dataGridView1.DataSource = vc.showrecord(q);
}
private void feessubmission_Load(object sender, EventArgs e)
{
q = "select s.std_id,sa.SA_ID,f.fee_id,s.std_name,sa.sa_year,f.dayx+'/'+f.monthx as PaidDate,f.fee_amount from student s inner join STUDENT_STATUS sa on s.std_id=sa.SA_ST_ID inner join fees f on sa.SA_ID=f.SA_FK_ID where sa.sa_year='"+System.DateTime.Now.Year.ToString()+"'";
dataGridView1.DataSource = vc.showrecord(q);
}
}
}
