Validate GridView textbox in clientside using javascript

 Find Gridview TextBox in client side using javascript  
 Here this article show show how we can find gridview textbox which is inside Template Field and validate the entry while
editing the record.  
 function ValidateGridEditMode()  
 {  
      var n = document.getElementById('ctl00_ContentPlaceHolder1_tbcLocation_tpnlCountry_gvEmp').rows.length;  
      var i;  
      for(i=2; i <=n; i++)  
      {   
           if(i<10)  
           {  
                txtEmpName=document.getElementById('ctl00_ContentPlaceHolder1_tbcLocation_tpnlCountry_gvEmp_ctl0'+i+'_txtEmpName');   
           }  
           else  
           {  
                txtEmpName=document.getElementById('ctl00_ContentPlaceHolder1_tbcLocation_tpnlCountry_gvEmp_ctl'+i+'_txtEmpName');   
           }  
           if(txtEmpName !=null)  
           {  
                if(txtEmpName.value == "")  
                {  
                     alert("Enter Emp Name...!");   
                     txtEmpName.focus();   
                     return false;  
                }   
           }   
      }  
      return true;  
 }  

Read More

Single Stored Procedure for Insert and update record

It good idea to use a single stored proceude instead of write two stored procedure for insert and update.So i am here explainig how to do this.

here we can check if EnquiryId ==0 then insert otherwise update.

Create PROCEDURE [dbo].[uspEnquiry_AddEdit]
(
@EnquiryId bigint
, @EnquiryNo varchar(20) output
, @ClientId bigint
, @ContactPerson varchar(50)
, @Desig varchar(50)
, @Mobile varchar(15)
, @Phone varchar(50)
, @EmailId varchar(100)

)
AS
DECLARE @Message varchar(100)
IF(@EnquiryId=0)
BEGIN
IF EXISTS(SELECT * FROM Enquiry WHERE EnquiryNo=@EnquiryNo)
SET @Message='Record Exists In Database...!'

ELSE
BEGIN
INSERT INTO Enquiry
(
[EnquiryNo]
,[ClientId]
,[ContactPerson]
,[Desig]
,[Mobile]
,[Phone]
,[EmailId]
)
VALUES
(
@EnquiryNo
,@ClientId
,@ContactPerson
,@Desig
,@Mobile
,@Phone
,@EmailId
)
SELECT @EnquiryId = @@IDENTITY;
END
END
ELSE
BEGIN
UPDATE Enquiry
SET [EnquiryNo] = @EnquiryNo
,[ClientId] = @ClientId
,[ContactPerson] = @ContactPerson
,[Desig] = @Desig
,[Mobile] = @Mobile
,[Phone] = @Phone
,[EmailId] = @EmailId
WHERE EnquiryId = @EnquiryId

END
SELECT @EnquiryId


Thanks & Regards
Santosh Singh

Read More

Calculate two textbox value using javascript

Calculate two textbox value using javascript in ASP.NET
function Calculate()
{
var txtProposalValue = document.getElementById('<%=txtProposalValue.ClientID %>');
var txtProfit = document.getElementById('<%=txtProfit.ClientID %>');
var txtLikelyValue = document.getElementById('<%=txtLikelyValue.ClientID %>');
var LikelyValue =(parseFloat(txtProposalValue.value) + parseFloat(txtProfit.value));
if(txtProfit.value =="")
{
txtLikelyValue.value = "0";
}
else
{
txtLikelyValue.value = LikelyValue;
}
}

Read More

Insert record in database using 3-Tier Architecture asp.net

Insert data in database using 3 Tier Architecture .
In this article i am explaining you how to insert record in database using 3-Tier Architecture.You can create separate project for Business and data access layer or also can keep in App_code folder.

This is aspx.cs code.Here code communicate through Business Layer.

