Regular expression validator for date format dd MMM yy and dd MMM yyyy

21 05 2008
<asp:RegularExpressionValidator ID=”RegularExpressionValidator1″ runat=”server” ControlToValidate=”TextBox1″ ErrorMessage=”Incorrect format” ValidationExpression=”^(d{0}|(31(?!(Feb|feb|Apr|apr|Jun|jun|Sep|
sep|Nov|nov)))|((30|29)(?!Feb|feb))|(29(?=Feb|feb(((1[6-9]|[2-9]\
d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00
)))))|(29(?=Feb|feb(((0[48]|[2468][048]|[13579]
[26])|((16|[2468][048]|[3579][26])00)))))|(0?[1-9])|1\d|2[0-8])[- ](Jan|jan|feb|Feb|mar|Mar|may|May|apr|Apr|jul|Jul|Jun|jun|Aug|aug|
Oct|oct|Sep|sep|Nov|nov|dec|Dec)[- ]((1[6-9]|[2-9]\d)\d{2}|\d{2}|d{0})$”></asp:RegularExpressionValidator>

Comments : 1 Comment »

Categories : Uncategorized

Loading Excel values to SQL server database

21 05 2008

Loading Excel values to database

If you want to load the excel values to database (eg:SQL server database) follow the steps:

1) Create a empty table with columns.

CREATE TABLE [dbo].[users](

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

[Password] [nchar](10) NULL )

Check the data must be empty.

2) Create an excel sheet with your data. In the excel the top row

must contain same column names as table created by us and

remember that you have your data in Sheet1 of excel sheet.

3) In code behind page write this code:

This code can be written in the event you like.

For now I am writing this code in Button_click

Event.

string excelConnectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\users.xls;Extended Properties=”"Excel 8.0;HDR=YES;”"”;

OleDbConnection conn = new

OleDbConnection(excelConnectionString);

//Here Sheet1 means the current sheet in excel file If your data is in sheet2 you can give Sheet2.

string selqry = “select * from [Sheet1$]“;

OleDbCommand cmd = new OleDbCommand(selqry, conn);

conn.Open();

OleDbDataReader dr;

dr = cmd.ExecuteReader();

string sqlConnectionString = “Data Source=MNCXP08;Initial

Catalog=test;Persist Security Info=True;User

ID=sa;Password=admin123″;

SqlBulkCopy bcp = new SqlBulkCopy(sqlConnectionString);

//Here users is the table we created

bcp.DestinationTableName = “users”;

bcp.WriteToServer(dr);

4) Check the users table you will get all the results.


Comments : 1 Comment »

Categories : Uncategorized

Displaying total in Footer of Gridview

9 05 2008

If you want to display the Total in the footer of a gridview. Follow the steps:

1) Create a table of employees with salary.

2) Select ename,esalary from emptbl

ename esalary

Bharath 18000

krishna 20000

3) Now in gridview the total of salary must come in footer.

4) .aspx code

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<html xmlns=”http://www.w3.org/1999/xhtml” >
<head runat=”server”>
<title>Untitled Page</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” BackColor=”#DEBA84″
BorderColor=”#DEBA84″ BorderStyle=”None” BorderWidth=”1px” CellPadding=”3″ CellSpacing=”2″
DataSourceID=”SqlDataSource1″ OnRowDataBound=”GridView1_RowDataBound” ShowFooter=”true”>
<FooterStyle BackColor=”#F7DFB5″ ForeColor=”#8C4510″ />
<Columns>
<asp:BoundField DataField=”ename” HeaderText=”ename” SortExpression=”ename” />
<asp:TemplateField HeaderText=”esalary” SortExpression=”esalary”>

<ItemTemplate>
<asp:Label ID=”Label1″ runat=”server” Text=’<%# Bind(”esalary”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>

<asp:Label ID=”Label2″ runat=”server” ></asp:Label>

</FooterTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BackColor=”#FFF7E7″ ForeColor=”#8C4510″ />
<SelectedRowStyle BackColor=”#738A9C” Font-Bold=”True” ForeColor=”White” />
<PagerStyle ForeColor=”#8C4510″ HorizontalAlign=”Center” />
<HeaderStyle BackColor=”#A55129″ Font-Bold=”True” ForeColor=”White” />
</asp:GridView>

</div>
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:TestConnectionString %>”
SelectCommand=”SELECT [ename], [esalary] FROM [emptbl]“></asp:SqlDataSource>
</form>
</body>
</html>

5) Code behind:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{

private int Total = 0;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{

//DataBinder.Eval method Evaluates data-binding expressions at run time.

int Tot=(int)DataBinder.Eval(e.Row.DataItem,”esalary”);
Total = Total + Tot;
}

if (e.Row.RowType == DataControlRowType.Footer)
{
Label lbltotal = e.Row.FindControl(”Label2″) as Label;
lbltotal.Text = Total.ToString();
}
}
}

Any doubts mail me.


Comments : No Comments »
Tags :
Categories : ASP.NET