Saturday, 7 October 2017

Database Application in urdu/hindi part 18 (how to take backup of database)

SqlConnection sqlCon = new SqlConnection(@"YOURCONNECTIONSTRING");

string database = sqlCon.Database.ToString();
try
{

string cmd = "BACKUP DATABASE [" + database + "] TO DISK='" + textBox1.Text + "\\" + "database" + "-" + DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss") + ".bak'";

using(SqlCommand command = new SqlCommand(cmd,sqlCon))
{
if(sqlCon.State!=ConnectionState.Open)
{
sqlCon.Open();
}
command.ExecuteNonQuery();
sqlCon.Close();
MessageBox.Show("database backup done successefully");
BackupButton.Enabled = false;
}

}
catch
{

}

Friday, 29 September 2017

Sql Script for part -5 (sql server)

USE [master]
GO
/****** Object: Database [schoolm] Script Date: 9/28/2017 7:55:55 PM ******/
CREATE DATABASE [schoolm]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'schoolm', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'schoolm_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm_log.ldf' , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [schoolm] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [schoolm].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [schoolm] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [schoolm] SET ANSI_NULLS OFF
GO
ALTER DATABASE [schoolm] SET ANSI_PADDING OFF
GO
ALTER DATABASE [schoolm] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [schoolm] SET ARITHABORT OFF
GO
ALTER DATABASE [schoolm] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [schoolm] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [schoolm] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [schoolm] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [schoolm] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [schoolm] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [schoolm] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [schoolm] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [schoolm] SET ENABLE_BROKER
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [schoolm] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [schoolm] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [schoolm] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [schoolm] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [schoolm] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [schoolm] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [schoolm] SET RECOVERY FULL
GO
ALTER DATABASE [schoolm] SET MULTI_USER
GO
ALTER DATABASE [schoolm] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [schoolm] SET DB_CHAINING OFF
GO
ALTER DATABASE [schoolm] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [schoolm] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [schoolm]
GO
/****** Object: Table [dbo].[administator] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[administator](
[ad_id] [int] IDENTITY(1,1) NOT NULL,
[ad_name] [nvarchar](20) NOT NULL,
[ad_password] [nvarchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ad_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[classes] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[classes](
[class_id] [int] IDENTITY(1,1) NOT NULL,
[classname] [nvarchar](20) NOT NULL,
[fees] [float] NULL,
PRIMARY KEY CLUSTERED
(
[class_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[fees] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fees](
[fee_id] [int] IDENTITY(1,1) NOT NULL,
[fee_amount] [float] NOT NULL,
[fee_fk_st_id] [int] NULL,
[SA_FK_ID] [int] NULL,
[dayx] [nvarchar](2) NULL,
[monthx] [nvarchar](2) NULL,
PRIMARY KEY CLUSTERED
(
[fee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[std_id] [int] IDENTITY(1,1) NOT NULL,
[std_name] [nvarchar](20) NOT NULL,
[std_fname] [nvarchar](20) NOT NULL,
[std_gender] [nvarchar](6) NOT NULL,
[std_address] [nvarchar](100) NOT NULL,
[std_admissiondate] [nvarchar](20) NOT NULL,
[std_ad_fk_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[std_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[student_img] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_img](
[img_id] [int] IDENTITY(1,1) NOT NULL,
[img_path] [nvarchar](max) NOT NULL,
[img_fk] [int] NULL,
PRIMARY KEY CLUSTERED
(
[img_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object: Table [dbo].[STUDENT_STATUS] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[STUDENT_STATUS](
[SA_ID] [int] IDENTITY(1,1) NOT NULL,
[SA_ST_ID] [int] NULL,
[SA_Class_id] [int] NULL,
[sa_year] [nvarchar](5) NULL,
PRIMARY KEY CLUSTERED
(
[SA_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[administator] ON

INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (1, N'salmnan', N'password123')
INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (2, N'raza', N'password113')
SET IDENTITY_INSERT [dbo].[administator] OFF
SET IDENTITY_INSERT [dbo].[classes] ON

INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (1, N'Montessori', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (2, N'I', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (3, N'II', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (4, N'III', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (5, N'IV', 4500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (6, N'V', 4500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (7, N'VI', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (8, N'VII', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (9, N'VIII', 5500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (10, N'IX', 6000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (11, N'X', 6000)
SET IDENTITY_INSERT [dbo].[classes] OFF
SET IDENTITY_INSERT [dbo].[fees] ON

INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1007, 4000, 2, 1, N'28', N'9')
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1008, 4000, 3, 4, N'28', N'9')
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1009, 5000, 2005, 5, N'28', N'9')
SET IDENTITY_INSERT [dbo].[fees] OFF
SET IDENTITY_INSERT [dbo].[student] ON

INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2, N'ahsan', N'ahmed', N'male', N't-3t2 north khi', N'8/26/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3, N'sunny', N'ahmed', N'male', N'e-3q2 north khi', N'8/26/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2004, N'jen', N'peter', N'male', N'i-32 street 5', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2005, N'sami', N'ali', N'male', N'y-434', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3004, N'ali ahmed', N'ahmed', N'male', N'y-3232', N'9/14/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3006, N'tony', N'james', N'male', N'u-454', N'9/19/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3007, N'sami', N'ahmed', N'male', N'y-565', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3008, N'raza ahmed', N'masood', N'male', N'y-6546', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3009, N'sana', N'ali', N'female', N'ds-6654', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3010, N'sana', N'ali', N'female', N'ds-6654', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3011, N'moosa', N'ali', N'male', N't-545', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3012, N'test', N'test2', N'male', N'i-4324', N'9/21/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (4007, N'ali', N'ahmed raza', N'male', N'r-654', N'9/23/2017', 2)
SET IDENTITY_INSERT [dbo].[student] OFF
SET IDENTITY_INSERT [dbo].[student_img] ON

INSERT [dbo].[student_img] ([img_id], [img_path], [img_fk]) VALUES (6, N'C:\Users\salman\Documents\Visual Studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\2005.jpg', 2005)
SET IDENTITY_INSERT [dbo].[student_img] OFF
SET IDENTITY_INSERT [dbo].[STUDENT_STATUS] ON

INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (1, 2, 2, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (2, 2004, 2, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (4, 3, 4, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (5, 2005, 1, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (6, 3004, 6, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (7, 3006, 6, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (8, 2, 1, N'2016')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (9, 3, 3, N'2016')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (10, 3009, 7, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (11, 3011, 11, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (12, 3012, 9, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (1010, 4007, 4, N'2017')
SET IDENTITY_INSERT [dbo].[STUDENT_STATUS] OFF
SET ANSI_PADDING ON

GO
/****** Object: Index [UQ__administ__84D9FB7AC548E39F] Script Date: 9/28/2017 7:55:55 PM ******/
ALTER TABLE [dbo].[administator] ADD UNIQUE NONCLUSTERED
(
[ad_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([fee_fk_st_id])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([SA_FK_ID])
REFERENCES [dbo].[STUDENT_STATUS] ([SA_ID])
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD FOREIGN KEY([std_ad_fk_id])
REFERENCES [dbo].[administator] ([ad_id])
GO
ALTER TABLE [dbo].[student_img] WITH CHECK ADD FOREIGN KEY([img_fk])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[STUDENT_STATUS] WITH CHECK ADD FOREIGN KEY([SA_Class_id])
REFERENCES [dbo].[classes] ([class_id])
GO
ALTER TABLE [dbo].[STUDENT_STATUS] WITH CHECK ADD FOREIGN KEY([SA_ST_ID])
REFERENCES [dbo].[student] ([std_id])
GO
/****** Object: StoredProcedure [dbo].[delete_student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[delete_student]
(
@std_id int
)
as
begin

delete from student_img where img_fk=@std_id
delete from student where std_id=@std_id

end

GO
/****** Object: StoredProcedure [dbo].[insert_student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student]
(
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100),
@std_admissiondate nvarchar(20),
@std_ad_fk_id int

)
as

begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)

end

GO
/****** Object: StoredProcedure [dbo].[insert_student_img] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student_img]
(
@img_path nvarchar(max),
@img_fk int
)
as

begin

insert into student_img(img_path,img_fk)
values(@img_path,@img_fk)

end

GO
/****** Object: StoredProcedure [dbo].[insert_STUDENT_STATUS] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_STUDENT_STATUS]
(
@SA_ST_ID INT ,
@SA_Class_id INT ,
@sa_year nvarchar(5)

)
as

begin

insert into STUDENT_STATUS(SA_ST_ID,SA_Class_id,sa_year)
VALUES(@SA_ST_ID,@SA_Class_id,@sa_year)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tbladmin] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tbladmin]
(
@ad_name nvarchar(20) ,
@ad_password nvarchar(20)

)
as

begin
insert into administator(ad_name,ad_password)
values(@ad_name,@ad_password)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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

GO
/****** Object: StoredProcedure [dbo].[insert_tblstudent] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tblstudent]
(
@std_name nvarchar(20) ,
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100) ,
@std_admissiondate nvarchar(20),
@std_ad_fk_id int
)
as
begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)
end

GO
/****** Object: StoredProcedure [dbo].[UPDATE_STUDENT] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[UPDATE_STUDENT]
(
@std_id int,
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100)
)
as

begin

update student

set std_name=@std_name,
std_fname=@std_fname,
std_gender=@std_gender,
std_address=@std_address

where std_id=@std_id

end

GO
USE [master]
GO
ALTER DATABASE [schoolm] SET READ_WRITE
GO

Thursday, 28 September 2017

Dbms Application # 15 (how to find fees Defaulter in recrods/Table Alteration/Removing anamolies)

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]

/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/28/2017 12:03:43 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER proc [dbo].[insert_tblfees]

(

@fee_amount float ,

@dayx nvarchar(2),

@monthx nvarchar(2),

@fee_fk_st_id int,

@SA_FK_ID INT

)

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)

{

dataGridView1.DataSource = vc.showrecord(q);

}

Tuesday, 26 September 2017

How to use Video Control in html-5

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Video and audion controls</title>
<style>
body
{
margin:0 auto;
padding:0px;
background-color:#F00;

}
#wrapper
{
width:80%;
margin:0 auto;
height:900px;
background-color:#FFF;
border:1px solid black;

}
header
{
width:100%;
height:100px;
background-color:#CCC;
margin-bottom:100px;

}

</style>

</head>

<body>

<div id="wrapper">
<header>

<DIV style="WIDTH:300PX;HEIGHT:100%;">
<img style="width:200px;height:100%" src="img/you.png"/>
</DIV>

</header>

<div style="WIDTH:100%;HEIGHT:500px; margin:0 auto; border:1px solid black ">

<video controls style="width:100%;height:100%">

<source src="video/carvideo.mp4" type="video/mp4" >
</video>

</div>

<div style="WIDTH:100%;HEIGHT:auto; margin:0 auto; border:1px solid black ">
<video controls style="width:25%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>

<video controls style="width:24%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>

<video controls style="width:24%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>
<video controls style="width:24%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>

</div>

</div>

</body>
</html>

Quiz Application# 1 (Databse Design)

create table admin_athu
(
ad_id int primary key identity ,
ad_user nvarchar(20) not null,
ad_password nvarchar(20) not null

)

create table student_record
(
std_id int primary key identity ,
std_name nvarchar(20) not null,
std_batchcode nvarchar(20) not null,
std_password nvarchar(20) not null,
std_ad_id int foreign key references admin_athu(ad_id )
)

create table exams
(
ex_id int primary key identity ,
exam_name nvarchar(20) not null
)

create table questions
(
q_id int primary key identity,
q_title nvarchar(max) not null,
q_opa nvarchar(200) not null,
q_opb nvarchar(200) not null,
q_opc nvarchar(200) not null,
q_opd nvarchar(200) not null,
q_opcorrect nvarchar(200) not null,
q_addeddate nvarchar(20) not null,
q_fk_ad int foreign key references admin_athu(ad_id ),
q_fk_ex int foreign key references exams(ex_id)
)

Sunday, 24 September 2017

How to create Attractive Forms in html-5 using some Css code

Copy and paste the code in your html file:

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Forms </title>

<style>
body
{ margin:0 auto;
padding:0px;
background-color:#99C;
background-image:url(img/1.jpg);
background-size:cover;
}

form
{
margin:0 auto;
width:350px;
height:600px;
background-color:#F6F;
margin-top:100px;
border-radius:50px 20px 50px 20px;
color:#93F;
border:8px inset #90F;
font-style:italic;
font-weight:bold;
opacity:0.6;

background-image:url(img/2.jpg);
}
TD{
padding:20PX;}

</style>

</head>

<body>

<form>
<br>
<br>

<table>

<tr>
<td> User Name: </td>

<td>
<input type="text" size="15" required="required" maxlength="10"/>

</td>
</tr>

<tr>
<td> Password: </td>
<td> <input type="password" size="15" required="required"/> </td>
</tr>

<tr>
<td> Gender: </td>
<td>
<input type="radio"value="male" name="gender"/>Male <input type="radio" value="Female" name="gender"/>Female
</td>

</tr>

<tr>

<td>Education </td>
<td>
<input type="checkbox" name="EDU" value="SSC"/>SSC
<input type="checkbox" name="EDU" value="HSC"/>HSC
<input type="checkbox" name="EDU" value="GRADUATION"/>GRAD
</td>

</tr>

<TR>

<TD> City: </TD>
<TD>
<select>
<option value="khi">Karachi</option>
<option value="lhr">Lahore</option>
<option value="isb">Islamabad</option>
<option value="MUL">Multan </option>

</select>

</TD>

</TR>

<tr>

<td>Upload Resume: </td>
<td> <input type="file"/> </td>

</tr>

<tr>

<td> </td>
<td><button> Submit!</button> </td>

</tr>

</table>

</form>

</body>
</html>

How to export From sql records to data Grid view

Database to Data Grid view:

string connectionString = null; //connection string

string sql = null; //query from db

connectionString = "Data Source=LAB6-168;Initial Catalog=schoolm;User ID=sa;Password=aptech";

SqlConnection cnn = new SqlConnection(connectionString); //con object

cnn.Open(); //coneection open form db

sql = "SELECT * FROM student"; //query

SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn); //reads all records from db

DataSet ds = new DataSet();

dscmd.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

The Concept Academy By Ideas Solution