protected void btnSubmit_Click(object sender, EventArgs e)
{
objClientCls.ClName = txtCoName.Text;
objClientCls.AddLine1 = txtAddLine1.Text;
objClientCls.AddLine2 = txtAddLine2.Text;
try
{
if (objClientCls.ClId == 0)
{
string Msg = objClBLL.InsertClMaster(objClientCls);
lblMessage.Text = Msg;
Clear();
}
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
finally
{
objClBLL = null;
}
}

Business Layer Code

public string InsertClMaster(ClMasterCls objClMasterCls)
{
try
{
return objClMasterDAL.Insert(objClMasterCls);
}
catch
{
throw;
}
finally
{
objClMasterDAL = null;
}
}

Data Acess layer code
public string Insert(ClMasterCls objClMasterCls)
{
try
{
conn.Open();
string CreateMsg = Convert.ToString(SqlHelper.ExecuteScalar(conn, "usp_InsertClMaster", objClMasterCls.ClId, objClMasterCls.ClName, objClMasterCls.AddLine1
, objClMasterCls.AddLine2));
return Msg;
}
catch
{
throw;
}
finally
{
conn.Close();
}
}

Thanks & Regards
Santosh Singh

Read More

Export Gridview to Excel and PDF file using asp.net and c#

In this article i am explaining you how to display report using aspx page.Exporting Gridview to excel and PDF report is one of the most common requirement in real world.So i have written this code,you can customize ConvertDataInPdf method according to your requirement.

aspx code.
<asp:GridView ID="GridView1" runat="server" EnableTheming="False" OnRowDataBound="GridView1_RowDataBound"
Width="100%">
<HeaderStyle CssClass="ReportHeader" Width="80px" /
<RowStyle CssClass="ReportText" />
<PagerStyle CssClass="ReportHeader" />
<AlternatingRowStyle CssClass="ReportText" />
<EmptyDataRowStyle CssClass="ReportText" />
<EmptyDataTemplate>
<span style="font-size: 11pt; color: #ff0033"><strong>No Records ........!!</strong></span>
</EmptyDataTemplate>
<Columns>
<asp:BoundField HeaderText="Sr. No." />
</Columns>
</asp:GridView>

.cs code
string conStr = FetchConnection.FetchConnectionString().ToString();
void GridViewEmp()
{
DataSet Ds = new DataSet();
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
string str = "SELECT * from EmployeeTbl"
Ds = SqlHelper.ExecuteDataset(conStr, CommandType.Text, str);
GridView1.DataSource = Ds;
GridView1.DataBind();
}

protected void lbExportInExcel_Click1(object sender, ImageClickEventArgs e)
{
string attachment = "attachment; filename=" + "Emp Report" + ".xls";
Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
reportName.RenderControl(htmlWrite);
rowInfo.RenderControl(htmlWrite);
rowGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

protected void btnPdf_Click(object sender, ImageClickEventArgs e)
{
PrintAndExport exportDataInPdf = new PrintAndExport();
string reportName = "Employee Loan Report" + " ( " +lbl.Text+ " ) ";
string OtherInfo = "Company Name: " + ddlCompany.SelectedItem.Text + "\t\t\t" + "Emp Status: " + ddlStatus.SelectedItem.Text;
DataTable dt = ExportData();
exportDataInPdf.ConvertDataInPdf(dt, reportName, OtherInfo);
}


public void ConvertDataInPdf(DataTable dtExportInPdf, string reportName, string OtherInfo)
{
try
{
float CompanyNameSize = 11;
float ReportNameSize = 9;
float HeaderTextSize = 7;
float ReportTextSize = 6;

int totalWidth = 0;
int tableWidthPercent = 100;
int[] widths = new int[dtExportInPdf.Columns.Count];

for (int h = 0; h < dtExportInPdf.Columns.Count; h++) //Data table header column width
{
string strWidth = dtExportInPdf.Columns[h].ToString();
widths[h] = strWidth.Length;
}

foreach (DataRow dr in dtExportInPdf.Rows) //Data table column width
{
int[] colItemWidth = new int[dtExportInPdf.Columns.Count];
for (int i = 0; i < dtExportInPdf.Columns.Count; i++) //Data table max item width
{
if (dr[i].ToString().Length > 20)
{
colItemWidth[i] = 20;
}
else if (dr[i].ToString().Length < 3)
{
colItemWidth[i] = 3;
}
else
{
colItemWidth[i] = dr[i].ToString().Length;
}

if (colItemWidth[i] > widths[i])
{
widths[i] = colItemWidth[i];
}
}
}
for (int h = 0; h < dtExportInPdf.Columns.Count; h++)
{
totalWidth += widths[h];
}
Document pdfDoc = null;
if (totalWidth > 0 && totalWidth <= 110)
{
pdfDoc = new Document(PageSize.A4, 20, 20, 20, 20);
}
else if (totalWidth > 110 && totalWidth <= 160)
{
pdfDoc = new Document(PageSize.A4.Rotate(), 20, 20, 20, 20);
}
else if (totalWidth > 160 && totalWidth <= 250)
{
HeaderTextSize = 6;
ReportTextSize = 5;
pdfDoc = new Document(PageSize.LEGAL.Rotate(), 20, 20, 20, 20);

}
else if (totalWidth > 250 && totalWidth <= 300)
{
CompanyNameSize = 9;
ReportNameSize = 7;
HeaderTextSize = 6;
ReportTextSize = 5;
pdfDoc = new Document(PageSize.B1, 20, 20, 20, 20);
}
else if (totalWidth > 300)
{
CompanyNameSize = 9;
ReportNameSize = 7;
HeaderTextSize = 6;
ReportTextSize = 5;
pdfDoc = new Document(PageSize.B1.Rotate(), 20, 20, 20, 20);
}

// Creates a PdfPTable with column count of the table equal to no of columns of the datatable or gridview or gridview datasource.
PdfPTable pdfTable = new PdfPTable(dtExportInPdf.Columns.Count);
pdfTable.WidthPercentage = tableWidthPercent;
pdfTable.HeaderRows = 4; // Sets the first 4 rows of the table as the header rows which will be repeated in all the pages.

#region PDFHeader
PdfPTable headerTable = new PdfPTable(3); // Creates a PdfPTable with 3 columns to hold the header in the exported PDF.
byte[] logo = (byte[])System.Web.HttpContext.Current.Session["Logo"];
iTextSharp.text.Image imgLogo = iTextSharp.text.Image.GetInstance(logo);
imgLogo.ScaleToFit(80f, 40f);//Resize image depend upon your need
imgLogo.SpacingBefore = 0f;//Give space before image
imgLogo.SpacingAfter = 1f;//Give some space after the image
PdfPCell clLogo = new PdfPCell(imgLogo);// Creates a PdfPCell which accepts a phrase as a parameter.
clLogo.Border = PdfPCell.NO_BORDER;// Sets the border of the cell to zero.
clLogo.HorizontalAlignment = Element.ALIGN_LEFT;// Sets the Horizontal Alignment of the PdfPCell to left.
clLogo.VerticalAlignment = Element.ALIGN_MIDDLE;

// Creates a phrase to hold the application name at the left hand side of the header.
Phrase phApplicationName = new Phrase("" + System.Web.HttpContext.Current.Session["CompanyName"] + "", FontFactory.GetFont("Arial", CompanyNameSize, iTextSharp.text.Font.NORMAL));
PdfPCell clApplicationName = new PdfPCell(phApplicationName);// Creates a PdfPCell which accepts a phrase as a parameter.
clApplicationName.Border = PdfPCell.NO_BORDER;// Sets the border of the cell to zero.
clApplicationName.HorizontalAlignment = Element.ALIGN_CENTER;// Sets the Horizontal Alignment of the PdfPCell to left.
clApplicationName.VerticalAlignment = Element.ALIGN_MIDDLE;

// Creates a phrase to show the current date at the right hand side of the header.
Phrase phDate = new Phrase(DateTime.Now.Date.ToString("dd/MM/yyyy"), FontFactory.GetFont("Arial", 7, iTextSharp.text.Font.NORMAL));
PdfPCell clDate = new PdfPCell(phDate);// Creates a PdfPCell which accepts the date phrase as a parameter.
clDate.HorizontalAlignment = Element.ALIGN_RIGHT;// Sets the Horizontal Alignment of the PdfPCell to right.
clDate.Border = PdfPCell.NO_BORDER;// Sets the border of the cell to zero.
clDate.VerticalAlignment = Element.ALIGN_MIDDLE;

headerTable.AddCell(clLogo);
headerTable.AddCell(clApplicationName);// Adds the cell which holds the application name to the headerTable.
headerTable.AddCell(clDate);// Adds the cell which holds the date to the headerTable.
headerTable.DefaultCell.Border = PdfPCell.NO_BORDER;// Sets the border of the headerTable to zero.

// Creates a PdfPCell that accepts the headerTable as a parameter and then adds that cell to the main PdfPTable.
PdfPCell cellHeader = new PdfPCell(headerTable);
cellHeader.VerticalAlignment = Element.ALIGN_TOP;
cellHeader.Border = PdfPCell.NO_BORDER;
cellHeader.Colspan = dtExportInPdf.Columns.Count;// Sets the column span of the header cell to noOfColumns.
pdfTable.AddCell(cellHeader);// Adds the above header cell to the table.
#endregion PDFHeader

//Creates a phrase for a new line.
Phrase phSpace1 = new Phrase("\n");
PdfPCell clSpace1 = new PdfPCell(phSpace1);
clSpace1.Border = PdfPCell.BOTTOM_BORDER;
clSpace1.BorderWidth = 1;
clSpace1.BorderColor = iTextSharp.text.Color.DARK_GRAY;
clSpace1.Colspan = dtExportInPdf.Columns.Count;
pdfTable.AddCell(clSpace1);

// Creates a phrase to hold the report name.
Phrase phHeader = new Phrase("" + reportName + "", FontFactory.GetFont("Arial", ReportNameSize, iTextSharp.text.Font.NORMAL));
PdfPCell clHeader = new PdfPCell(phHeader);
clHeader.Colspan = dtExportInPdf.Columns.Count;
clHeader.Border = PdfPCell.NO_BORDER;
clHeader.HorizontalAlignment = Element.ALIGN_CENTER;
clHeader.VerticalAlignment = Element.ALIGN_MIDDLE;
clHeader.PaddingTop = 5;
clHeader.PaddingBottom = 2;
pdfTable.AddCell(clHeader);

//Create Phrage to hold other informations
Phrase phOtherInfo = new Phrase(OtherInfo, FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
PdfPCell cellOtherInfo = new PdfPCell(phOtherInfo);
cellOtherInfo.Colspan = dtExportInPdf.Columns.Count;
cellOtherInfo.Border = Element.ALIGN_LEFT;
cellOtherInfo.PaddingBottom = 10;
pdfTable.AddCell(cellOtherInfo);

PdfWriter.GetInstance(pdfDoc, System.Web.HttpContext.Current.Response.OutputStream);
string strFooter = "Copyright © 2010 By Cnergee. Page:";
Phrase phCopyright = new Phrase(strFooter, FontFactory.GetFont(FontFactory.TIMES_ROMAN, 8, iTextSharp.text.Font.NORMAL));
Phrase phPageNo = new Phrase("", FontFactory.GetFont(FontFactory.TIMES_ROMAN, 8, iTextSharp.text.Font.NORMAL));
HeaderFooter footer = new HeaderFooter(phCopyright, phPageNo);
//footer.Alignment = Element.ALIGN_LEFT;
footer.Alignment = Element.ALIGN_RIGHT;
footer.Border = iTextSharp.text.Rectangle.TOP_BORDER;
footer.GrayFill = 10;
pdfDoc.Footer = footer;

pdfDoc.Open();
Font font8 = FontFactory.GetFont("ARIAL Narrow", 7);
if (dtExportInPdf != null)
{
pdfDoc.Header = null;
//Create header for pdf table
string cloName = null;
Phrase ph = null;
for (int i = 0; i < dtExportInPdf.Columns.Count; i++)
{
cloName = dtExportInPdf.Columns[i].ColumnName;
if (dtExportInPdf.Columns.Count > 0)
{
ph = new Phrase(cloName, FontFactory.GetFont("Arial", HeaderTextSize, iTextSharp.text.Font.BOLD));
}
else
{
ph = new Phrase(cloName, FontFactory.GetFont("Arial", HeaderTextSize, iTextSharp.text.Font.BOLD));
}
pdfTable.AddCell(ph);
}

//Add data into the pdf table
for (int rows = 0; rows < dtExportInPdf.Rows.Count; rows++)
{
ph = null;
PdfPCell pCell = null;
for (int column = 0; column < dtExportInPdf.Columns.Count; column++)
{
ph = new Phrase(dtExportInPdf.Rows[rows][column].ToString(), FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
pCell = new PdfPCell(ph);
if (dtExportInPdf.Columns[column].ColumnName == "SrNo" || dtExportInPdf.Columns[column].ColumnName == "Sr.No." || dtExportInPdf.Columns[column].ColumnName == "Sr. No." || dtExportInPdf.Columns[column].ColumnName == "Code" || dtExportInPdf.Columns[column].ColumnName == "EmpCode" || dtExportInPdf.Columns[column].ColumnName == "EmployeeCode")
{
pCell.HorizontalAlignment = Element.ALIGN_CENTER;
}
else if (dtExportInPdf.Columns[column].ColumnName == "Amount")
{
pCell.HorizontalAlignment = Element.ALIGN_RIGHT;
}
else if (dtExportInPdf.Columns[column].ColumnName == "Date" || dtExportInPdf.Columns[column].ColumnName == "From" || dtExportInPdf.Columns[column].ColumnName == "To")
{
pCell.HorizontalAlignment = Element.ALIGN_CENTER;
}
else
{
pCell.HorizontalAlignment = Element.ALIGN_LEFT;
}
pdfTable.AddCell(pCell);
}
pdfTable.SetWidths(widths);
}
pdfTable.SpacingBefore = 15f; // Give some space after the text or it may overlap the table
pdfDoc.Add(pdfTable); // add pdf table to the document
}
pdfDoc.Close();

string pdfFileName = reportName;
reportName = reportName.Replace(" ", "");
System.Web.HttpContext.Current.Response.ContentType = "application/pdf";
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename= " + pdfFileName + ".pdf");
System.Web.HttpContext.Current.Response.Write(pdfDoc);

System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
catch (DocumentException de)
{
System.Web.HttpContext.Current.Response.Write(de.Message);
}
catch (IOException ioEx)
{
System.Web.HttpContext.Current.Response.Write(ioEx.Message);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
}
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
//specified ASP.NET server control at run time.
}

Thanks & Regards
Santosh

Read More

Find tables that contain a certain field in database

Some times user need to get information about a table field,means how many table contains this field. A table field eg 'xyz' have relationship with many table and you want to see all table.Write below query.

select * from information_schema.columns where column_name = 'xyz'

Thanks & Regards
Santosh

Read More