Dot Net Vellore: April 2016

Tuesday, 5 April 2016

Adding temporary values from multiple textbox to gridview and multi rows inserted to database.

Screen Shot 1:

Screen Shot 2:

Screen Shot 3:

aspx code:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="grn.aspx.cs" Inherits="grn" %>

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<%@ Register assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">

.style2

{

border: 1px solid black;

margin-left: 0px;

margin-top: 6px;

margin-bottom: 0px;

}

.style3

{

color: #CC0000;

}

.style4

{

color: #000099;

}

.style5

{

width: 262px;

}

.style6

{

height: 33px;

}

.style7

{

width: 262px;

height: 33px;

}

</style>

:focus {

background-color:white;

}

</style>

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

</asp:ToolkitScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<asp:PostBackTrigger ControlID="printbtn" />

</Triggers>

<td align="right"><span style="color: #FF6600; font-size: large;">Login Name:   </span></td>

<td>

<asp:Label ID="username" runat="server" CssClass="style4" ForeColor="#FF6600"></asp:Label>

</td>

<td align="right"><span style="color: #FF6600">Organization Name:</span></td>

<td>

<asp:Label ID="ddlorgname" runat="server" CssClass="style4" ForeColor="#FF6600"></asp:Label>

</td>

</tr>

</table>

<div>

<tr><td class="style3"><strong>GRN NUMBER</strong></td><td>

<asp:Label ID="Label1" runat="server" style="color: #CC0000; font-weight: 700;"></asp:Label>

</td><td>

 </td><td> </td><td class="style5">

 </td><td>

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

<tr>

<td>

Date:</td>

<td>

<asp:TextBox ID="Txtdate" runat="server" CssClass="textbox" Height="25px"

Width="200px" AutoPostBack="True"></asp:TextBox>

<asp:CalendarExtender ID="Txtdate_CalendarExtender" runat="server"

Enabled="True" Format="dd-MM-yyyy" TargetControlID="Txtdate">

</asp:CalendarExtender>

</td>

<td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"

ControlToValidate="Txtdate" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td>

<td>

 </td>

<td>

CustomerName</td>

<asp:DropDownList ID="deptdrop" runat="server" AutoPostBack="True"

CssClass="style2" Height="25px" Width="200px">

</asp:DropDownList>

</td>

<td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server"

ControlToValidate="deptdrop" CssClass="style3" ErrorMessage="*"

InitialValue="0" style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td>

<td>

 </td>

</tr>

<tr><td class="style4">Problem.Material Name</td><td>

<asp:TextBox ID="pmetxt" runat="server" TextMode="MultiLine"

CssClass="textbox" Height="52px" Width="197px"></asp:TextBox></td>

<td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"

ControlToValidate="pmetxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td>

<td>

 </td>

<td class="style4">Problem.Material

.Model.No</td><td class="style5">

<asp:TextBox ID="pmetmonotxt" runat="server" CssClass="textbox" Height="25px"

Width="200px"></asp:TextBox></td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator11" runat="server"

ControlToValidate="pmetmonotxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

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

<tr><td class="style4">Problem.Material .S.No</td><td>

<asp:TextBox ID="pmsnotxt" runat="server" CssClass="textbox" Height="25px"

Width="200px"></asp:TextBox></td>

<td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"

ControlToValidate="pmsnotxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td>

<td>

 </td>

<td class="style4">Problem</td><td class="style5">

<asp:TextBox ID="prodettxt" runat="server" TextMode="MultiLine"

CssClass="textbox" Height="52px" Width="197px"></asp:TextBox></td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator12" runat="server"

ControlToValidate="prodettxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

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

<tr>

Material Recive Date

</td>

<td>

<asp:TextBox ID="datetimetxt" runat="server" CssClass="style2" Height="25px"

Width="200px"></asp:TextBox>

<asp:CalendarExtender ID="datetimetxt_CalendarExtender" runat="server"

Enabled="True" Format="dd-MM-yyyy" TargetControlID="datetimetxt">

</asp:CalendarExtender>

</td>

<td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator14" runat="server"

ControlToValidate="datetimetxt" CssClass="style3" ErrorMessage="*"

