How to find running total in Sql Server 2012

How to find running total in Sql Server 2012

--create database which name testdb.............

create database testdb

use testdb

-- query for  create   table....

create table  Employee

(

EmpId int primary key,

Name varchar(50),

Gender varchar(50),

Salary int

)

insert into Employee (EmpId,Name,Gender,Salary) values(1,'Kiran Pal','Male',9000)

-- query 1 for  running total in Sql Server 2012

select Name,Gender,Salary,

SUM(Salary)over(order by EmpId)  from Employee

-- query 2 for  running total in Sql Server 2012 with  order by EmpId

select Name,Gender,Salary,

SUM(Salary)over(order by Salary)  from Employee

-- query 3 for  running total in Sql Server 2012 with partition

select Name,Gender,Salary,

SUM(Salary)over(Partition by Gender order by EmpId)  from Employee

Read More

Cascading ComboBox in Windows Application using C# .Net

******************** Sql Query*************...................

create database test

use test

--create table county,state,city in database...................

create table CountryDetails(CountryId int primary key,CountryName varchar(50) unique)

create table StateDetails(StateId int primary key,StateName varchar(50)unique,CountryId int foreign key references CountryDetails(CountryId) on delete cascade)

create table CityDetails(CityId int primary key,CityName varchar(50)unique,StateId int foreign key references StateDetails(StateId) on delete cascade)

--insert into tables........................

insert into CountryDetails values(1,'India')

insert into CountryDetails values(2,'England')

insert into StateDetails values(1,'Uttar Pradesh',1)

insert into StateDetails values(2,'Madhya Pradesh',1)

insert into CityDetails values(1,'Varanasi',1)

insert into CityDetails values(2,'Ghaziabad',1)

insert into CityDetails values(3,'Lucknow',1)

insert into CityDetails values(4,'Bhopal',2)

insert into CityDetails values(5,'Reewa',2)

****************** C# .Net      ******************

First we will take Windows Application and add app.config file for SqlConnection String

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name="dbcon"

     connectionString="Data Source=DESKTOP-BIISIIA;Initial Catalog=test;Integrated Security=True"

     providerName="System.Data.SqlClient" />

  </connectionStrings>

    <startup>

        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />

    </startup>

</configuration>

********************* Code for Form1.cs ***************************

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Configuration;

namespace CascadingComboBox

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

//SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-BIISIIA;Initial Catalog=test;Integrated Security=True");

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString);

private void Form1_Load(object sender, EventArgs e)

{

CountryBind();

}

// code for Country bind..................

private void CountryBind()

{

SqlCommand cmd = new SqlCommand("select * from CountryDetails", con);

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

DataRow dr = dt.NewRow();

dr.ItemArray = new object[] { 0, "Select Country" };

dt.Rows.InsertAt(dr, 0);

cbCountry.DisplayMember = "CountryName";

cbCountry.ValueMember = "CountryId";

cbCountry.DataSource = dt;

}

// code for State  bind..................

private void StateBind(int countryid)

{

SqlCommand cmd = new SqlCommand("select * from StateDetails where CountryId=@CountryId", con);

cmd.Parameters.AddWithValue("@CountryId", countryid);

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

DataRow dr = dt.NewRow();

dr.ItemArray = new object[] { 0, "Select State" };

dt.Rows.InsertAt(dr, 0);

cbState.DisplayMember = "StateName";

cbState.ValueMember = "StateId";

cbState.DataSource = dt;

}

// code for City bind..................

private void CityBind(int stateid)

{

SqlCommand cmd = new SqlCommand("select * from CityDetails where StateId=@StateId", con);

cmd.Parameters.AddWithValue("@StateId", stateid);

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

DataRow dr = dt.NewRow();

dr.ItemArray = new object[] { 0, "Select City" };

dt.Rows.InsertAt(dr, 0);

cbCity.DisplayMember = "CityName";

cbCity.ValueMember = "CityId";

cbCity.DataSource = dt;

}

private void cbCountry_SelectedIndexChanged(object sender, EventArgs e)

{

// MessageBox.Show(cbCountry.SelectedValue.ToString());

int countryid =Convert.ToInt32(cbCountry.SelectedValue);

StateBind(countryid);

}

private void cbState_SelectedIndexChanged(object sender, EventArgs e)

{

int stateid = Convert.ToInt32(cbState.SelectedValue);

CityBind(stateid);

}

}

}

Result

Read More

How to handle multiple submit Buttons with Validation on the same form MVC Razor

Note: In this topic we will discuss how to handle multiple submit buttons with types of validation required, regular expression, compare on same MVC razor form.

/****** Sql Query ************************/

CREATE TABLE [dbo].[Employee](

[EmpId] [varchar](50) NOT NULL,

[Name] [varchar](50) NULL,

[Gender] [varchar](50) NULL,

[EmailId] [varchar](50) NOT NULL,

[Password] [varchar](50) NULL,

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

[EmpId] 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

CREATE TABLE [dbo].[EmployeeInfo](

[EmpId] [varchar](50) NOT NULL,

[Address] [varchar](max) NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL,

[MobileNo] [varchar](50) NULL,

CONSTRAINT [PK_EmployeeInfo] PRIMARY KEY CLUSTERED

(

[EmpId] 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

CREATE TABLE [dbo].[CityInfo](

[CityId] [int] NOT NULL,

[CityName] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

[CityId] 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

We will take  Mvc  Application  and  add  Controller  which name is CetpaController and  using this  controller  we  will add  View  which  name is Index 

Code for Index.cshtml………………………

@{

Layout = null;

}

<!DOCTYPE html>

<html>

<head>

<meta name="viewport" content="width=device-width" />

<script src="~/Scripts/jquery-1.10.2.js"></script>

<script type="text/javascript">

$(document).ready(function()

{

$('#ddlcity').change(function (e)

{

var v = $('#ddlcity').find('option:selected').text();

$('#ddlcityText').val(v);

e.preventDefault();

});

});

</script>

<title>Index</title>

</head>

<body>

<div>

<fieldset>

@using (Html.BeginForm())

{

<table>

<tr>

<td>EmpId</td>

<td>

@Html.TextBox("txtempid")

@Html.ValidationMessage("txtempid", new { @style = "color:red" })

</td>

</tr>

<tr><td>Name</td><td>

@Html.TextBox("txtname")

@Html.ValidationMessage("txtname", new { @style = "color:red" })

</td></tr>

<tr><td>Gender</td><td>@Html.RadioButton("Gender", "Male") Male @Html.RadioButton("Gender", "Female")FeMale

@Html.ValidationMessage("Gender", new { @style = "color:red;margin-left:40px" })</td></tr>

<tr><td>Emailid</td><td>@Html.TextBox("txtemailid") @Html.ValidationMessage("txtemailid", new { @style = "color:red" })</td></tr>

<tr><td>Password</td><td>@Html.Password("txtpassword") @Html.ValidationMessage("txtpassword", new { @style = "color:red" })</td></tr>

<tr><td>Confirm Password</td><td>@Html.Password("txtcpassword") @Html.ValidationMessage("txtcpassword", new { @style = "color:red" })</td></tr>

<tr><td  colspan="2"><input type="submit" value="First" name="btn" /></td></tr>

</table>

<table>

<tr>

<td>EmpId</td>

<td>

@Html.TextBox("txtempid1")

@Html.ValidationMessage("txtempid1", new { @style = "color:red" })

</td>

</tr>

<tr><td>Address</td><td>@Html.TextBox("txtaddress")@Html.ValidationMessage("txtaddress", new { @style = "color:red" })</td></tr>

<tr><td>City</td><td>@Html.DropDownList("ddlcity", "Select")

@Html.Hidden("ddlcityText") @Html.ValidationMessage("ddlcity", new { @style = "color:red" })</td></tr>

<tr><td>Country</td><td>@Html.DropDownList("ddlcountry", new[]

{

new SelectListItem { Text="Select", Value="Select" },

new SelectListItem { Text="India", Value="India"},

new SelectListItem { Text="England", Value="England" },

new SelectListItem { Text="USA", Value="USA"}

}) @Html.ValidationMessage("ddlcountry", new { @style = "color:red" })</td></tr>

<tr><td>Mobile No</td><td>@Html.TextBox("txtmobileno")@Html.ValidationMessage("txtmobileno", new { @style = "color:red" })</td></tr>

<tr><td colspan="2"><input type="submit" value="Second" name="btn" /></td></tr>

<tr><td colspan="2">@ViewBag.Message</td></tr>

</table>

}

</fieldset>

</div>

</body>

</html>

Code for CetpaController………………………

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Text;

using System.Web.Security;

using System.Text.RegularExpressions;

using ValidationConcept.Models;

namespace ValidationConcept.Controllers

{

public class CetpaController : Controller

{

testEntities2 database = new testEntities2();

[HttpGet]

public ActionResult Index()

{

ViewBag.ddlcity = (from m in database.CityInfoes select new SelectListItem { Text = m.CityName, Value = m.CityId.ToString() }).ToList();

return View();

}

[HttpPost]

public ActionResult Index(FormCollection fc)

{

// *******************  this code for First Button....................*********//

if (fc["btn"] == "First")

{

if (string.IsNullOrEmpty(fc["txtempid"]))

{

ModelState.AddModelError("txtempid", "Please enter empid");

}

if (string.IsNullOrEmpty(fc["txtname"]))

{

ModelState.AddModelError("txtname", "Please enter name");

}

if (string.IsNullOrEmpty(fc["txtemailid"]))

{

ModelState.AddModelError("txtemailid", "Please enter emailid");

}

else if (!Regex.IsMatch(fc["txtemailid"].ToString(), @"^\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*", RegexOptions.IgnoreCase))

{

ModelState.AddModelError("txtemailid", "not validation format");

}

if (string.IsNullOrEmpty(fc["txtpassword"]))

{

ModelState.AddModelError("txtpassword", "Please enter password");

}

if (string.IsNullOrEmpty(fc["txtcpassword"]))

{

ModelState.AddModelError("txtcpassword", "Please enter Confirm password");

}

else if (!Regex.Equals(fc["txtpassword"], fc["txtcpassword"]))

{

ModelState.AddModelError("txtcpassword", "do not match password and confirm password");

}

if (string.IsNullOrEmpty(fc["Gender"]))

{

ModelState.AddModelError("Gender", "Please select gender");

}

if (ModelState.IsValid)

{

Employee emp = new Employee

{

EmpId=fc["txtempid"].ToString(),

Name = fc["txtname"].ToString(),

Gender = fc["Gender"].ToString(),

EmailId = fc["txtemailid"].ToString(),

Password = fc["txtpassword"].ToString()

};

database.Employees.Add(emp);

database.SaveChanges();

ViewBag.Message = "Saved successfully in First Table";

}

}

// *******************  this code for Second Button....................*********//

if (fc["btn"] == "Second")

{

//  string aa = fc["ddlcity"].ToString();

if (string.IsNullOrEmpty(fc["txtempid1"]))

{

ModelState.AddModelError("txtempid1", "Please enter empid");

}

if (string.IsNullOrEmpty(fc["txtaddress"]))

{

ModelState.AddModelError("txtaddress", "Please enter name");

}

if (fc["ddlcity"].ToString()== "")

{

ModelState.AddModelError("ddlcity", "Please select city");

}

if (fc["ddlcountry"].ToString() == "Select")

{

ModelState.AddModelError("ddlcountry", "Please select state");

}

if (string.IsNullOrEmpty(fc["txtmobileno"]))

{

ModelState.AddModelError("txtmobileno", "Please enter mobile no");

}

else if (!Regex.IsMatch(fc["txtmobileno"].ToString(), "^[0-9]"))

{

ModelState.AddModelError("txtmobileno", "Please enter only integer no");

}

if (ModelState.IsValid)

{

EmployeeInfo empinfo = new EmployeeInfo

{

EmpId = fc["txtempid1"].ToString(),

Address = fc["txtaddress"].ToString(),

City = fc["ddlcityText"].ToString(),

Country = fc["ddlcountry"].ToString(),

MobileNo = fc["txtmobileno"].ToString()

};

database.EmployeeInfoes.Add(empinfo);

database.SaveChanges();

ViewBag.Message = "Saved successfully in Second Table";

}

}

ViewBag.ddlcity = (from m in database.CityInfoes select new SelectListItem { Text = m.CityName, Value = m.CityId.ToString() }).ToList();

return View();

}

}

}

Result

Read More