InitialValue="0" style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td>

<td>

 </td>

 </td>

 </td>

<td>

 </td>

<td>

 </td>

</tr>

<tr><td>Stand By</td><td>

<asp:DropDownList ID="standbydrop" runat="server" CssClass="style2"

Height="25px"

Width="200px" onselectedindexchanged="standbydrop_SelectedIndexChanged"

AutoPostBack="True">

<asp:ListItem Value="0">--Select--</asp:ListItem>

<asp:ListItem Value="Y">Yes</asp:ListItem>

<asp:ListItem Value="N">No</asp:ListItem>

</asp:DropDownList>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"

ControlToValidate="standbydrop" CssClass="style3" ErrorMessage="*"

InitialValue="0" style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td><td>

 </td><td> </td><td class="style5">

 </td><td>

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

<tr id="stnd" runat="server" visible="false"><td class="style3">Replace Material Name</td><td>

<asp:TextBox ID="rmnametxt" runat="server" CssClass="textbox" Height="52px"

TextMode="MultiLine" Width="197px"></asp:TextBox>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server"

ControlToValidate="rmnametxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td><td>

 </td><td class="style3">Model No</td><td class="style5">

<asp:TextBox ID="repmodnotxt" runat="server" CssClass="textbox" Height="25px"

Width="200px"></asp:TextBox>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator13" runat="server"

ControlToValidate="repmodnotxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

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

<asp:TextBox ID="rmetsnotxt" runat="server" CssClass="textbox" Height="25px"

Width="200px"></asp:TextBox>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server"

ControlToValidate="rmetsnotxt" CssClass="style3" ErrorMessage="*"

style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td><td>

 </td><td class="style3">Material Reference No</td><td class="style5">

<asp:TextBox ID="matnotxt" runat="server" CssClass="textbox" Height="25px"

Width="200px" ontextchanged="txtmatno_TextChanged" AutoPostBack="true" ></asp:TextBox>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator15" runat="server"

ControlToValidate="matnotxt" ErrorMessage="*" ValidationGroup="G1" ForeColor="#CC0000"></asp:RequiredFieldValidator>

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

<tr><td>StaffName</td><td>

<asp:DropDownList ID="staffdrop" runat="server" Height="25px" Width="200px"

CssClass="style2" AutoPostBack="True">

</asp:DropDownList>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server"

ControlToValidate="staffdrop" CssClass="style3" ErrorMessage="*"

InitialValue="0" style="font-weight: 700" ValidationGroup="G1"></asp:RequiredFieldValidator>

</td><td>

 </td><td class="style5">

<asp:Label ID="lbler" runat="server" ForeColor="#CC0000" Text="Label"></asp:Label>

</td><td>

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

<tr>

<td>

 </td>

 </td>

<td>

 </td>

<td>

 </td>

<asp:Button ID="addbtn" runat="server" onclick="addbtn_Click" style="font-size: large" Text="Add"

ValidationGroup="G1" Width="89px" Height="40px" />

</td>

 </td>

<td>

 </td>

<td>

 </td>

</tr>

<tr id="retun" runat="server" visible="false"><td>Reparing Status</td><td>

<asp:TextBox ID="repairtxt" runat="server" TextMode="MultiLine"

CssClass="textbox" Height="52px" Width="197px"></asp:TextBox></td><td>

 </td><td>

 </td><td>Return Details</td><td class="style5">

<asp:TextBox ID="retdettxt" runat="server" TextMode="MultiLine"

CssClass="textbox" Height="52px" Width="197px"></asp:TextBox></td><td>

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

 </td><td class="style6">

 </td><td class="style7">

</td><td class="style6">

</td><td class="style6"></td></tr>

 </td><td align="center"> </td><td> </td></tr>

</table>

</fieldset>

<div>

<asp:GridView ID="stockgrid" runat="server" AutoGenerateDeleteButton="True">

</asp:GridView>

</div>

<br />

<div>

<asp:Button ID="savebtn" runat="server" Height="39px" onclick="savebtnn_Click"

style="font-size: large" Text="Save" Width="93px" />

<asp:Label ID="lblmain" runat="server"></asp:Label>

<asp:Button ID="printbtn" style="font-size: large" runat="server" Height="40px" Text="Print"

Width="95px" onclick="printbtn_Click" />

</div>

</center>

<br />

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"

AutoDataBind="true" />

<br />

</div>

</ContentTemplate>

</asp:UpdatePanel>

</asp:Content>

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.Configuration;

using System.IO;

using System.Net;

using System.Data;

using MySql.Data.MySqlClient;

using System.Globalization;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

public partial class grn : System.Web.UI.Page

{

string createdon;

string ipaddr;

DataTable tbs = new DataTable();

DataRow drow;

protected void Page_Load(object sender, EventArgs e)

{

lbler.Visible = false;

string strHostName = null;

strHostName = System.Net.Dns.GetHostName();

ipaddr = System.Net.Dns.GetHostEntry(strHostName).AddressList[0].ToString();

ddlorgname.Text = Session["location"].ToString();

createdon = DateTime.Now.ToString("yyyy-MM-dd");

username.Text = Session["user"].ToString();

Txtdate_CalendarExtender.StartDate = DateTime.Now;

if (!IsPostBack)

{

staff();

dept();

create_table();

Txtdate.Focus();

savebtn.Visible = false;

printbtn.Visible = false;

}

protected void standbydrop_SelectedIndexChanged(object sender, EventArgs e)

{

if (standbydrop.Text == "Y")

{

stnd.Visible = true;

stnd1.Visible = true;

}

else

{

stnd.Visible = false;

stnd1.Visible = false;

}

public void staff()

{

string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;

MySqlConnection myConnection = new MySqlConnection(connString);

myConnection.Open();

MySqlCommand cmd = new MySqlCommand("select staffname,loginname from volsstaff order by staffname asc", myConnection);

MySqlDataAdapter ad = new MySqlDataAdapter(cmd);

DataSet ds = new DataSet();

ad.Fill(ds);

staffdrop.DataSource = ds;

staffdrop.DataTextField = "staffname";

staffdrop.DataValueField = "loginname";

staffdrop.DataBind();

staffdrop.Items.Insert(0, new ListItem("--Select--", "0"));

myConnection.Close();

}

public void dept()

{

string connString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

MySqlConnection myConnection = new MySqlConnection(connString);

myConnection.Open();

MySqlCommand cmdqw = new MySqlCommand("select departmentname from dept where orgname='" + ddlorgname.Text + "' or departmentname='Others' order by departmentname asc", myConnection);

MySqlDataAdapter adap = new MySqlDataAdapter(cmdqw);

DataSet ds = new DataSet();

adap.Fill(ds);

deptdrop.DataSource = ds;

deptdrop.DataTextField = "departmentname";

//ddlrack.DataValueField = "uniqueboxcode";

deptdrop.DataBind();

deptdrop.Items.Insert(0, new ListItem("--Select--", "0"));

myConnection.Close();

}

public void createtablecategory()

{

}

protected void create_table()

{

tbs.Columns.Add("Date", typeof(string));

tbs.Columns.Add("Customer Name", typeof(string));

tbs.Columns.Add("Problem.Material Name", typeof(string));

tbs.Columns.Add("Problem.Material .Model.No", typeof(string));

tbs.Columns.Add("Problem.Material .S.No", typeof(string));

tbs.Columns.Add("Problem", typeof(string));

tbs.Columns.Add("Stand By", typeof(string));

tbs.Columns.Add("Replace Material Name", typeof(string));

tbs.Columns.Add("Model No", typeof(string));

tbs.Columns.Add("S.No", typeof(string));

tbs.Columns.Add("StaffName", typeof(string));

tbs.Columns.Add("Material Recive Date", typeof(string));

tbs.Columns.Add("Reference ID", typeof(string));

//tbs.Columns.Add("Reparing Status", typeof(string));

//tbs.Columns.Add("Return Details", typeof(string));

//tbs.Columns.Add("Date ", typeof(string));

stockgrid.DataSource = tbs;

stockgrid.DataBind();

ViewState["stockdetails"] = tbs;

}

protected void addbtn_Click(object sender, EventArgs e)

{

tbs = (DataTable)ViewState["stockdetails"];

drow = tbs.NewRow();

drow["Date"] = Txtdate.Text;

drow["Customer Name"] = deptdrop.SelectedItem.ToString();

drow["problem.Material Name"] = pmetxt.Text;

drow["Problem.Material .Model.No"] = pmetmonotxt.Text;

drow["Problem.Material .S.No"] = pmsnotxt.Text;

drow["Problem"] = prodettxt.Text;

drow["Stand By"] = standbydrop.SelectedItem.Text;

drow["Replace Material Name"] = rmnametxt.Text;

drow["Model No"] = repmodnotxt.Text;

drow["S.No"] = rmetsnotxt.Text;

drow["StaffName"] = staffdrop.SelectedItem.ToString();

drow["Material Recive Date"] = datetimetxt.Text;

drow["Reference ID"] = matnotxt.Text;

// drow["Return Details"]=retdettxt.Text;

// drow["Date"]=

tbs.Rows.Add(drow);

stockgrid.DataSource = tbs;

stockgrid.DataBind();

Txtdate.Text = "";

deptdrop.SelectedValue = "0";

pmetxt.Text = "";

pmetmonotxt.Text = "";

pmsnotxt.Text = "";

prodettxt.Text = "";

datetimetxt.Text = "";

standbydrop.SelectedValue = "0";

rmnametxt.Text = "";

repmodnotxt.Text = "";

rmetsnotxt.Text = "";

staffdrop.SelectedValue = "0";

repairtxt.Text = "";

retdettxt.Text = "";

matnotxt.Text = "";

savebtn.Visible = true;

printbtn.Visible = true;

}

protected void printbtn_Click(object sender, EventArgs e)

{

string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;

MySqlConnection myConnection = new MySqlConnection(connString);

myConnection.Open();

MySqlCommand cmd12p = new MySqlCommand("select grnno,grnumber,DATE_FORMAT(grndate, '%m-%d-%Y') AS 'grndate',deptname,upper(CONCAT_ws('',pmdetails,'/ ',pmmno,'/ ',pmsno)) 'pmdetails',problem,upper(CONCAT_ws('',rmdetails,'/ ',rmmno,'/ ',rmsno)) 'rmdetails',staffname,role,COALESCE(printon, '" + createdon + "') 'printon',COALESCE(printby, '" + username.Text + "') 'printby',COALESCE(printat, '" + ipaddr + "') 'printat' from grntable where grnumber='" + Label1.Text + "' and fflag='N'", myConnection);

MySqlDataAdapter da12p = new MySqlDataAdapter(cmd12p);

DataSet ds12p = new DataSet();

da12p.Fill(ds12p);

ReportDocument rddp = new ReportDocument();

rddp.Load(Server.MapPath(@"crystalreport.rpt"));

rddp.SetDataSource(ds12p.Tables[0]);

CrystalReportViewer1.ReportSource = rddp;

myConnection.Close();

rddp.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "EXPORTEDREPORT");

}

protected void txtmatno_TextChanged(object sender, EventArgs e)

{

if (Page.IsPostBack == true)

{

string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;

MySqlConnection myConnection = new MySqlConnection(connString);

using (var cmd = new MySqlCommand("select matno from sbm where matno=@matno", myConnection))

{

myConnection.Open();

cmd.Parameters.AddWithValue("@matno", matnotxt.Text);

using (var dr = cmd.ExecuteReader())

{

if (dr.HasRows)

{

//lbler.Visible = true;

//lbler.Text = "already exists";

}

else

{

lbler.Visible = true;

lbler.Text = "doesn't exists";

}

myConnection.Close();

}

protected void savebtnn_Click(object sender, EventArgs e)

{

string grno, cname, pmdet, pmmno, pmsno, problem, standby, rmdet, rmmno, rmsno, staffname, referenceid;

string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;

MySqlConnection myConnection = new MySqlConnection(connString);

myConnection.Open();

MySqlTransaction tran = myConnection.BeginTransaction();

try

{

MySqlCommand cmd89 = new MySqlCommand("select count(*) from grnno", myConnection);

int count = Convert.ToInt32(cmd89.ExecuteScalar());

if (count == 0)

{

grno = "GRNR-1001";

}

else

{

int dg = count + 1 + 1000;

grno = "GRNR-" + dg.ToString();

}

for (int i = 0; i < stockgrid.Rows.Count; i++)

{

string sdate = stockgrid.Rows[i].Cells[1].Text;

DateTime recdt = DateTime.ParseExact(sdate, "dd-MM-yyyy", CultureInfo.InvariantCulture);

string dates = recdt.ToString("yyyy-MM-dd");

cname = stockgrid.Rows[i].Cells[2].Text;

pmdet = stockgrid.Rows[i].Cells[3].Text;

pmmno = stockgrid.Rows[i].Cells[4].Text;

pmsno = stockgrid.Rows[i].Cells[5].Text;

problem = stockgrid.Rows[i].Cells[6].Text;

standby = stockgrid.Rows[i].Cells[7].Text;

rmdet = stockgrid.Rows[i].Cells[8].Text;

rmmno = stockgrid.Rows[i].Cells[9].Text;

rmsno = stockgrid.Rows[i].Cells[10].Text;

staffname = stockgrid.Rows[i].Cells[11].Text;

string sdate1 = stockgrid.Rows[i].Cells[12].Text;

referenceid = stockgrid.Rows[i].Cells[13].Text;

DateTime recdt1 = DateTime.ParseExact(sdate1, "dd-MM-yyyy", CultureInfo.InvariantCulture);

string dates1 = recdt.ToString("yyyy-MM-dd");

MySqlCommand cmd = new MySqlCommand("insert into grntable(grnumber,grndate,deptname,pmdetails,pmmno,pmsno,standby,rmdetails,rmmno,rmsno,staffname,createdon,createdby,createdat,problem,role,promatrcvdate,trackitem,referenceid)values('" + grno + "','" + dates + "','" + cname + "','" + pmdet + "','" + pmmno + "','" + pmsno + "','" + standby + "','" + rmdet + "','" + rmmno + "','" + rmsno + "','" + staffname + "','" + createdon + "','" + username.Text + "','" + ipaddr + "','" + problem + "','" + ddlorgname.Text + "','" + dates1 + "','GRC','" + referenceid + "')", myConnection, tran);

cmd.ExecuteNonQuery();

}

MySqlCommand cmd87 = new MySqlCommand("insert into grnno(date)values('" + createdon + "')", myConnection, tran);

cmd87.ExecuteNonQuery();

tran.Commit();

Label1.Text = grno;

lblmain.Visible = true;

lblmain.Text = "GRN Submited Sucessfully";

myConnection.Close();

}

catch (Exception)

{

tran.Rollback();

lblmain.Visible = true;

lblmain.Text = "GRN Submited Failed";

myConnection.Close();

}

Monday, 4 April 2016

How to check if a value already exists in my database or checking user name availability

(Note: I have table column name matno with some values like SB-1003,SB-1004.....etc )

See demo video:

Screen Shot 1:

Screen Shot 2:

Screen Shot 3:

Aspx Code :

(for Text box and Label and Required Field Validator):-

</td><td>

 </td><td class="style3">Material Reference No</td><td class="style5">

<asp:TextBox ID="matnotxt" runat="server" CssClass="textbox" Height="25px"

Width="200px" ontextchanged="txtmatno_TextChanged" AutoPostBack="True" ></asp:TextBox>

</td><td>

<asp:RequiredFieldValidator ID="RequiredFieldValidator15" runat="server"

ControlToValidate="matnotxt" ErrorMessage="*" ForeColor="#CC0000"></asp:RequiredFieldValidator>

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

<asp:Label ID="lbler" runat="server" ForeColor="#CC0000" Text="Label"></asp:Label>

C# Code :

(For text box changed event):-

protected void txtmatno_TextChanged(object sender, EventArgs e)

{

if (Page.IsPostBack == true)

{

string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;

MySqlConnection myConnection = new MySqlConnection(connString);

using (var cmd = new MySqlCommand("select matno from sbm where matno=@matno", myConnection))

{

myConnection.Open();

cmd.Parameters.AddWithValue("@matno", matnotxt.Text);

using (var dr = cmd.ExecuteReader())

{

if (dr.HasRows)

{

lbler.Visible = true;

lbler.Text = "already exists";

}

else

{

lbler.Visible = true;

lbler.Text = "doesn't exists";

}

myConnection.Close();

}

Multiple Search values added into single Grid view and update using check box selection :

(Note: I have use to connect the database in Mysql )

See below demo video for reference :

Click to play video

Screen Shot 1:

Screen Shot 2:

Screen Shot 3:

Screen Shot 4:

Codes:

Design Picture :

Fist you need to design the page like in Visual Studio 2010.

aspx code:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="grnreceive.aspx.cs" Inherits="grnreceive" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<style type="text/css">
.style1
{
height: 42px;
}
</style>
<style type="text/css">
:focus {
background-color:white;

}

</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<center> <h3> GRN RECEIVE </h3></center>

<center>
<table style="height: 61px; width: 393px">
<tr>
<td class="style1">
 
Enter Material Number</td>
<td class="style1">
<asp:TextBox ID="txtsrch" runat="server" Height="25px" Width="127px"></asp:TextBox>
 
</td>
<td class="style1">
 <asp:Button ID="bnadd" runat="server" Text="Add" onclick="bnadd_Click"
Height="30px" style="font-size: large" />
 </td>
</tr>
<tr>
<td colspan="3">
 
 
<asp:Label ID="lblerror" runat="server" Text="Label"></asp:Label>
 
</td>
</tr>
</table>
</center>
<br />
<br />
<center>
<asp:GridView ID="grdvw" runat="server">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="checkboxalldelete" runat="server" AutoPostBack="true" OnCheckedChanged="checkrow" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="checkdelete" runat="server" AutoPostBack="true" /></ItemTemplate>
<HeaderStyle HorizontalAlign="Justify" />
</asp:TemplateField>
</Columns>
</asp:GridView>
</center>

<br />
<br />
<center>
<asp:Button ID="bnsave" runat="server" Text="Save" Height="30px" style="font-size: large" onclick="bnsave_Click" />
<br />
</center>
<br />
<br />
</asp:Content>

C# code:
(Rename it your lables , textboxs,buttons,gridviews)

Like Below:

label1=lblerror,
Textbox=txtsrch,
Gridview1=grvw,

Button=grdvw.

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 MySql.Data.MySqlClient;
using System.Globalization;
using System.Configuration;

public partial class grnreceive : System.Web.UI.Page
{
string createdon;
string ipaddr;
DataTable tbs = new DataTable();
DataRow drow;

protected void Page_Load(object sender, EventArgs e)
{

string strHostName = null;
strHostName = System.Net.Dns.GetHostName();
ipaddr = System.Net.Dns.GetHostEntry(strHostName).AddressList[0].ToString();
createdon = DateTime.Now.ToString("yyyy-MM-dd");
lblerror.Visible = false;
if(!IsPostBack)
{
txtsrch.Focus();
create_table();

}

}
protected void create_table()
{
tbs.Columns.Add("GRNNO", typeof(string));
tbs.Columns.Add("GRN DATE", typeof(string));
tbs.Columns.Add("DEPARTMENT NAME", typeof(string));
tbs.Columns.Add("PROBLEM METERIAL DETAILS", typeof(string));
tbs.Columns.Add("PROBLEM", typeof(string));
tbs.Columns.Add("STAND BY METERIAL DETAILS", typeof(string));
tbs.Columns.Add("STAFF NAME", typeof(string));
tbs.Columns.Add("TRACK ITEM", typeof(string));
grdvw.DataSource = tbs;
grdvw.DataBind();
ViewState["table"] = tbs;
}
protected void bnadd_Click(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(connString);
myConnection.Open();
tbs = (DataTable)ViewState["table"];
drow = tbs.NewRow();

MySqlCommand cmd = new MySqlCommand("select grnno,grndate,deptname,pmdetails,problem,standby,staffname,trackitem from grntable where trackitem='DSR' and grnno='" +txtsrch.Text + "'", myConnection);
MySqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())
{

string grn = dr["grnno"].ToString();
string grn1 = dr["grndate"].ToString();
string grn2 = dr["deptname"].ToString();
string grn3 = dr["pmdetails"].ToString();
string grn4 = dr["problem"].ToString();
string grn5 = dr["standby"].ToString();
string grn6 = dr["staffname"].ToString();
string grn7 = dr["trackitem"].ToString();
string grn8 = txtsrch.Text;
drow["GRNNO"] = grn;
drow["GRN DATE"] = grn1;
drow["DEPARTMENT NAME"] = grn2;
drow["PROBLEM METERIAL DETAILS"] = grn3;
drow["PROBLEM"] = grn4;
drow["STAND BY METERIAL DETAILS"] = grn5;
drow["STAFF NAME"] = grn6;
drow["TRACK ITEM"] = grn7;
tbs.Rows.Add(drow);
grdvw.DataSource = tbs;
grdvw.DataBind();
txtsrch.Text = "";
myConnection.Close();
lblerror.Visible = true;
lblerror.Text="Sucessfully Added,Try Another";

}
else
{
lblerror.Visible = true;
lblerror.Text = "Invalid Material Number";

}

}
protected void bnsave_Click(object sender, EventArgs e)
{

string connString = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(connString);
myConnection.Open();
MySqlTransaction tran = myConnection.BeginTransaction();
try
{
for (int i = 0; i < grdvw.Rows.Count; i++)
{
CheckBox chkdelete = (CheckBox)grdvw.Rows[i].Cells[0].FindControl("checkdelete");
string matno = grdvw.Rows[i].Cells[1].Text;

if (chkdelete.Checked == true)
{
MySqlCommand cmd1 = new MySqlCommand("update grntable set trackitem='GSC',modifiedon='" + createdon + "',modifiedby='" + txtsrch.Text + "',modifiedat='" + ipaddr + "' where grnno='" + matno + "'", myConnection, tran);
cmd1.ExecuteNonQuery();

}

}
tran.Commit();
myConnection.Close();
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertmessage", "alert('Scucess')", true);

}
catch (Exception)
{
tran.Rollback();
myConnection.Close();
}
myConnection.Close();
Response.Redirect("grnreceive.aspx");
}
protected void checkrow(object sender, EventArgs e)
{

CheckBox chkheader = (CheckBox)grdvw.HeaderRow.FindControl("checkboxalldelete");
if (chkheader.Checked == true)
{
for (int i = 0; i < grdvw.Rows.Count; i++)
{
CheckBox chkrr = (CheckBox)grdvw.Rows[i].Cells[0].FindControl("checkdelete");
chkrr.Checked = true;
}
}
else
{
for (int i = 0; i < grdvw.Rows.Count; i++)
{
CheckBox chkrr = (CheckBox)grdvw.Rows[i].Cells[0].FindControl("checkdelete");
chkrr.Checked = false;
}
}
}

}

Database :

To create database :

create database jkt;

Open particular database :

use jkt;

Create a new table:

create table grntable
(
grnno int(11) not null auto_increment primary key ,
grndate date,
deptname varchar(100),
pmdetails varchar(250),
problem varchar(200),
standby varchar(10),
staffname varchar(50),
trackitem   varchar(10)
);

Description for the table :

desc grntable;

Select all values from table :

select * from grntable;

Insert values into the table:

insert into grntable (grnno,grndate,deptname,pmdetails,problem,standby,staffname,trackitem)

values

(01,now(),'Hardware','Mouse','Click button not working','yes','John','DSR

'),

(02,now(),'Network','Printer','Paper not working','yes','Jagan','DSR

'),

(032,now(),'Hardware','Keyboard','Keys not working','yes','Sathish','DSR

');

Picture for table Structure:

Dot Net Vellore

Tuesday, 5 April 2016

Adding temporary values from multiple textbox to gridview and multi rows inserted to database.

Screen Shot 1:

Monday, 4 April 2016

How to check if a value already exists in my database or checking user name availability

Multiple Search values added into single Grid view and update using check box selection :

(Note: I have use to connect the database in Mysql )

Report Abuse

Labels